Complex Query to retrieve all RITMS for which the current user is assigned to approve

[jamesmcwhinney]

Are there any GlideRecord query gurus out there that could assist with writing a query?

I am trying to write a glide query to get all requested items for which any of the following is true:

- The current user has an approval request associated with the RITM

- The current user has an approval request associated with the parent request

- The current user is the requested for or requested by user for the parent request

- The RITM field "department_it" is set to true

Would any one have an insight on how to perform this?

Here is what I have so far (not working):

var currentUserID = gs.getUserID();

//Where RITM is in IT catalog

var qc = current.addCondition("u_department_it", "true");

//Where user is an approver of the Request

var req = current.addJoinQuery('sc_request','request', 'sys_id');

var reqapp = req.addJoinQuery('sysapproval_approver','sys_id', 'sysapproval');

reqapp.addOrCondition('approver', currentUserID );

//Where user is the requested by for the request

reqapp.addOrCondition('opened_by', currentUserID );

//Where user is the requested for for the request

reqapp.addOrCondition('u_request_requested_for', currentUserID );

//Where user is an approver of the RITM

var ritmapp = current.addJoinQuery('sysapproval_approver','sys_id', 'sysapproval');

ritmapp.addOrCondition('approver', currentUserID );

[jordanh]

What is your use case for this? I don't think you can get all of what you are looking for from one GlideRecord query because you will have to get the RITMs where the user is an approver from the approval table, but then you can't get the RITMs where your other conditions are met and the user is not an approver. There are definitely options available, but need to understand what you are trying to accomplish here.

[jamesmcwhinney]

Some users have been granted "read only" roles via ACLs to all RITMs of type u_department_it = true.

This works fine, however when these users view the list of RITMs, the results show "20 items removed" etc as their query inevitably attempts to show results for which they have no access.

To get around this, I am attempting to put a before-query-business-rule in place for this role such that their query will be adjusted to show only those results which the ACLs give them access to read, eliminating the "20 results removed" message and giving them a better user experience.

From what I understand, this is the same approach SN takes OOTB against the incidents table:

if (!gs.hasRole("itil") && gs.isInteractive()) {
   var u = gs.getUserID();
   var qc = current.addQuery("caller_id", u).addOrCondition("opened_by", u).addOrCondition("watch_list", "CONTAINS", u);
   gs.print("query restricted to user: " + u);
}

[Valor1]

You'll need to run a separate query for approvals and collate the results -- you can't (easily?) do this in one query.

Example (should get you close):

var requestItemSidArr = [];
var requestSidArr = [];

var appGr = new GlideRecord('sysapproval_approver');
appGr.addEncodedQuery('approver=javascript:getMyApprovals()^state=requested^sysapproval.sys_class_name=sc_request^ORsysapproval.sys_class_name=sc_req_item');
appGr.query();
while (appGr.next()){
   if (appGr.sysapproval.sys_class_name == "sc_request"){
           requestSidArr.push(appGr.sysapproval + "");
   }
   else {
           requestItemSidArr.push(appGr.sysapproval + "");
   }
}

reqapp.addEncodedQuery('request.requested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORrequest.opened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORrequestIN' + requestSidArr + "^ORsys_idIN" + requestItemSidArr + "^ORu_department_it=true");

Let's break down that last line:

• request.requested_for DYNAMIC me
• --OR--
• request.opened_by DYNAMIC me
• --OR--
• request IS ONE OF [list generated by approval query]
• --OR--
• request item (sys_id) IS ONE OF [list generated by approval query]
• --OR--
• u_department_it IS true

Please note that this is a VERY expensive query -- if you have a lot of requests, this may take around 3-5 seconds to run.

[jamesmcwhinney]

Thanks, yeah I was hoping to do it all in one query.

It seems like the NQ operator might get me close, except I havent yet found a way to put an OR around it.

Here is where I am at so far thanks to this thread

Re: Ritm query with approvers

//Get Current User
var u=gs.getUserID();


//Where current user is the requested for or requested by, or if the RITM is an IT request
var qc = current.addCondition("u_department_it", "true");
qc.addOrCondition('opened_by', u);
qc.addOrCondition('u_request_requested_for', u);


//Build Separate Query of RITMs for which current user is an approver of the RITM
var ritm2 = new GlideRecord('sc_req_item');
var ritmapp = ritm2.addJoinQuery('sysapproval_approver','sys_id','sysapproval');
ritmapp.addCondition('approver',u);


//Build Separate Query of RITMs for which current user is an approver of the parent request
var ritm3 = new GlideRecord('sc_req_item');
var req = ritm3.addJoinQuery('sc_request','request','sys_id');
var reqapp = req.addJoinQuery('sysapproval_approver','sys_id','sysapproval');
reqapp.addCondition('approver',u);


//Merge the queries
var q1 = '^NQ'+ritm2.getEncodedQuery();
var q2 = '^NQ'+ritm3.getEncodedQuery();


qc.addEncodeQuery(q1);
qc.addEncodeQuery(q2);

gs.print("query restricted for custom roles");