Issue with GlideRecord for Request & Requested Item

[manish123]

I have to generate a Gliderecord report, where Request is still open and attached Requested Items are closed or closed incomplete.

var str = 'request.request_stateNOT INclosed_complete,closed_incomplete^stateINclosed_complete,closed_incomplete';

var gr = new GlideRecord('sc_req_item');

gr.addEncodedQuery(str);

gr.query();

while(gr.next())

{

gs.print('Requested Item is :' + gr.request.number);

}

However, I think this would not capture our one scenario. That is

REQ001 has got 3 RITMs RITM1, RITM2 & RITM3.

Out of 3 RITMs, RITM2 & RITM3 are closed however RITM is still open. Then as per my gliderecord that Request also get captured. Which I don't want because its one RITM is still open.

Could anyone of you please let me know what needs to be corrected to avoid such rows.

Any help must be appreciated

[bbarber9]

Can you clarify the question a little bit? Are you trying to find requests where the request is still open and ALL the requested items are closed?

[manish123]

Thanks for your response.

And Yes, you're right. Breyton

[bbarber9]

I can't think of a good way to do it with a single query, but you can do it with a little extra logic:

var reportRequests = [];

var request_gr = new GlideRecord('sc_request');

request_gr.query();

//loop through all requests

while(request_gr.next()){

    //check each RITM in request

    var ritm_gr = new GlideRecord('sc_req_item');

    ritm_gr.addQuery('request', request_gr.sys_id);

    ritm_gr.addEncodedQuery('stateNOT INclosed_complete,closed_incomplete')

    ritm_gr.query();

    //if there aren't any records that aren't closed they go in the list

    if(!ritm_gr.next()){

    reportRequests.push(request_gr.getValue('sys_id'));

  }

}

//do whatever you want with reportRequests