topic Setting up Foreign Keys (ServiceNow) in App Development

Setting up Foreign Keys (ServiceNow)

erieqlik — Wed, 15 Jan 2025 19:57:30 GMT

I have two tables from a call to ServiceNow. Incident and User.

User is easy. It has user_name and a sys_id.

Incident has a number of references to user. If has an opened_by_sys_id, a closed_by_sys_id, and an assigned_to_sys_id. What is the relationship between these? I cannot set up a simple additional column because it would cause synthetics. How do I load the incident table so that it will look up the three columns in the user table?

Re: Setting up Foreign Keys (ServiceNow)

morgankejerhag — Wed, 12 Feb 2025 05:44:39 GMT

Easiest way is to read the user several times and create different keys (or just join in the value to Incident):

OpenByUser:
Load
sys_id as opened_by_sys_id,
user_name as [Opened by user]
resident User;

ClosedByUser:
Load
sys_id as closed_by_sys_id,
user_name as [Closed by user]
resident User;

drop table User;

Re: Setting up Foreign Keys (ServiceNow)

Qrishna — Wed, 12 Feb 2025 14:45:42 GMT

User:
Load sys_id as %open_sys_id,
sys_id as %close_sys_id,
sys_id as %assigned_sys_id,
sys_id,
user_name
From [..User_Table];

Incident:
Load opened_by_sys_id,
closed_by_sys_id,
assigned_to_sys_id,
*
From Incident ;

Fact:
Load opened_by_sys_id as %open_sys_id,
'open' as Activity_Flag,
Other_Needed_Fields
Resident Incident;
Load closed_by_sys_id as %close_sys_id,
'close' as Activity_Flag,
Other_Needed_Fields
Resident Incident;
Load assigned_to_sys_id as %assigned_sys_id,
'assign' as Activity_Flag,
Other_Needed_Fields
Resident Incident;

drop table Incident;

this shoud, fix you issue, create a list box with activity flag and you can filer on open,close and assign and also avoid loops by splitting and concatenation methdo. Thnx