Include field data from another table inside a Business Rule

[LRhodes]

Hi all - I'm struggling to find assistance on this and I think it's because I'm not wording the question correctly.

Little background we have a BR on the Incident table that triggers a REST Message to send. Most of the data we want to include in the payload is part of the Incident table and this goes through fine. However there's a field on the sys_user table we want to include in this payload.

Below is my script and you can see I've had a go at it but it's returning 'undefined' so I'm clearly doing something wrong.

I've tried a GlideRecord query on the sys_user table where the username on that table matches the caller_id on the Incident form. I then want to take the value of the Partner Code field on that record and include it in the payload to be sent.

(function executeRule(current, previous /*null when async*/) {
	
	function jsonEncode(str) {
			
		str = new JSON().encode(str);
		return str.substring(1, str.length - 1);

		}
	
	var practice = new GlideRecord('sys_user');
	practice.addQuery('username', current.caller_id);
	practice.query();
		if (practice.next()) {
			var pc = practice.u_partnership_number();
			}
	
 try { 
 var r = new sn_ws.RESTMessageV2('Salesforce Integration', 'Create Incident');
 r.setStringParameterNoEscape('sysid', current.sys_id);
 r.setStringParameterNoEscape('created', current.sys_created_on);
 r.setStringParameterNoEscape('sub_category', current.subcategory);
 r.setStringParameterNoEscape('state', current.state);
 r.setStringParameterNoEscape('updated', current.sys_updated_on);
 r.setStringParameterNoEscape('practice_code', pc);
 r.setStringParameterNoEscape('category', current.category);
 r.setStringParameterNoEscape('short_desc', current.short_description);
 r.setStringParameterNoEscape('desc', jsonEncode(current.description));
 r.setStringParameterNoEscape('caller_id', current.caller_id.getDisplayValue());
 r.setStringParameterNoEscape('priority', current.priority);
 r.setStringParameterNoEscape('contact_type', current.contact_type);
 r.setStringParameterNoEscape('business_service', current.business_service.getDisplayValue());
 r.setStringParameterNoEscape('number', current.number);
 r.setStringParameterNoEscape('on_behalf', current.u_on_behalf_of.getDisplayValue());
 r.setStringParameterNoEscape('confidential', current.u_confidential);
 r.setStringParameterNoEscape('thirdPartyReferenceId', current.u_3rd_party_reference_number);

//override authentication profile 
//authentication type ='basic'/ 'oauth2'
//r.setAuthenticationProfile(authentication type, profile name);

//set a MID server name if one wants to run the message on MID
//r.setMIDServer('MY_MID_SERVER');

//if the message is configured to communicate through ECC queue, either
//by setting a MID server or calling executeAsync, one needs to set skip_sensor
//to true. Otherwise, one may get an intermittent error that the response body is null
//r.setEccParameter('skip_sensor', true);

 var response = r.execute();
 var responseBody = response.getBody();
 var httpStatus = response.getStatusCode();

	var jsonObj = JSON.parse(responseBody);
	var targetID = jsonObj.recordId;
	var targetCID = jsonObj.caseNumber;
	current.u_3rd_party_reference_number = targetID;
	current.u_salesforce_ref = targetCID;
	current.update();
	
}
catch(ex) {
 var message = ex.message;
}

})(current, previous);

 Any and all help greatly appreciated!

[Johns Marokky · Accepted solution]

Hi @LRhodes ,

Since you are using it in business rule. you can easily get it done by using dot walking facility.

Replace the below code and it would work.

var practice = new GlideRecord('sys_user');
	practice.addQuery('username', current.caller_id);
	practice.query();
		if (practice.next()) {
			var pc = practice.u_partnership_number();
			}

Replace this code with the below code.

var pc = current.caller_id.u_partnership_number;

hoping that u_partnership_number is the field name that is in the sys_user table.

Mark helpful and accept the solution if it helps in solving your query.

Regards,

Johns

[priyasunku]

Hi @LRhodes 

In the below code you are using 'username' field to query but it is the current.caller_id will return sys_id of the record.

please query with sys_id and check once. if not you can use current.caller_id.û_partnership_number;

var practice = new GlideRecord('sys_user');
	practice.addQuery('username', current.caller_id);
	practice.query();
		if (practice.next()) {
			var pc = practice.u_partnership_number();
			}

If my answer solved your issue, please mark my answer as ✅Correct & 👍Helpful

[Ankur Bawiskar]

@LRhodes 

you can directly dot walk.

if you still want to query then use this -> you should query with sys_id field

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

function jsonEncode(str) {

str = new JSON().encode(str);
return str.substring(1, str.length - 1);

}

var pc;
var practice = new GlideRecord('sys_user');
practice.addQuery('sys_id', current.caller_id);
practice.query();
if (practice.next()) {
pc = practice.u_partnership_number;
}

try {
var r = new sn_ws.RESTMessageV2('Salesforce Integration', 'Create Incident');
r.setStringParameterNoEscape('sysid', current.sys_id);
r.setStringParameterNoEscape('created', current.sys_created_on);
r.setStringParameterNoEscape('sub_category', current.subcategory);
r.setStringParameterNoEscape('state', current.state);
r.setStringParameterNoEscape('updated', current.sys_updated_on);
r.setStringParameterNoEscape('practice_code', pc);
r.setStringParameterNoEscape('category', current.category);
r.setStringParameterNoEscape('short_desc', current.short_description);
r.setStringParameterNoEscape('desc', jsonEncode(current.description));
r.setStringParameterNoEscape('caller_id', current.caller_id.getDisplayValue());
r.setStringParameterNoEscape('priority', current.priority);
r.setStringParameterNoEscape('contact_type', current.contact_type);
r.setStringParameterNoEscape('business_service', current.business_service.getDisplayValue());
r.setStringParameterNoEscape('number', current.number);
r.setStringParameterNoEscape('on_behalf', current.u_on_behalf_of.getDisplayValue());
r.setStringParameterNoEscape('confidential', current.u_confidential);
r.setStringParameterNoEscape('thirdPartyReferenceId', current.u_3rd_party_reference_number);

//override authentication profile
//authentication type ='basic'/ 'oauth2'
//r.setAuthenticationProfile(authentication type, profile name);

//set a MID server name if one wants to run the message on MID
//r.setMIDServer('MY_MID_SERVER');

//if the message is configured to communicate through ECC queue, either
//by setting a MID server or calling executeAsync, one needs to set skip_sensor
//to true. Otherwise, one may get an intermittent error that the response body is null
//r.setEccParameter('skip_sensor', true);

var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();

var jsonObj = JSON.parse(responseBody);
var targetID = jsonObj.recordId;
var targetCID = jsonObj.caseNumber;
current.u_3rd_party_reference_number = targetID;
current.u_salesforce_ref = targetCID;
current.update();

}
catch(ex) {
var message = ex.message;
}

})(current, previous);