List view of open requested items with no open catalog tasks associated to the requested item.

[Community Alums]

How to know if a requested item has open catalog tasks or not, how to build the filter condition. I want to see the list of requested items with all closed catalog tasks.

[Zach Koch]

You would go to the sc_task table, then use the filter and add, State is one of <your states you need> (can select multiple states), then hit the AND, then put Request Item Is <your RITM number here> , then hit run. It would look like this, for example 

[Community Alums]

@Zach Koch - I want the list of all open requested items with no open catalog tasks.

List of all active requested items with no active catalog tasks associated with them.

[Zach Koch]

Ok, I understand now. The script SAI VENKATESH provided would work for what you are needing. In terms of a filter, unfortunately, I don't believe there is no easy way to bring back this info without a script since it involves multiple tables.

[SAI VENKATESH]

Hi @Community Alums 

I am providing a different way just to get you the details as per your requirement. I have tried in background script:

var requestedItems = new GlideRecord('sc_req_item');
requestedItems.addQuery('active', true); 
requestedItems.addQuery('stage', '!=', 'closed'); 
requestedItems.query();

while (requestedItems.next()) {
    var catalogTasks = new GlideRecord('sc_task');
    catalogTasks.addQuery('request_item', requestedItems.getUniqueValue()); 
    catalogTasks.addQuery('active', true); 
    catalogTasks.query();
    if (catalogTasks.getRowCount() === 0) {
        gs.info('Requested Item ID: ' + requestedItems.number);
    }
}

Thanks and Regards

Sai Venkatesh