how to query related list record value between sys_user_group and cmn_location ?

Ram117 · ‎09-11-2019

Experts,

On the location table, i have added the Related list 'Groups Covering' ( screenshot below) . I am developing a workflow (context: sc_req_item) and in that, I need to write a script to look at ' Requested For ' users location and get the support group value .

This support group value will be assignment to the catalog task assignment group. I am not sure how do I query the related support group value after getting the location of the user .

Please guide.

Ram117 · ‎09-11-2019

var id = "<sys id of a user record>";
var gr = new GlideRecord('sys_user');
gr.addQuery('sys_id',id);
gr.query();
while(gr.next()) { 
 var grLoc = new GlideRecord('sys_group_covers_location');
 var query = "location="+gr.location;
 grLoc.addQuery(query);
 grLoc.query();
 while (grLoc.next()) { 
  gs.print('group is '+grLoc.group.getDisplayValue()+ ' location is '+grLoc.location.getDisplayValue());
 }
}

I tested with the above code on bG scripts, seems to be working, will place into WF scripts with minor modificatins based on sc_req_item context.

View solution in original post

barshaNow · ‎09-11-2019

You can write a script in the catalog task activity on the workflow, which can query the group table, with the location record.

For ex

var grp = new GlideRecord('sys_user_group');

grp.addQuery('<location field nane in group table>',current.variables.request.requested_for.location);

grp.query();

if (grp.next())

{

task.assignment_group = grp.sys_id;

}

Ram117 · ‎09-11-2019

Thank you for the reply.

Interesting thing is , I do not find any location reference field on the sys_user_group table.

When I got this user story for this environment, the groups covering was already present on the location record form . While trying to figure out, i checked the 'sys_ui_related_list' table for records related to 'sys_user_group' table , found the below list

Looked at the plugins, i found the field Service Management plugin enabled . I am guessing, this is the reason I am find the 'Groups Covering' related list under location table.

Now coming back to the script, as I am unable to find a reference field on the group table, I am unable to build the query.

Ram117 · ‎09-11-2019

I verified with OOB personal instance. If I enable Field Service Management plugin, i get the 'Groups Covering' related list .

Now how do I query the group once I get the location ?

Ram117 · ‎09-11-2019

Printed out the entire list of a location record after tagging a group record to it , i do not see any property which displays the Group information on location record.

i tried the below script on a background scrip to check if group information gets printed once it is tagged to a location.

< initial statements to query the location of a specific 'requested_for' >

var grLoc = gr.location.getRefRecord();

for (var name in grLoc) {

gs.print('value of the col '+ name + ' is ' +grLoc[name]);

}

This printed out all information on the location record except the group information.