Query REQ Table and Return All Records with Multiple RITMs in Them

[jmiskey]

What is the easiest way to get a list of all active REQ records that have more than one RITM assigned to them?  I am not sure if it can be done with some simple filters on the REQ table, or if it requires code involving GlideRecords.

Thanks

[Nicholas_Gann · Accepted solution]

This can be done with standard reporting functionality, provided you are using the new reporting UI.

Create a report against the Request (sc_request) table

On the filter condition builder, click on RELATED LIST CONDITIONS

Select Requested Item > Request

You will see there is a blue hyperlink stating 'Greater than or Equal to 1' click on this and change the 1 to a 2

See below:

[jmiskey]

Oh, I went back tried again, and saw what the issue was, using the new Reporting UI (which I had not used before).  I just wasn't getting the criteria selection boxes to pop-up (not as obvious as it was on the old version).  Once I did that, I was able to apply your filters, and it looks like it worked!

Thank you.

[Nicholas_Gann]

Good to hear, I was about to post a screenshot, although it seems you don't need it now but may as well post for future audiences:

The only thing this won't give you is numbers of RITMs against each REQ but the other solution might give you that.

[jmiskey]

Yeah, I do not need to know them.  I just need to know which of the active REQs have multiple RITMs in them.

Thanks again!

[Satishkumar B · Accepted solution]

Hi @jmiskey 

Using GlideRecord:

// Initialize GlideRecord for the RITM table
var ritmGR = new GlideRecord('sc_req_item');
ritmGR.addActiveQuery(); // Filter only active RITM records
ritmGR.query();

// Object to keep track of REQ IDs and their count of RITMs
var reqCountMap = {};

// Loop through RITM records
while (ritmGR.next()) {
    var reqId = ritmGR.request; // Get REQ ID from the RITM record
    if (reqCountMap[reqId]) {
        reqCountMap[reqId]++;
    } else {
        reqCountMap[reqId] = 1;
    }
}

// Collect REQ IDs with more than one RITM
var reqIdsWithMultipleRITMs = [];
for (var reqId in reqCountMap) {
    if (reqCountMap[reqId] > 1) {
        reqIdsWithMultipleRITMs.push(reqId);
    }
}

// Output REQ records with more than one RITM
var reqGR = new GlideRecord('sc_request');
reqGR.addQuery('sys_id', 'IN', reqIdsWithMultipleRITMs.join(','));
reqGR.query();

while (reqGR.next()) {
    gs.info('REQ Record with multiple RITMs: ' + reqGR.number);
}

• You can run this script in a Background Script.
• Navigate to System Definition > Scripts - Background, paste the script, and execute it.
  
  

Using Report/Filter Approach

• Create a Report:

  • Navigate to Reports > Create New.
  • Choose the Requested Item [sc_req_item] table.
  • Add a filter to select Active records.
  • Group by the Request [request] field.
  • Use a Sum or Count function to count the number of RITM records per REQ.
  • Add a condition to filter for groups where the count is greater than 1.

• Save and Run the Report:

  • Save the report with a meaningful name.
  • Run the report to see the list of REQ records that have more than one RITM.

……………………………………………………………………………………………………

Please Mark it helpful 👍and Accept Solution✔️!! If this helps you to understand. 

[jmiskey]

Thank you.  That background script gave me exactly what I need.