Calculate Days Requested for Approval Record

[neil_b]

I am trying to accomplish the following:

• Have a new field "Days Open" that indicates the number of days the approval record has been in a REQUESTED state
• If the STATE of the record is anything other than REQUESTED, Days Open should be BLANK

I already added a field to the table sysapproval_approver for "Days Open". I currently have a scheduled job with the following script.

Scheduled Job:

var approvalRecord = new GlideRecord('sysapproval_approver');
approvalRecord.query();
while(approvalRecord.next()){
  var dat = new GlideDateTime();
  var datedif = gs.dateDiff(approvalRecord.sys_created_on,dat);
  approvalRecord.u_days_open= datedif;
  approvalRecord.update();
}

While this is currently working for when the approval record is created (sys_created_on), this will not work for what I am ultimately trying to accomplish because I need the calculation to be based off the following conditions:

Updated

AND
State = REQUESTED

The reason for this, is we have multi-tier approvals, and instead of creating the approvals one at a time after each record is approved, we modified our flows to create ALL the approval records upfront for visibility.

For example, when a request is submitted this is what happens; 3 approval records are created, 1 record is requested while the remaining 2 are not yet requested. The expectation is to have Days Open indicate the # of days the record has been in a requested state since the state was changed TO requested.

Approver 1 | CREATED ON 8:00 | REQUESTED | UPDATED ON 8:00 | DAYS OPEN 1 |
Approver 2 | CREATED ON 8:00 | NOT YET REQUESTED | UPDATED ON 8:00 | DAYS OPEN |
Approver 3 | CREATED ON 8:00 | NOT YET REQUESTED | UPDATED ON 8:00 | DAYS OPEN |

If approver 1 approves then the table would display as:

Approver 1 | CREATED ON 8:00 | APPROVED | UPDATED ON 10:00 | DAYS OPEN |
Approver 2 | CREATED ON 8:00 | REQUESTED | UPDATED ON 10:00 | DAYS OPEN 1 |
Approver 3 | CREATED ON 8:00 | NOT YET REQUESTED | UPDATED ON 8:00 | DAYS OPEN |

The issue with having it on sys_updated_on is that each time my scheduled job runs to calculate the days open, it updates the sys_updated_on for all the records, so that's an unreliable field to use to determine days open so I am really stuck!

Any help is greatly appreciated! 

[Tushar]

Hi @neil_b 

Please try below updated code and let me know if it works - 

var approvalRecord = new GlideRecord('sysapproval_approver');
approvalRecord.query();

while (approvalRecord.next()) {
    var dateModified = approvalRecord.sys_mod_date.getGlideObject(); // Get the sys_mod_date field
    var currentState = approvalRecord.state.toString(); // Get the state field as a string
    var daysOpen = gs.dateDiff(dateModified, gs.nowDateTime()); // Calculate the number of days

    // Check if the state is "REQUESTED" and the number of days is greater than 0
    if (currentState == 'REQUESTED' && daysOpen > 0) {
        approvalRecord.u_days_open = daysOpen;
        approvalRecord.update();
    } else {
        // Clear the u_days_open field if the state is not "REQUESTED"
        approvalRecord.u_days_open = '';
        approvalRecord.update();
    }
}

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

[neil_b]

@Tushar  , thank you for the quick response!

I have tested out the code and it works but it seems to have a minor caveat. I have attached it in the image below.

In attempting to accomplish the desired result, the sys_updated_on appears to be updating the record due to the scheduled job. Each time my scheduled job runs to calculate the days open (which is every 2 minutes), it updates the sys_updated_on for all the records, so now it has become an unreliable field to use to determine days open so now we are really stuck! 

[Tushar · Accepted solution]

Hi @neil_b 

I see the issue you're facing with the sys_updated_on field getting frequently updated due to the scheduled job, which makes it unreliable for tracking the "Days Open" based on state changes.

In this situation, you can consider an alternative approach to calculate "Days Open" based on the specific state transitions without relying on sys_updated_on.

We will have to create a separate table which will allow you to track state changes in a more granular way, providing a detailed history of transitions.

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar