question Re: Trying to use GlideAggregate to count only specific catalog items in Developer forum

Trying to use GlideAggregate to count only specific catalog items

mrcorbeaux — Fri, 21 Jan 2022 21:25:03 GMT

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

Re: Trying to use GlideAggregate to count only specific catalog items

vkachineni — Fri, 21 Jan 2022 22:20:26 GMT

//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');
    }
}

Re: Trying to use GlideAggregate to count only specific catalog items

mrcorbeaux — Fri, 21 Jan 2022 23:35:10 GMT

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

Re: Trying to use GlideAggregate to count only specific catalog items

vkachineni — Sat, 22 Jan 2022 00:16:16 GMT

//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				
    }    
}

Re: Trying to use GlideAggregate to count only specific catalog items

-O- — Sat, 22 Jan 2022 00:16:51 GMT

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');

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:

Re: Trying to use GlideAggregate to count only specific catalog items

-O- — Sat, 22 Jan 2022 00:34:59 GMT

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.

Re: Trying to use GlideAggregate to count only specific catalog items

-O- — Sat, 22 Jan 2022 00:35:54 GMT

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

Re: Trying to use GlideAggregate to count only specific catalog items

mrcorbeaux — Sat, 22 Jan 2022 00:54:51 GMT

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.

Re: Trying to use GlideAggregate to count only specific catalog items

mrcorbeaux — Sat, 22 Jan 2022 00:58:06 GMT

Vkachineni,

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

Thank you.

Re: Trying to use GlideAggregate to count only specific catalog items

-O- — Sat, 22 Jan 2022 01:02:41 GMT

What do you mean by "software title"?

Re: Trying to use GlideAggregate to count only specific catalog items

-O- — Sat, 22 Jan 2022 01:03:46 GMT

I mean I assumed 566ff7371ba2601064c8a686624bcb98 is a Catalog Item unique ID, but maybe it is a Configuration Item unique ID (sys_id)?

Re: Trying to use GlideAggregate to count only specific catalog items

mrcorbeaux — Mon, 24 Jan 2022 17:01:25 GMT

Hello Janos,

566ff7371ba2601064c8a686624bcb98 is the sys_ID of the catalog item for SnagIT software.

Thanks.

Re: Trying to use GlideAggregate to count only specific catalog items

-O- — Mon, 24 Jan 2022 20:46:17 GMT

If that is the sys_id of a Catalog Item included multiple times in a request (b.t.w. do you want the count for the current request only, or any request) and current is a Requested Item that is part of a Request that also contains the SnagIT Catalog Item, the code should update that field with at least 1. If it does not, then something else must be the problem, like the Requested Items do not in fact belong to the same request, or the Business Rule is scheduled to run at the wrong time, etc. But the code has been tested (of course with proper sys_id). It also seems strange to me, especially now that you confirm that the sys_id is indeed that of the SnagIT Catalog Item, that a software would be included multiple times into the same request, except if this is perhaps a delegated request experience instance?

Re: Trying to use GlideAggregate to count only specific catalog items

mrcorbeaux — Mon, 24 Jan 2022 23:49:38 GMT

That is the correct sys_ID for the SnagIT catalog item.
Yes, I only want to count items (ex SnagIT) on the current request only, not all requests. The reason someone may request 2 SnagITs on a single REQ is because user can request the software for multiple people on one REQ.

Not sure why your code doesn't populate the CorrID. You may be right that a Biz Rule may be an issue. There's an Out-of-Box rule called "Can request be sourced" that looks for catalog items that have a Model in the catalog item record. That's really what I'm trying to do. Only count items (like SnagIT) that have a model. The problem with the code I initially provided is if you request SnagIT (which has a model) and an iphone (no model) the CorrID is 2 when it should be 1.

Below is the rule "Can request be sourced". We don't have plugin com.sn_hamp installed so much of it is irrelevant. I tried striping that code out and making changes to gr.addQuery but still can't get this to work.

Thanks.

sourceable(current);

function sourceable(record) {
  var C_STOCK_ORDER_CAT_SYS_ID = '4109aa5fdb22001015a8ffefbf961984';
  var gr = new GlideRecord("sc_req_item");
  gr.addQuery("request", record.sys_id);
  //A OR (B AND C) not supported
	//Hence going for (A OR B) AND (A OR C)
	var qc = gr.addNotNullQuery("cat_item.model");
	if (GlidePluginManager.isActive('com.sn_hamp')) {
		qc.addOrCondition("cat_item.sys_id", C_STOCK_ORDER_CAT_SYS_ID);
		qc.addOrCondition('variables.dbccd3f2b7621010189e22b5de11a90e', '!=', '');//expecting this catalog item only if HAMP is active, hence not checking if HAMP active or not
		// need to enhance code
		var qc1 = gr.addNotNullQuery("cat_item.model");
		qc1.addOrCondition("variables.6189629fdb22001015a8ffefbf96197f", "!=", "");//Checking if model variable is not null
		qc1.addOrCondition('variables.dbccd3f2b7621010189e22b5de11a90e', '!=', '');
	}
	gr.setLimit(1);
  gr.query();
  if (gr.hasNext()) {
    record.sourceable = true;
		record.update();
  }
}

Re: Trying to use GlideAggregate to count only specific catalog items

-O- — Tue, 25 Jan 2022 00:08:22 GMT

Now that I think of it, it is almost obvious. The issue might be that when a Request is submitted, 1st the Requested Items are created than the parent Request. That means when the Requested Items are running the Business Rule, the Request does not yet exist. So just move the Business Rule to run on the Request table after insert.

As a clarification, do you want to count any Catalog Items that have a model, or only SnagIT?

The code needs to change if moved to sc_request and will need additional changes if any Catalog Item with model should be counted.

Re: Trying to use GlideAggregate to count only specific catalog items

mrcorbeaux — Tue, 25 Jan 2022 18:10:17 GMT

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.

Re: Trying to use GlideAggregate to count only specific catalog items

-O- — Wed, 26 Jan 2022 09:15:00 GMT

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.

Re: Trying to use GlideAggregate to count only specific catalog items

mrcorbeaux — Wed, 26 Jan 2022 22:09:32 GMT

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!

Re: Trying to use GlideAggregate to count only specific catalog items

-O- — Thu, 27 Jan 2022 06:49:26 GMT

You're most welcome 🙂