How do I filter on REQs to show REQs with no requested items in the related list at the bottom?

[chrisperry]

I am trying to run a filter on the task table for 'My Groups Work' to show any incidents, CATTSKs, changes, etc. that a group needs to actually take action on / fulfill.   Part of the requirement is to show REQs which have not yet been transferred to an incident (easy enough, Transferred to = empty), or that have no requested items associated with them (that is the tricky part).

I can't figure out how to remove the REQs that DO have an RITM associated with them in the related list... it is kind of tricky since the RITMs are shown as a related list at the bottom of the REQ and therefore we cannot access info about those tickets from the REQ itself.

In Fuji, we had a solution where the filter was the REQ's price = empty, because the REQ's price was set to $0.00 whenever an RITM was actually added to the REQ.   However, this filter was broken when we upgraded to Helsinki, as it appears that Helsinki now lists all empty REQ prices = $0.00, regardless of whether an RITM has been added or not.

Any help would be much appreciated, thanks!

[Chuck Tomasi]

Hi Christopher,

Things brings up more questions than answers in my mind...

Q: If there is no RITM on a REQ, what's the point of even having the record? How did the record get created and why?

In concept, the REQ is more or less a shopping cart for one or more RITMs. If there's nothing in the cart, what is the REQ record doing for you?

[chrisperry]

Our process is that an REQ is an 'interaction' record, and one is made whenever the customer contacts the support desk or creates a request from the self-service portal.

The reason an RITM is not always automatically associated with an REQ is so that the support desk can look at the REQ (interaction) record, and determine whether the nature of the interaction is break/fix (and therefore transfer to an incident), or a service request (and therefore add a catalog item).

[Chuck Tomasi]

Thank you for the background information, Christopher.

I would handle this by adding a new field on the sc_request table. A simple true/false should do the trick. Let's call it u_has_ritm

Use a before insert/update business rule on the sc_request to keep the field updated. The BR simply counts the related RITMs something like this:

current.u_has_ritm = false;

var ritm = new GlideAggregate('sc_req_item');

ritm.addAggregate('COUNT');

ritm.addQuery('request', current.sys_id);

ritm.query();

if (ritm.next()) {

        var count = ritm.getAggregate('COUNT');

        if (count > 0)

                  current.u_has_ritm = true;

}

[Chuck Tomasi]

Now you can simply query the REQ records where u_has_ritm is false (or true depending on which you are looking for.)

Wait, I just thought of something, you'll also need an after BR on sc_req_item to keep the sc_request record up to date when RITMs are added/deleted from the parent REQ.