1

I would deeply appreciate any help. I have been staring at this code for days. I am trying to add a Script Editor web part above a list, and make the rows in the list change color according to how close today's date is to a suspense date (SuspenseDate column in the list.) I want to have a different column for items in these three circumstances: past due, 1 day before due, 2 days before due

This code does nothing, and I can't figure out why, despite hours of troubleshooting. My SuspenseDate column shows dates in standard format, with no time. (ex. 08/08/2023)

I have been banging my head against the wall on this for days. Please let me know if you have any ideas.

<script>
(function () {
    'use strict';

    var overrideCtx = {};
    overrideCtx.Templates = {};
    //OnPostRender call postRenderHandler function.
    overrideCtx.OnPostRender = postRenderHandler;

    // Register the template overrides.
    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);   

})();
function postRenderHandler(ctx) {

            // get today's date
            var today = new Date();
            // zero out the time portion so we will only compare days
            today.setHours(0,0,0,0);



            var rows = ctx.ListData.Row;
            for (var i = 0; i < rows.length; i++) {

                // get the date set in your date YourDateField
                var suspenseDate = new Date(rows[i]["SuspenseDate"]).toISOString();

                var rowId = GenerateIIDForListItem(ctx, rows[i]);
                var row = document.getElementById(rowId); // so we know which row to change

                // get date 24 hours before suspense date
                var suspense24 = new Date(suspenseDate);
                suspense24.setDate(suspense24.getDate() - 1);
    
                // get date 48 hours before suspense date
                var suspense48 = new Date(suspenseDate);
                suspense48.setDate(suspense48.getDate() - 2);
           


                if (today > suspense24) {
                    row.style.backgroundColor = '#FF5733';
                } 
                if (today == suspense24) {
                    row.style.backgroundColor = '#FFC300';
                } 
                if (today == suspense48) {
                    row.style.backgroundColor = '#DAF7A6';
                } 
            }
}
</script>
6
  • Make sure you are using correct Internal name of your suspense date column in the code at: var suspenseDate = new Date(rows[i]["SuspenseDate"]).toISOString();. You can get the internal name of list column by following: ganeshsanapblogs.wordpress.com/2023/04/17/…. Let me know if it is still not working. Aug 10, 2023 at 12:34
  • Thanks Ganesh! Although it looks like this is for SharePoint online? I'm working with the legacy version, SharePoint 2016. Aug 10, 2023 at 14:35
  • You can use the classic experience method (via list settings) given in above article to find the internal name of list column or use SharePoint REST API (directly from browser tab). If you are not using correct internal name of column then your code will work as expected. Aug 10, 2023 at 14:41
  • 1
    Thanks! This made a huge difference. Aug 14, 2023 at 15:22
  • You're welcome, glad it helped you! Aug 14, 2023 at 15:24

3 Answers 3

0

There's a couple things going on here. One is that, whether or not you see it in the SharePoint UI, even if you have a date column set to be "date only", behind the scenes there is still a time component to a Date object.

Second, you can't compare Date objects using ==. If you want to do that you would need to call getTime() and compare the resulting numbers. But you don't need to, you can use >, <, >= and <= directly with Date objects.

I would try restructuring the code inside the for loop like this:

// get the date set in your date YourDateField
// but DON'T turn it into an ISO string, you still
// want it as a Date object
var suspenseDate = new Date(rows[i]["SuspenseDate"]);

// but you still probably want to zero out
// the time portion of the date so that it's
// comparing relatively equally with the "today"
// date that has time zeroed out
suspenseDate.setHours(0, 0, 0, 0);

var rowId = GenerateIIDForListItem(ctx, rows[i]);
var row = document.getElementById(rowId); // so we know which row to change

// get date 24 hours before suspense date
var suspense24 = new Date(suspenseDate);
suspense24.setDate(suspense24.getDate() - 1);

