Calculate status to display it with Datatable

Katia_LT

I have SharePoint list with this data:
Task name, task start date, task due date, status.
Status is calculated column with IF clause to change status accordingly to tasks' start and due dates.
Short example: IF(AND([Due Date]>=TODAY();[Due Date]<=TODAY()+14);"Due is approaching"; "Late")

There is 5 statuses in total: Not started, In progress, Due is approaching, Late, Finished on time, Finished with delay.

The problem is that SharePoint doesn't refresh the status, when the today date changes.

So I want to try and calculate these statuses in Datatable. Is it possible?

I calculate approaching due date with jQuery and add it to <div id="Due_Date"></div>:

function CalcDue(Due_Date) {
var today = new Date();
var nextday  = moment(today).add(14,'days');
var targetDate = moment(today).subtract(1,'day');

targetDate = moment(targetDate).format("YYYY-MM-DD");
var currentDate = targetDate+'T00:00:00.000Z';

nextday = moment(nextday).format("YYYY-MM-DD");
var nextDate = nextday+'T00:00:00.000Z';

        var url7 = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbyTitle('Tasks')/items?$filter=(due ge datetime'"+currentDate+"') and (due le datetime'"+nextDate+"')&$top=3000";
        getListItems(url7, function(data) {
                var art = data.d.results;
                 $('#' + Due_Date).text(art.length);
        });
}

All other statuses I calculate from the list using filter and count. For this case I have to have statuses already in the list.

Is it possible to calculate statuses and then use them in my Datatable with all other tasks data from the tasks' list?

allan

Hi,

Yes, use a custom data rendering function to perform calculations such as this.

Then when the date changes (use a setInterval to detect that) you could call rows().invalidate() which will cause the calculations to run again and the dates be updated.

The other option is to just use the Ajax feed from SharePoint with the setInterval and call ajax.reload when the day ticks over.

Allan