Trying to use GlideAggregate to count only specific catalog items

[mrcorbeaux]

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();
}

[mrcorbeaux]

Janos, The Biz Rule is actually already on the the request table and running after insert. Also, yes I'm trying to count all catalog items with a model. SnagIT is just one of about a dozen we have with a model. Thanks.

[-O- · Accepted solution]

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.

[mrcorbeaux]

Janos,

Fantastic! This is working with your last revised code. The REQs are only counting catalog items that have a model. I didn't even need to change the Biz Rule. You have been a great help.

Thank you!

[-O-]

You're most welcome 🙂