Subtract date from Today date to "Updated" field & update result in "last_updated" field.

[hanimi9623]

I have two fields in incident form. One is property field: sys_updated_on. Second one custom field: u_last_updated (integer).

requirement: Creating a report for "how many days idle without updating the incident". In pre-size, need to subtract from "Today" time to "Updated" field time, and that result should be update on custom field: last_updated (integer).

For this, I created a custom field:u_last_updated (integer) to display the no. of idle days. Also, I created a
After Update BR. But it didn't worked. Could you please check and correct me where I did wrong.

Script: After BR/Update

Provided No conditions 

Java script: 

var updatedDateTime = new GlideDateTime(current.sys_updated_on);

    var now = new GlideDateTime();

    var duration = GlideDateTime.subtract(now, updatedDateTime);

    var msDiff = duration.getNumericValue();

    var days = Math.floor(msDiff / (1000 * 60 * 60 * 24));

    current.u_last_updated_days = days;

[Roshnee Dash · Accepted solution]

1. Create a Business Rule on the Incident [incident] table.
2. Set it to run:
   • When: Before
   • Insert: False
   • Update: True
   • Filter Conditions: You can add conditions to limit when this runs (e.g., only when certain fields change).
3. Script (in the "Advanced" tab):

(function executeRule(current, gsn, gs) {
    var lastUpdated = current.sys_updated_on.getGlideObject(); // Get GlideDateTime
    var now = new GlideDateTime(); // Current time

    var diff = GlideDateTime.subtract(now, lastUpdated); // Difference in milliseconds
    var daysIdle = Math.floor(diff / (1000 * 60 * 60 * 24)); // Convert to days

    current.u_last_updated = daysIdle; // Set the custom field
})(current, gsn, gs);

• GlideDateTime.subtract() returns the difference in milliseconds.

You can also use a Scheduled Job or Scheduled Script Execution if you want this to run periodically (e.g., daily) instead of on update.

1. Navigate to:
   System Definition → Scheduled Jobs → New

2. Choose:
   "Scheduled Script Execution"

3. Fill in Basic Info:

   • Name: Update Incident Idle Days
   • Run: Daily (or as needed)
   • Time: Choose a time when system load is low (e.g., midnight)

4. Script (in the Script field):
   You can apply query condition as well.

(function() {
    var now = new GlideDateTime();
    var gr = new GlideRecord('incident');
    gr.query();

    while (gr.next()) {
        var lastUpdated = gr.sys_updated_on.getGlideObject();
        var diff = GlideDateTime.subtract(now, lastUpdated); // milliseconds
        var daysIdle = Math.floor(diff / (1000 * 60 * 60 * 24)); // convert to days

        gr.u_last_updated = daysIdle;
        gr.update(); // Save the updated value
    }
})();

[Roshnee Dash · Accepted solution]

1. Create a Business Rule on the Incident [incident] table.
2. Set it to run:
   • When: Before
   • Insert: False
   • Update: True
   • Filter Conditions: You can add conditions to limit when this runs (e.g., only when certain fields change).
3. Script (in the "Advanced" tab):

(function executeRule(current, gsn, gs) {
    var lastUpdated = current.sys_updated_on.getGlideObject(); // Get GlideDateTime
    var now = new GlideDateTime(); // Current time

    var diff = GlideDateTime.subtract(now, lastUpdated); // Difference in milliseconds
    var daysIdle = Math.floor(diff / (1000 * 60 * 60 * 24)); // Convert to days

    current.u_last_updated = daysIdle; // Set the custom field
})(current, gsn, gs);

• GlideDateTime.subtract() returns the difference in milliseconds.

You can also use a Scheduled Job or Scheduled Script Execution if you want this to run periodically (e.g., daily) instead of on update.

1. Navigate to:
   System Definition → Scheduled Jobs → New

2. Choose:
   "Scheduled Script Execution"

3. Fill in Basic Info:

   • Name: Update Incident Idle Days
   • Run: Daily (or as needed)
   • Time: Choose a time when system load is low (e.g., midnight)

4. Script (in the Script field):
   You can apply query condition as well.

(function() {
    var now = new GlideDateTime();
    var gr = new GlideRecord('incident');
    gr.query();

    while (gr.next()) {
        var lastUpdated = gr.sys_updated_on.getGlideObject();
        var diff = GlideDateTime.subtract(now, lastUpdated); // milliseconds
        var daysIdle = Math.floor(diff / (1000 * 60 * 60 * 24)); // convert to days

        gr.u_last_updated = daysIdle;
        gr.update(); // Save the updated value
    }
})();

[hanimi9623]

Thank you so much.

[Mark Manders]

What is the result of your query? Did you put logs in it to see where it went wrong? Is the calculation wrong, or are you just not updating anything?

[Ankur Bawiskar · Accepted solution]

@hanimi9623 

your business rule should be Before and not after

Updated your script as this

(function executeRule(current, previous /*null when async*/) {
    var updatedDateTime = new GlideDateTime(current.sys_updated_on);
    var now = new GlideDateTime();
    // Get the difference in milliseconds
    var msDiff = now.getNumericValue() - updatedDateTime.getNumericValue();
    // Convert milliseconds to days
    var days = Math.floor(msDiff / (1000 * 60 * 60 * 24));
    current.u_last_updated_days = days;
})(current, previous);

If my response helped please mark it correct and close the thread so that it benefits future readers.