Report creation with all Open/inProgress RITMS but with completed sctasks

[Fotina Galieb]

Hello,

I got an issue with some RITMs which remain in state open/in progress even when all sctasks were already completed long time ago. I need to get the list of such RITMs. 

I found a great solution in postSolved: Report on all Open RITMS but all the related sctas... - ServiceNow Community , thank you @Ankur Bawiskar. However after migration to Xanadu, my report is empty.

I have created identiacal script include as client callable.

var RRRRRRR = Class.create();

RRRRRRR.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    getRITM:function(){

        var arr = [];

        var gr = new GlideRecord("sc_req_item");

        gr.addEncodedQuery("state=2"); // state is in progress

        gr.query();

        while(gr.next()) {

            var closedCount = 0;

            var taskRec = new GlideRecord("sc_task");

            taskRec.addQuery("request_item", gr.sys_id);

            taskRec.query();

            var totalCount = taskRec.getRowCount();

            while(taskRec.next()){

                if(taskRec.active.toString() == 'false'){

                    closedCount++;

                }

            }

            if(closedCount == totalCount && totalCount != 0){

                // if count is same then all tasks are closed then push RITM sys_id in array

                arr.push(gr.getValue('sys_id'));

            }

        }

        return arr.toString();

    },

    type: 'RRRRRRR'

});

Then a report in the same application, with the filter as per solution,

Condition of Report

sys_id [IS ONE OF] javascript: new RRRRRRR().getRITM();

 When I checked client callable, i was asked for role, so i used itil role.

@Ankur Bawiskar do you have any idea what could be changed in Xanadu or what i did wrong? Before migration it worked perfectly.

Thank you so much in advance,

Best regards,
Fotina

[BriTaylor]

Where is says "Greater than or Equal to 1" change that to None.

Additionally change your conditions to use the open states instead of the closed states, and active=true.

This way the report will look for any RITMs where there are no SCTASK records that are still open.

[Fotina Galieb]

Thank you so-so much @BriTaylor! It looks exactly as you said!

[Uncle Rob · Accepted solution]

Conditions on related records are sometimes a little harder to express.
Also, you need to express how many you expect.  You're saying "GREATER THAN OR EQUAL TO 1" before your conditions.

The conditions you SHOULD Have are more like "NONE", where sc_task.active = true.
AKA:  "Count all the active tasks, and if that count is 0, then I've passed the conditions, therefore show me the RITM".

WARNING!!  This will be true for any RITM that is *also* waiting for approval before sc_tasks generate, so you may want to add a condition to your main RITM conditions like "stage != Approval"

[Fotina Galieb]

Hi @Uncle Rob Thank you so-so much for warning, thats right, I didnt think about it, very important remark!

[Ankur Bawiskar]

@Fotina Galieb 

I agree with Rob here.

You can try to leverage related list conditions here

But for the script did you debug if it is getting called?

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