Retrieve sys_id from array stocking unique Names

[Nico12]

Hi,

We are trying to retrieve the unique values of a table and then stock their sys_id in an array.

I got the unique values of Software Names in an array but don't achieve to get their associate sys_id.

var arrCmdbSpkgID=[];
var arrCmdbSpkgName=[];
var uniqueSpkgName=[];
var uniqueSpkgID=[];
var uniqueArr = new ArrayUtil();

	var pkgLogiciel = new GlideRecord('cmdb_ci_spkg');
	pkgLogiciel.addEncodedQuery('install_statusNOT IN99,7');
	pkgLogiciel.query();
	while(pkgLogiciel.next())
	{
		arrCmdbSpkgID.push(pkgLogiciel.sys_id.toString());
		arrCmdbSpkgName.push(pkgLogiciel.getValue('name'));
	}

	uniqueSpkgName = uniqueArr.unique(arrCmdbSpkgName);

	for (var e = 0; e < arrCmdbSpkgName.length; e++) {
		if(uniqueSpkgName){ 
			uniqueSpkgID.push(arrCmdbSpkgID[e]);

		}

	}

Regards,

[Brad Bowman · Accepted solution]

Hi Nico,

You could use your script down to the unique array without the ID push, then run another GlideRecord on that table with the addQuery line to see if the name is in that joined array, then push the sys_id of each returned record into your ID array, or simplify it with just one GR, doing the unique test with each result before pushing the ID to an array like this:

var arrCmdbSpkgID=[];
var spkgName = '';
var pkgLogiciel = new GlideRecord('cmdb_ci_spkg');
pkgLogiciel.addEncodedQuery('install_statusNOT IN99,7');
pkgLogiciel.orderBy('name');
pkgLogiciel.query();
while (pkgLogiciel.next()){
	if (pkgLogiciel.name != spkgName) {
		spkgName = pkgLogiciel.name;
		arrCmdbSpkgID.push(pkgLogiciel.sys_id.toString());
	}
}

[Nico12]

Hi Brad,

Thanks for the reply, it helped me a lot.

There is still a part of shadow in what i have to do.

In this software table, there are multiple records with the same Name (exemple : there is 4 McAfee, one for each version stored in SN). I would like to return only one of them to display it in a sn-record-picker in the portal. But for now, it always return all of them.

First i get the unique names with array.unique.

If i make a new GlideRecord with a query on the name, it will return the 4 McAfee again.

var arrCmdbSpkgID=[];
	var arrCmdbSpkgName=[];
	var uniqueSpkgName=[];
var uniqueSpkgID=[];
	var uniqueArr = new ArrayUtil();

	var pkgLogiciel = new GlideRecord('cmdb_ci_spkg');
	pkgLogiciel.addEncodedQuery('install_statusNOT IN99,7');
	pkgLogiciel.query();
	while(pkgLogiciel.next())
	{

		//Tableau retournant les Packages logiciel
		arrCmdbSpkgID.push(pkgLogiciel.sys_id.toString());
		arrCmdbSpkgName.push(pkgLogiciel.getValue('name'));

		uniqueSpkgName = uniqueArr.unique(arrCmdbSpkgName);

		for( var e = 0; e < uniqueSpkgName.length; e++) {
			
			
			var spkgUnique = new GlideRecord('cmdb_ci_spkg');
			spkgUnique.addEncodedQuery('nameIN' + uniqueSpkgName[e]);
			spkgUnique.query();
			while(spkgUnique.next())
			{
				uniqueSpkgID.push(spkgUnique.sys_id.toString());

			}

		}
	}

[Brad Bowman]

With my script approach, you are sorting the query by name, then only adding the sys_id to an array if the name isn't the same as the name on the previous record, so that mean's it unique so it sounds like this will give you the results you are looking for.

[Nico12]

Hi Brad,

You were right, your approach was way more easy and it worked.

Thanks!