script help with a business rule to prevent closing a problem if there are open child tasks

[patricklatella]

hi all,

I've build a couple similar business rules but am struggling with this one.   I have a related list of child tasks on our Problem records ([problem_task] table).

And what I'm trying to do is use "after" business rule on the [Problem] table to query the "problem_task" table to see if there are associated tasks, and then check their "state" field, and if the state on the child tasks is not one of the 3 complete states, to abort the closing of the parent Problem record.   Here is my script with comment notes.   thanks!

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

var prbNumber = current.number; //Problem number

gs.info('++++prbNumber1 is '+prbNumber);//this returns correctly

var gr = new GlideRecord('problem_task'); // This will be the child task table

gr.addQuery('problem',prbNumber); //field on the child table where the parent record number is = "problem"

gr.query();

gs.info('++++prbNumber2 is '+gr.number);//this returns undefined

if(gr.next()){

gs.info('++++state is '+gr.state);//this is not reached even if there is a record on the problem_task table with the "problem" field matching the prbNumber

if (gr.state == '3' || gr.state == '4' || gr.state == '7'){

  gs.addErrorMessage('Submission aborted, all associated tasks must be closed before this Problem record can be closed.');

//current.setAbortAction(true);

//current.state = previous.state;

}

}

})(current, previous);

[Goran WitchDoc]

yes. Since for each record it's checking, it doesn't know the number of the problem, it only has the sys_id. So it need to do another servercall to get the number and then compare that with the number you have filled in to compare to see if it matches the query or not. And this it has to do with each record in the table (depending on how you setup the order of the query). so basically, if you have 1000 tasks, and you do a query like that.. it will do 1001 server calls (1 for the orginal query and 1000 to fetch the problem number). Now cache will speed things up since most of those tasks has the same problem sys_id, but I think you get the point.

[patricklatella]

thanks Juan...you were correct that I should have been looking for the sysID

[Goran WitchDoc · Accepted solution]

Hi Patrick,

Without checking to deep into the code, I would say this line is the problem:

gr.addQuery('problem',prbNumber); //field on the child table where the parent record number is = "problem"

The problem field contains the sys_id of the problem, not the problem number

I would do something like this:

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

var prb = current.getUniqueValue(); //get sys_id

var gr = new GlideRecord('problem_task'); // This will be the child task table

gr.addQuery('problem',prb); //field on the child table where the parent record number is = "problem"

gr.addActiveQuery();/Only want to look if there is active tasks

gr.setLimit(1);//Set limit to 1 since it only require 1 active task to abort

gr.query();

if(gr.next()){

  gs.addErrorMessage('Submission aborted, all associated tasks must be closed before this Problem record can be closed.');

current.setAbortAction(true);

}

})(current, previous);

[patricklatella]

thanks Goran!   that was it...forgot about the sys_id being the value for the "problem" field on the problem_task field.

thanks as well for the additional explanation regarding dot.walking in a query.