Comparing updated_by and opened_by fields from task table

[Anthony Okoth]

I have a custom incident table that is extending the task table. I would like implement a logic that:

1. Re-opens incidents from this table that are in state "Solution Proposed" if the person who opened the incident updates it within 14 days after the incident changes to state "Solution Proposed".  So updated_by == opened_by.
2. Auto-closes incidents that have been in the state "Solution Proposed" for more than 14 days.

I am seeing that the ootb task table field updated_by is a String field containing the email of the user that updated the ticket last. The opened_by field however, with which i would like to make the comparison on, is a reference field. Is dot-walking on the opened_by field to find the email and then transforming it to a String the only way to make a comparison between the two fields? And why is the updated_by field a String and not a Reference?

[HIROSHI SATOH · Accepted solution]

I think it's a good idea to use dot walking to get the email address.

Why updated_by is a String and not a Reference:

The updated_by field is likely designed as a string for efficiency, as storing user information directly as a string might reduce database lookups for frequently updated fields. Since it's often a field that changes regularly, keeping it as a simple data type could be advantageous in terms of performance, even though it sacrifices the flexibility of reference fields.

It's also possible that they're simply holding onto an older design.

[Rajesh Chopade1]

hi @Anthony Okoth 

you can achieve this requirement by BR or Scheduled job. Following sample script will help you:

(function() {
    var gr = new GlideRecord('your_custom_incident_table'); 
    gr.addQuery('state', 'Solution Proposed');
    gr.query();

    while (gr.next()) {
        var openedByEmail = gr.opened_by.email; // Get the email of the user who opened the incident
        var updatedByEmail = gr.updated_by; // Get the email of the last user who updated it
        
        // Check if the incident is updated by the person who opened it
        var daysSinceSolutionProposed = gs.dateDiff(gr.solution_proposed_date, new GlideDateTime(), true);
        
        if (daysSinceSolutionProposed <= 14 && updatedByEmail == openedByEmail) {
            gr.setValue('state', 'Reopened'); // Change to the appropriate state value for "Reopened"
            gr.update();
        } else if (daysSinceSolutionProposed > 14) {
            gr.setValue('state', 'Closed'); // Change to the appropriate state value for "Closed"
            gr.update();
        }
    }
})();

 

Why updated_by is a String?

The updated_by field is a string field because it is intended to hold the email address (or a similar identifier) directly, making it easier to reference or log without needing to perform additional lookups. This allows for quick access to the last updater’s identifier without needing a database join.

i hope my answer helps you to resolve your issue, if yes please mark my answer helpful and correct.

thank you

rajesh