Business Rule that checks to see if catalog item variable matches table value and updates

[Jayce Antrum]

Hi all,

I'm new to scripting in ServiceNow and have a requirement that I'm a little stuck on. This is preferred to be done in a business rule.

I have a catalog item with a variable called Workday Requisition Number (ritm_supplies_workday_requisition_number).This is a string field. The requirement is that when this is filled in with a requisition number, automatically update another read-only field on the RITM/TASK called Workday PO Number (ritm_supplies_workday_po_number). Workday PO number is a reference field to the proc_po table and would pull in a PO record.

On the proc_po table, we have a field called "u_workday_purchase_request". Essentially what I want to do is when a user enters their workday requisition number on the RITM variable, I want the system to query the proc_po table and if there is a matching value in the "u_workday_purchase_request" field, then automatically update the Workday PO Number field on the catalog item (ritm_supplies_workday_po_number) to the PO record that has the matching purchase requisition number.

High level flow would be:

User enters Workday Requisition Number on RITM variable > IF this Workday Requisition Field on the RITM EQUALS the "u_workday_purchase_request" field on the purchase order table, THEN UPDATE the Workday PO Number (ritm_supplies_workday_po_number) field on the RITM ticket to the corresponding PO record.

See below for the current code of my before business rule although I think it's pretty wrong at the moment. Thank you in advance. I have this currently on the sc_req_items table.

(function executeRule(current, previous /*null when async*/) {

	var lookup= new GlideRecord('proc_po');


lookup.addQuery('u_workday_purchase_request'),current.u_workday_purchase_request,

lookup.query();


if(lookup.next())
{


current.setDisplayValue('ritm_supplies_workday_po_number',lookup.getDisplayValue());


}

})(current, previous);

[JenniferRah]

Is the u_workday_purchase_request field a Reference field? If so, it stores the sys_id of the RITM and not the number. There's also a syntax error in your Business Rule code. So your query would have to look like this: 

(function executeRule(current, previous /*null when async*/) {

	var lookup= new GlideRecord('proc_po');
	lookup.addQuery('u_workday_purchase_request.number', current.variables.ritm_supplies_workday_requisition_number);
	lookup.query();
	if(lookup.next()){
	current.setDisplayValue('ritm_supplies_workday_po_number',lookup.getDisplayValue());
	}
})(current, previous);

[Jayce Antrum]

Hi Jennifer,

Thanks for helping with the code syntax, appreciate all the little things as I'm still learning.

For more context, u_workday_purchase_request is a string field, not a reference. So essentially I am trying to tie the string value entered on the RITM variable (ritm_supplies_workday_requisition_number), see if it matches the string listed on the purchase order table (u_workday_purchase_request)  and then update a field back on the RITM (ritm_supplies_workday_po_number, a reference field) to pull in the corresponding PO record that has the matching requisition number.

Thanks for your help so far!

[JenniferRah · Accepted solution]

That's a little simpler for your lookup. I think the code you are wanting should be this:

(function executeRule(current, previous /*null when async*/) {

	var lookup= new GlideRecord('proc_po');
	lookup.addQuery('u_workday_purchase_request', current.variables.ritm_supplies_workday_requisition_number);
	lookup.query();
	if(lookup.next()){
	current.variables.ritm_supplies_workday_po_number = lookup.getDisplayValue();
	}
})(current, previous);

That will set your ritm_suppolies_workday_po_number variable to a sys_id, so that variable should be a reference variable to the proc_po table. 

Incidentally, you need to be sure this is a BEFORE update business rule or your update won't get saved.

[Jayce Antrum]

Hi Jennifer,

Thank you so much! This got me 95% of the way there which was super helpful. Marking this as the solution.

If any one is wondering I had to change the last line of the business rule to 

current.variables.ritm_supplies_workday_po_number.setDisplayValue(lookup.u_workday_purchase_order);

get it to work properly. Jennifer's code worked correctly but I was running into an issue with the reference field displaying as blank even through the preview icon was there and showed the correct value. My slight code change above managed to fix that small issue.