Business Rule to retrieve value from another table.

[Aki17]

I need a Business Rule to retrieve value from another table. The 2 tables are:

[syslog_transaction] table
- user_name: UserA
- remote_ip: 10.1.2.3

and
[sysevent] table
- sys_created_by: UserA
- u_ip_address_from_transaction_log: (should be auto-populated using BR by referencing "remote_ip" in  [syslog_transaction] table)

After a record is created in [sysevent] table, the custom field "u_ip_address_from_transaction_log" should be auto-populated by referencing "remote_ip" field in [syslog_transaction] table which has same user name. (Please see the image below)

For this requirements, I wrote the following BR, but it didn't work and the custom field was not populated. Please kindly check where the problem is.

*after insert/update BR in [sysevent] table:

(function executeRule(current, previous /*null when async*/) {
var gr=new GlideRecord("syslog_transaction");
 gr.addQuery("sys_created_by",current.user_name);
 gr.query();
if(gr.next()){
	current.u_ip_address_from_transaction_log = gr.remote_ip;
	gr.update();
}
})(current, previous);

*Filter Conditions is "[Name] is [attachment.read]"

Best Regards,
Aki

[asifnoor]

okay. It looks like during insert the username is not populated, but might be populated after update. 

To confirm, manually update the sysevent and check once. If working, then change he Br to before update and check once.

Also, remove gr.update() line. you don't need it.

Kindly mark the comment as a correct answer and helpful if it helps to solve your problem.

Regards,
Asif
2020 ServiceNow Community MVP

[Aki17]

Hi asifnoor,

With the BR which CB suggested for me below, when I "manually" created a record in [sysevent] table, the "u_ip_address_from_transaction_log" field got populated automatically.

(after insert/update)

(function executeRule(current, previous /*null when async*/) {
gs.log('@inside BR '+ current.getValue('user_name'));
var gr=new GlideRecord("syslog_transaction");
 gr.addQuery("sys_created_by",current.getValue('user_name'));
 gr.query();
if(gr.next()){
gs.log('@inside loop '+gr.remote_ip);
	current.setValue('u_ip_address_from_transaction_log',gr.remote_ip);
	current.update();
}
})(current, previous);

However, when the Event record gets inserted by API by downloading an attachment in KB article, that field was not populated...

Could you give me a possible solution for this, please?

*The result didn't change even after changing the BR to "before update"...

[asifnoor]

Hi,

Check if the username is being updated after insertion. Have both insert and update BR and check the logs and see if you get the username anytime.

[Aki17]

H asifnoor,

I checked the log by running BR provided by CB above.

The Log result is below:

	Before Insert/Update	After Insert/Update
Manual	@inside BR 20200615	@inside BR 20200615
Auto (API)	@inside BR null	@inside BR null

Regardless of Before/After, when creating Event record manually, the logs have username, and also IP address was retrieved successfully.

However, when creating Event via API, the logs don't have username, and IP address was not retrieved. But looking at the Event record, I can see 'user_name' field with proper value.

Could you please investigate it and how to clear this issue?

[Chander Bhusha1]

Hi Aki,

In your api call have you used any setWorkflow(false) methods.

If yes then you can comment that line.

Can you please provide as are you usign the OOB table api for event or you have created a new table api . If yes can you share the details of the api .

Thanks,

CB