Order Guide Summary Notification

[DylanB]

Hello, we have an order guide for new hires. When that order guide is submitted, I would like to send a notification to Requested For summarizing the order. I am running into a few problems with this. 

Problem 1: Ideally the notification runs off the sc_request table but that table doesn't have the Order Guide field to be used as a condition. The other option then is running off the sc_req_item table, but this will generate one notification for each RITM and the notification should only send once regardless of how many items are ordered. 

Another thought was to modify the workflow to check if the request is an order guide and if so, trigger an event, but of course this is the same problem of sc_request not having the Order Guide field, and using sc_req_item triggers the notification multiple times.

Problem 2: The other issue is summarizing the items ordered. I would like to link right to the Request Summary page on the portal, but this is not OOB functionality, so I created a mail script to construct the link by grabbing the sys_id of the request. Again I'm running into the same problem as above because for this to work correctly, the notification needs to run off the sc_request table otherwise it grabs the sys_id of the RITM, not the REQ. 

Anyone have any experience with this or know of a better way to get this working?

[Claude DAmico]

You could add a new field to sc_request for Order Guide and populate it when the request is created, or after. That would give you what you need. Just need to do a query to sc_req_item for items referencing the request and pull the order guide from there.

Might need to do the reverse though so after sc_req_item is created just update the parent sc_request record if the new order guide field is empty (specifically if it's empty so it doesn't get touched for every item.)

[DylanB]

We considered adding an Order Guide field to the sc_request table, but our organization is leary about creating new fields on core tables so I think we are going to find another solution. 

[DylanB · Accepted solution]

After a series of compromises, we got this working by doing the following: 

Create a flow that runs when a Request is created, then look up an SCTASK where the REQ number is the same as the triggered REQ, and the SCTASK is an order guide SCTASK.

If a record is not found, then the REQ was not created from an order guide and the rest of the flow fails (correctly). If a record is found, the entire REQ was created from an order guide. The flow then generates a link to the Portal Request Summary page for the REQ and sends that in an email to the Requested For user. From there, the end user can see all items ordered on that REQ.

Generating the link to the Portal Request Summary page was more complicated than expected. I used a slightly modified version of this link generator by Kieran Ason to get it done. 

A downside of this method is that this flow runs on every REQ created, and most of them don't have anything to do with an order guide, so the flow execution log is filled with lots of failure logs. Those failures are "accurate", but still not fun to see. I'm sure there's more elegant ways to accomplish this need, but hey, it works.

Flow screenshot below for those curious.