Sharepoint list item status depending on previous item

Katia_LT

I am using ajax call to get list items:

var oDataUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('tasks')/items?$select=ID,start,end,finished,Title,status,Modified,objects/Title&$expand=objects";
        $.ajax({
                url: oDataUrl,
                type: "GET",
                dataType: "json",
                headers: {
                        "accept": "application/json;odata=verbose"
                },
                success: successFunction,
                error: errorFunction
        });

I have a function, which calculates statuses

function calculateStatus(rowData) {
    var obj = rowData.objects.Title;
    var ID = rowData.ID;
    
    var today = moment();
    var startDate = moment(rowData.start);
    var endDate = moment(rowData.end);
    var finishedDate = moment(rowData.finished);
    var daysUntilend = endDate.diff(today, 'days');

    if (startDate.isBefore(today) && endDate.isSameOrAfter(today) && daysUntilend <= 14 && !finishedDate.isValid()) {
        return 'Approaching';
    } else if (startDate.isAfter(today) && !finishedDate.isValid() ) {
        return 'Not_started';
    } else if (startDate.isSameOrBefore(today) && endDate.isAfter(today) && !finishedDate.isValid()) {
        return 'In_Progress';
    } else if (startDate.isSameOrBefore(today) && endDate.isBefore(today) && !finishedDate.isValid()) {
        return 'Late';
    } else if (finishedDate.isValid() && finishedDate.isSameOrBefore(endDate)) {
        return 'Finished_on_time';
    } else if (finishedDate.isValid() && finishedDate.isAfter(endDate)) {
        return 'Finished_with_delay';
    } else {
        return '';
    }
}  

And displays it in Datatable column

{"mData": "status",
"render": function (data, type, row) {
          var calculatedStatus = calculateStatus(row);
          if (calculatedStatus === null) return "";
          else
          return "<label class='badge1 badge-" + calculatedStatus + "'>" + calculatedStatus + "</label>"
}
},

It all works fine, but I need to modify status checking logic, that it would check if PREVIOUS ITEM WITH THE SAME OBJECT has the finished date entered. If the finished date is entered (finished value not empty) check all conditions, but if the finished date is empty, calculate status of that item and then all OTHER ITEMS WITH THE SAME OBJECT would have status Not_started.

Example of Datatable statuses which I want to achieve:

ID	Title	Object	Start	End	Finished	status
1	Task1	Reading	2024-01-01	2025-02-01	2024-02-03	Finished_on_time
2	Task2	Reading	2024-01-01	2024-01-01	2024-01-01	In_Progress
3	Task3	Dancing	2023-10-25	2024-01-01		Late
4	Task4	Dancing	2023-12-01	2024-02-01		Not_started
5	Task5	Dancing	2023-11-01	2023-12-01		Not_started
6	Task6	Painting	2024-02-03	2024-03-01		In_Progress
7	Task7	Painting	2024-02-03	2024-03-01		Not_started

allan

Hi,

The rendering function doesn't have immediate access to the data from the other rows in the table. So what I would suggest in this case is that in your successFunction, which is presumably where you initialise the DataTable and pass it the data to display, you pre-process the data. Spin over the rows changing if there are items with the same object id and update them accordingly. Indeed, you could even run your status calculation before the DataTable is initialised, which would give a slight performance improvement as well, since you are only doing it once per row, rather than every time the rendering function is called.

Allan

Katia_LT

Thank you for the idea!
Changed my success funtion to this:

function successFunction(data) {
    var tasks = data.d.results;
    var objectsMap = {}; // Map to store objects and their statuses

    // Iterate through tasks to update statuses based on previous items with the same object
    tasks.forEach(function(task, index) {
        var object = task.objects.Title;
      var previousTask = findPreviousTaskWithSameObject(tasks, index, object);

        if (previousTask && previousTask.finished=== null) {

            // If previous item with same object exists and current item has no finished date
            task.status = 'Not_started';
        } else {
            // Calculate status based on original logic
            task.status= calculateStatus(task);
        }

        // Store the status for the object
        if (!objectsMap[object]) {
            objectsMap[object] = task.status;
        }
    });

    // Update statuses for all items with the same object if necessary
    tasks.forEach(function(task) {
        var object = task.objects.Title;
        var status = task.status;

        if (status === 'Not_started' && objectsMap[object] === 'Not_started') {
            // Update statuses for all items with the same object
            objectsMap[object] = 'Not_started';
        }
        
    });

    // Render DataTable with modified statuses
    renderDataTable(tasks);
}

And everything works as expected.