Setting up Foreign Keys (ServiceNow)

[erieqlik]

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? 

[morgankejerhag]

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;

[Qrishna]

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