How to check a field value for all change tasks?

Aishwarya Selv1 · ‎05-17-2022

For example, I have added a new field (abc) in change requests form, this abc field is already available in all change tasks form so I have to get the abc field value from all the change tasks and display the maximum value in the abc field of change request form. How will I query it?

Mohit Kaushik · ‎05-17-2022

Hi Aishwarya,

Could you please tell me if this solution is for old data or you want to make a generic solution.

For old data you can write a Background script or fix script as shown below:

var ctask = new GlideRecord('change_task');
ctask.query();
while(ctask.query()){
    var change = new GlideRecord('change_request');
    change.addQuery('sys_id',ctask.change_request);
    change.query();
    if(change.next()){
        if(change.abc<ctask.abc){ //instead of abc please use your field name
            change.abc = ctask.abc;
            change.setWorkflow(false);
            change.update();
        }
    }
}

Generic Solution:

As @Aman Kumar told you need to create an after Insert and Update Business rule on Change Task table and that should be able to handle your requirement. You can write below script in your Business Rule.

(function executeRule(current, previous /*null when async*/ ) {

    // Add your code here
    var change = new GlideRecord('change_request');
    change.addQuery('sys_id', current.change_request);
    change.query();
    if (change.next()) {
        if (change.abc < current.abc) { //instead of abc please use your field name
            change.abc = current.abc;
            change.update();
        }
    }
})(current, previous);

Please mark this as correct and helpful if it resolved the query or lead you in right direction.

Thanks,
Mohit Kaushik
Community Rising Star 2022

Thanks,
Mohit Kaushik
ServiceNow MVP (2023-2025)

View solution in original post

Aishwarya Selv1 · ‎05-17-2022

It's working fine but one small issue, once the abc field value in change request displays the highest value, later when the abc field value for tasks change to lower value, it is not displayed in change request.

For example,

chg task 1 value--> 3

chg task 2 value--> 2

now according to the query, change request abc field will display value 3, its working fine.

Now I modify chg task 1 value -->1, chg task 2--> 2(remains same), now the highest value is 2 (from chg task 2), technically it should display 2 but it still stays in 3 (which was the highest value before)

How should I rectify it?

Mohit Kaushik · ‎05-17-2022

Ahh, okay, in that case you need to tweak the script a bit.

(function executeRule(current, previous /*null when async*/ ) {

    // Add your code here
    var change = new GlideRecord('change_request');
    change.addQuery('sys_id', current.change_request);
    change.query();

    if (current.isNewrecord()) {
        if (change.next()) {
            if (change.abc < current.abc) { //instead of abc please use your field name
                change.abc = current.abc;
                change.update();
            }
        }
    } else {
        // Find all the tasks for this change request first and check the value of abc field.
        var max = current.abc;
        var ctask = new GlideRecord('change_task');
        ctask.query('change_request', current.change_request);
        ctask.query();
        while (ctask.next()) {
            if (ctask.abc > max) {
                max = ctask.abc;
            }
        }
        if (change.next()) {
            change.abc = max;
            change.update();
        }
    }
})(current, previous);

Please mark this as correct and helpful if it resolved the query or lead you in right direction.

Thanks,
Mohit Kaushik
Community Rising Star 2022

Thanks,
Mohit Kaushik
ServiceNow MVP (2023-2025)

Mohit Kaushik · ‎05-19-2022

Hi Aishwarya,

Did you get a chance to check my suggestion? If it solves your query then please mark my answer correct and helpful so that this question can be moved from unanswered queue and others can get benefited from the same.

Thanks,
Mohit Kaushik
Community Rising Star 2022

Thanks,
Mohit Kaushik
ServiceNow MVP (2023-2025)