GlideRecord Query

Saurabh _ · ‎01-12-2023

Hi Developer,

I am running a GlideRecord Query written below:

var myObj = new GlideRecord('sys_user_grmember');
myObj.addQuery('group', '=', 'Database');
myObj.query();
gs.info("Returned record count = " + myObj.getRowCount());

I want to know the number of people who are in 'Database' Group. (The number of rows).

But even there are records in the sys_user_grmember table which obey the above query. The output is coming 0 and not the desired number.

Please look into this and let me know where I am wrong!

Thankyou in advance.

Omkar Ranjane · ‎01-13-2023

Hi @Saurabh _ ,

group is a reference field so instead of group name add sys_id of database group.

myObj.addQuery('group', '=', '<sys_id of database group>');

If your question is solved, please close the topic by marking my answer "Accept as Solution". This will help others searching for a similar question and will remove the topic from the unsolved list.

View solution in original post

newhand · ‎01-13-2023

@Saurabh _

The group field is a reference field.

so you should set the sys_id of the group "Database" to the query condition.

var myObj = new GlideRecord('sys_user_grmember');
myObj.addQuery('group', '=', sys id of the group );
myObj.query();
gs.info("Returned record count = " + myObj.getRowCount());

or use dot-walk to build the query

var myObj = new GlideRecord('sys_user_grmember');
myObj.addQuery('group.name', '=', 'Database');
myObj.query();
gs.info("Returned record count = " + myObj.getRowCount());

And by the way .If you only want to get the row count .

getRowCount is not suggested to get the row count of the query. please use GlideAggregate .

Please mark my answer as correct and helpful based on Impact.

View solution in original post

-O- · ‎01-13-2023

In most databases entities (e.g. users, groups, locations, etc.) are stored with at least two attributes: a human readable "name" (in ServiceNow referred to as "display name") and a unique key, an internal identifier (in ServiceNow it is field sys_id). When working with the UI one will use the human readable name - in this case Database, but in the database, in fields that are references to such entities (in ServiceNow called Reference fields) the internal unique key will be stored; something like c4a417ea47106910d2689995536d4310. Therefore in scripting you have to use that; something like:

var myObj = new GlideRecord('sys_user_grmember');
myObj.addQuery('group', '=', 'c4a417ea47106910d2689995536d4310');
myObj.query();
gs.info("Returned record count = " + myObj.getRowCount());

Of course in your database, group Database will have a different specific sys_id. To find out what is the sys_id you can open the record/form for group Database, right-click on the form header and click on the Copy sys_id menu item.

ServiceNow does offer a functionality, called dot-walking, that would enable you te filter by group name, but this is not recommended/not bad practice as it makes your script bread when the data changes - i.e. when the group name is modified for whatever reason. In that case your script would be:

var myObj = new GlideRecord('sys_user_grmember');
myObj.addQuery('group.name', '=', 'Database');
myObj.query();
gs.info("Returned record count = " + myObj.getRowCount());

Notice that the filter is no longer applied to field group, but to field group.name. This method of walking from a field to another field in the referenced table is a functionality exclusive to field of type Reference.

A more fundamental issue is that for queries that aggregate data (in this case count date) one should use class GlideAggregate, not GlideRecord. E.g:

var myObj = new GlideAggregate('sys_user_grmember');
myObj.addQuery('group.name', '=', 'Database');
myObj.addAggregate('count');
myObj.setGroup(false);
myObj.query();
if (myObj.next())
	gs.info("Returned record count = " + myObj.getAggregate('count'));

This is for performance reasons.

Another option is to use the new kid on the block - GlideQuery (which under the hood uses GlideRecord or GideAggregate as needed):

var myCount = new GlideQuery('sys_user_grmember').where('group.name', '=', 'Database').count();

gs.info("Returned record count = " + myCount);

A lot simpler, right?

View solution in original post

Omkar Kumbhar · ‎01-13-2023

Hello @Saurabh _ ,

Please use the below code

var myObj = new GlideRecord('sys_user_grmember');
myObj.addEncodedQuery('group=287ee6fea9fe198100ada7950d0b1b73');
myObj.query();
while(myObj.next()){
gs.info("Returned record count = " + myObj.getRowCount());
}

If I was able to help you with your case, please click the Thumb Icon and mark as Correct.

Omkar Kumbhar · ‎01-13-2023

Hello @Saurabh _ ,

You can also use below code to print he user name from the group

var users=[];
var myObj = new GlideRecord('sys_user_grmember');
myObj.addEncodedQuery('group=287ee6fea9fe198100ada7950d0b1b73');
myObj.query();
while(myObj.next()){
users.push(myObj.user.getDisplayValue().toString());
}
gs.print('users:'+users);

*** Script: users:Beth Anglin,Fred Luddy,ITIL User

If I was able to help you with your case, please click the Thumb Icon and mark as Correct.

Saurabh _ · ‎01-13-2023

Thankyou for the help and also for additional help. While still I don't want to go with the EncodedQuery method in my requirement.

Omkar Ranjane · ‎01-13-2023

Hi @Saurabh _ ,

group is a reference field so instead of group name add sys_id of database group.

myObj.addQuery('group', '=', '<sys_id of database group>');

If your question is solved, please close the topic by marking my answer "Accept as Solution". This will help others searching for a similar question and will remove the topic from the unsolved list.