How to insert a variable into a query

[TomBruam]

Good morning!

I need to insert the selected result in the 'u_group purchases' field inside the 'gr' variable query. But my script is not working. Could someone please review it? The expected result is as per the examples:

If u_group purchases = 575
Then
GlideRecord('sys_user_grmember');gr.addQuery("group.nameLIKE575");gr.query();var users='';while(gr.next()){users+=gr.user.sys_id + ",";}"sys_idIN" + users;

If u_group purchases = 580
Then
GlideRecord('sys_user_grmember');gr.addQuery("group.nameLIKE580");gr.query();var users='';while(gr.next()){users+=gr.user.sys_id + ",";}"sys_idIN" + users;

The script that needs to be revised is this one

javascript: var pdd = new GlideRecord('x_planning_demand');var grpp = pdd.getValue('u_group_purchases');var gr = new GlideRecord('sys_user_grmember');gr.addQuery("group.nameLIKEgrpp");gr.query();var users='';while(gr.next()){users+=gr.user.sys_id + ",";}"sys_idIN" + users;

[Brad Bowman]

You're using addQuery with an addEncodedQuery syntax/format, so try

gr.addEncodedQuery("group.nameLIKE" + grpp);

And your pdd GR looks incomplete as you don't have a pdd.get... or pdd.query(); with if/while(pdd.next())... 

[TomBruam]

I've been working with Service-Now technology and scripts for a short time. I entered the adjustments you suggested and unfortunately it did not work. The script looked like this:

javascript: var pdd = new GlideRecord('x_planning_demand');grpp = pdd.getValue('u_group_purchases');pdd.query();while(pdd.next()); var gr = new GlideRecord('sys_user_grmember');gr.addEncodedQuery("group.nameLIKE" + grpp);gr.query();var users='';while(gr.next()){users+=gr.user.sys_id + ",";}"sys_idIN" + users;


I understand that my error is in this section:

javascript: var pdd = new GlideRecord('x_planning_demand');grpp = pdd.getValue('u_group_purchases');pdd.query();while(pdd.next());

The idea is to capture the user's option in the u_group_purchases field, to use it in the addEncodedQuery.
Can you identify the error?

[Brad Bowman]

It would be closer to this, but with custom fields/tables I can't test it for you:

javascript: var pdd = new GlideRecord('x_planning_demand');pdd.query();var grpp='';while(pdd.next()){grpp = pdd.getValue('u_group_purchases');var gr = new GlideRecord('sys_user_grmember');gr.addEncodedQuery("group.nameLIKE" + grpp);gr.query();var users='';while(gr.next()){users+=gr.user.sys_id.toString() + ",";}}"sys_idIN" + users;

Note that the u_group_purchases field must be the type of string for this to work and contain exactly the same name as a group name.  A more typical/best practice approach would be for the u_group_purchases field on this table to be the type of reference to the sys_user_group table.  Reference fields store values as sys_ids, so if this field is a reference type then change group.name in the encoded query to group.

To test this script outside of a reference qualifier, run the middle part as a fix script with a print line

var pdd = new GlideRecord('x_planning_demand');
pdd.query();
var grpp='';
while(pdd.next()){
    grpp = pdd.getValue('u_group_purchases');
    var gr = new GlideRecord('sys_user_grmember');
    gr.addEncodedQuery("group.nameLIKE" + grpp);
    gr.query();
    var users='';
    while(gr.next()){
        users+=gr.user.sys_id.toString() + ",";
    }
}
gs.print(users);

Then if 'users' does not contain the expected values, add more gs.print lines throughout the script to see which record(s) are returned by the GRs, and the values assigned to variables. 

[jaheerhattiwale]

@TomBruam You are just doing GlideRecord on x_planning_demand table. but not querying it to get the value of grpp. So the below line does not make sense.

var grpp = pdd.getValue('u_group_purchases');

Please try to update script as below and try:

javascript&colon; var pdd = new GlideRecord('x_planning_demand');pdd.addQuery(<QUERY HERE>);pdd.query();if(pdd.next()){var grpp = pdd.getValue('u_group_purchases');var gr = new GlideRecord('sys_user_grmember');gr.addQuery("group.nameLIKE"+grpp);gr.query();var users='';while(gr.next()){users+=gr.user.toString() + ",";}}"sys_idIN" + users;

Note: make sure to replace &colon; to : in above script

Please mark as correct answer if this solves your issue.