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

[Ram117]

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]

Found a table 'sys_group_covers_location' which is having entries for groups and locations inherited.

Going to build query based on this table. I will post back the update

[Ram117 · Accepted solution]

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.