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

jmiskey · ‎07-22-2024

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 · ‎07-22-2024

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:

View solution in original post

Satishkumar B · ‎07-22-2024

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.

View solution in original post

Nicholas_Gann · ‎07-22-2024

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 · ‎07-22-2024

I could not get this to work. I could not get the screens you are showing. I am not sure if we do not have that version, or just my inexperience with writing more complex reports.

Nicholas_Gann · ‎07-22-2024

How to Activate or Deactivate the Original Report Builder User Interface - Support and Troubleshooti...

If you are using the old UI for reporting, there should be a hyperlink in the top right to change to the new UI when on a report. See the KA above for screenshots showing where the button is to switch between the two

jmiskey · ‎07-22-2024

Yeah, I toggled that, and didn't have very much luck with either one. Being inexperienced at creating anything other than basic reports, I went to a member of our team who has much more reporting experience than I do. He said it didn't seem to give the Aggregate options on List reports, which is what I am trying to do.

So I don't really know if I am doing something wrong, or if it isn't possible to do what I want from a List report.