Trying to use GlideAggregate to count only specific catalog items

mrcorbeaux · ‎01-21-2022

The below workflow Run Script is using GlideAggregate to count how many RITMs are included in an REQ and adds that number to the correlation_id field of the REQ. I am trying to update it so that it only adds to the correlation_id field if specific catalog items are requested while ignoring others on the same REQ. I tried a number of things including the part I commented out which was an attempt to only count specific catalog item sys_ids, but no luck.
Any suggestions?

var agg = new GlideAggregate('sc_req_item');
agg.addQuery('request', current.sys_id);
//agg.addQuery('sys_id', '566ff7371ba2601064c8a686624bcb98');
agg.addAggregate("COUNT");
agg.query();
if(agg.next()){
var counter = agg.getAggregate("COUNT");
var req_update = new GlideRecord('sc_request');
req_update.get(current.sys_id);
req_update.correlation_id = counter;
req_update.update();
}

-O- · ‎01-26-2022

In that case the code should be something like:

var agg = new GlideAggregate("sc_req_item");
// Select Requested Items of the current Request
agg.addQuery("request", current.getUniqueValue());
// Select only those Requested Items that were created from Catalog Items that have a model associated with it
// It may be necessary to further qualify the selection here, but specifying model category (so that only software models are included)?
agg.addNotNullQuery("cat_item.model")
agg.addAggregate("COUNT");
agg.query();
if (agg.next()) {
	var counter = agg.getAggregate("COUNT");
	current.correlation_id = counter;
}

Also because the Business Rule runs on the same table as the one modified, this must be a Before Business Rule, not an After Business Rule.

'Cause we know the rules:

- No updating current in Business Rules; that means not only calling current.update(), but also loading the current record and updating it.

- If the current record is modified, it has to be a Before Business Rule

- If a different record is modified, it has to be an After Business Rule, unless what is modified is not loaded into the form in which case it can also be an Async Business Rule to allow faster form load times.

View solution in original post

vkachineni · ‎01-21-2022

//This worked on PDI

var agg = new GlideAggregate('sc_req_item');
agg.addQuery('request', "c3b22a00db323300868370e2399619f0"); //REQ sys_id
agg.addAggregate("COUNT");
agg.query();
if(agg.next()){
    var counter = agg.getAggregate("COUNT");
    gs.log("counter = " + counter, '@@@TESTING');
    var req_update = new GlideRecord('sc_request');
    req_update.get("c3b22a00db323300868370e2399619f0"); //Load the same REQ
    req_update.setValue('correlation_id',counter); // this is the only change made
    var id = req_update.update();
    if(id){
        gs.log("Updated = " + req_update.number, '@@@TESTING');
    }
}

Please mark Correct and click the Thumb up if my answer helps you resolve your issue. Thanks!
Vinod Kumar Kachineni
Community Rising Star 2022

mrcorbeaux · ‎01-21-2022

Thanks but unfortunately that did not work. I replaced c3b22a00db323300868370e2399619f0 with one of my sys_ids but REQ's CorrID did not count it.

vkachineni · ‎01-21-2022

//Just to make sure, the sys_id you used is of a REQ...right?

//Can you check with try catch{}. Please check the log and see if an exception is logged

var agg = new GlideAggregate('sc_req_item');
agg.addQuery('request', "c3b22a00db323300868370e2399619f0"); //REQ sys_id
agg.addAggregate("COUNT");
agg.query();
if(agg.next()){
    var counter = agg.getAggregate("COUNT");
    gs.log("counter = " + counter, '@@@TESTING');
    var req_update = new GlideRecord('sc_request');
    req_update.get("c3b22a00db323300868370e2399619f0"); //Load the same REQ
    req_update.setValue('correlation_id',counter); // this is the only change made
	try{
		var id = req_update.update();
		if(id){
			gs.log("Updated = " + req_update.number, '@@@TESTING');
		} else{
			gs.log("Not Updated = " + req_update.number, '@@@TESTING');
		}
	}
	catch (ex) 
	{
		var message = ex.getMessage();
        gs.log("Error: " + message, '@@@TESTING'); //Check the log				
    }    
}

Please mark Correct and click the Thumb up if my answer helps you resolve your issue. Thanks!
Vinod Kumar Kachineni
Community Rising Star 2022

mrcorbeaux · ‎01-21-2022

Vkachineni,

The sysID is actually of a specific catalog item and not the REQ. My original code was misleading.

Thank you.