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

-O- · ‎01-21-2022

If I understand correctly and you want to count how many Catalog Items of a certain kind have been included in a Request, you should change the commented filter

agg.addQuery('sys_id', '566ff7371ba2601064c8a686624bcb98');

to

agg.addQuery('cat_item', '566ff7371ba2601064c8a686624bcb98');

Assuming 566ff7371ba2601064c8a686624bcb98 is the unique id of the Catalog Item of interest.

Though I wonder, is it really the the same Catalog Items will be included multiple times into a request?

Not related, but I urge you to use the code toolbar item when posting code, to get proper and nice formatting and helper's attention:

-O- · ‎01-21-2022

After a closer examination of the script, I can see it has a couple more issues: has

current.sys_id

where it should have:

current.request

The complete revised code:

var agg = new GlideAggregate('sc_req_item');
agg.addQuery('request', current.request);
agg.addQuery('cat_item', '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.request);
	req_update.correlation_id = counter;
	req_update.update();
}

But you may also want to define a custom field for storing this count, or you should find some other field, cause correlation_id has a pretty specific use case: to store the relationship to records in foreign system with which SN could be integrated.

-O- · ‎01-21-2022

Make sure the Catalog Item sys_id is valid for your instance and need before trying out the code.

mrcorbeaux · ‎01-21-2022

Janos,

Yes, what I'm trying to do, for example, is if someone submits an RITM for a specific software title and also an RITM for an iphone on the same REQ, I only want the CorrID field to count the software title (in my example that title has a sys_id of 566ff7371ba2601064c8a686624bcb98). So in this example the REQ would have a CorrID of 1 and not 2.

The first code you provided generated a CorrID of 111 which is the total of all RITMs for that sys_ID. The revised code did not generate anything in the CorrID field.

Thank you.

-O- · ‎01-21-2022

What do you mean by "software title"?