// you don't need to zero out the hours here because
// suspense24 was created by cloning suspenseDate
// which already has time zeroed out

// get date 48 hours before suspense date
var suspense48 = new Date(suspenseDate);
suspense48.setDate(suspense48.getDate() - 2);

// it's overdue if it is greater than suspenseDate,
// (or is exactly on the suspenseDate?)
// not greater than suspense24, because suspense24
// is still one day _before_ the suspenseDate

if (today >= suspenseDate) {
    row.style.backgroundColor = '#FF5733';
}
// it's within one day if it's equal (or greater) than suspense24
// but _before_ the suspense date
if (today >= suspense24 && today < suspenseDate) {
    row.style.backgroundColor = '#FFC300';
}
// it's within two days if it's equal (or greater) than suspense48
// but _before_ suspense24
if (today >= suspense48 && today < suspense24) {
    row.style.backgroundColor = '#DAF7A6';
}
3
  • Thank you so much for the detailed response Dylan! You made a lot of good points. Unfortunately it still doesn't work. Do you think it's possible the format of today's date is different from the format of the date I'm getting from the "SuspenseDate" column somehow? Even if I alter the above code to change green (for example) if the suspense date is today, and then put today's date in that column for a row, nothing happens. Aug 10, 2023 at 14:33
  • I would suggest opening the developer tools for your browser, and setting some breakpoints in your code so that you can see exactly what the values of the variables are as the code is running. That will give you a lot of insight into what might be going wrong. Aug 10, 2023 at 16:35
  • Thank you. I turned out to be the thing Ginesh mentioned above. Thanks again for your detailed response. It really helped a lot. Aug 14, 2023 at 15:23
0

To all who will read this in the future. The solution ended up being following Ganesh's advice above to find the real column name, plus taking into account what Dylan said.

Below is the working code:

<script>
(function () {
    'use strict';

    var overrideCtx = {};
    overrideCtx.Templates = {};
    //OnPostRender call postRenderHandler function.
    overrideCtx.OnPostRender = postRenderHandler;

    // Register the template overrides.

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);   

})();
function postRenderHandler(ctx) {

            // get today's date
            var today = new Date();
            // zero out the time portion so we will only compare days
            today.setHours(0,0,0,0);



            var rows = ctx.ListData.Row;
            for (var i = 0; i < rows.length; i++) {

                var suspense = new Date(rows[i]["Suspense"]);
                suspense.setHours(0,0,0,0);

                // get date 24 hours before suspense date
                var date24 = new Date(suspense);
                date24.setDate(date24.getDate() - 1);


                // get date 48 hours before suspense date
                var date48 = new Date(suspense);
                date48.setDate(date48.getDate() - 2);
                //us48 = date48.toLocaleDateString();

                var rowId = GenerateIIDForListItem(ctx, rows[i]);
                var row = document.getElementById(rowId); // so we know
which row to change

                if (today >= date24 && today <= suspense) {
                    row.style.backgroundColor = '#FFC300'; //lightorange,
due today/tomorrow

                }
                else if (today >= date48 && today <= date24) {
                    row.style.backgroundColor = '#DAF7A6'; //yellowgreen in
2 days
                }
                else if (today > suspense) {
                                row.style.backgroundColor = '#FF5733';
//orange, past due
                        }
            }
}
</script>
0

Make sure you are using correct internal name of your suspense date column in the code at line:

var suspenseDate = new Date(rows[i]["SuspenseDate"]).toISOString();

You can get the exact internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?

You can use the "classic experience" method (via list settings) given in above article link to find the internal name of list column or use SharePoint REST API (directly from browser tab). If you are not using correct internal name of column then your code will work as expected.

Also, refer below answers for using Client Side Rendering (CSR) / JSLink for customizing display of date columns in the SharePoint lists:

  1. How to color code a SharePoint list date field based on comparison with todays date?
  2. Conditional Formatting with date comparison
  3. How to Highlight a Row on Active Status

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.