Hi All,
Please guide me on how to find joins fields for tables.
eg: schema map for Table :User [sys_user] and Table : User Role [sys_user_has_role]
Join : User Role > user = User > sys_Id .
In the diagram below table: User Role shows "user " how to know which field from user can be join.(in this example I know we need to join on User >sys_id but for any other table how to determine the join fields)
Hi,
To identify with field to use at join for other tables are still same like how you did for user table.
You need to check with field is the REFERENCE and all the references are using SYS_ID as a unique key to join with other table.
For example: sc_task & sc_req_item.
Since Request Item in sc_task is a reference to sc_req_item. So the relationship will be Request Item => sc_req_item.sys_id
Sample Database view:
Hi
The references made automatically in ServiceNow are caused by database fields/columns of type "Reference". They are marked as "red" in your picture above. For example, the "reference to User" will join to the "User" [sys_user] table.
A Reference field ALWAYS stores the SysID of the "foreign" record in the joined / referenced table.
If you are on the "Role" [sys_user_has_role] table, you can "walk" to the fields on the referenced table with
[sys_user_has_role.user.company] for example.
That means, the "user" field on the [sys_user_has_role] table links to the TABLE [sys_user], which has the field "company".
If you just use [sys_user_has_role.user], the CONTENT of the "user" field will be a SysID, but the "DisplayValue" will be the content of the column defined as "Display" field in the [sys_user] table (which is the "name" field OOB).
Please have also a look at this article from
If you want to set some specific field to display, you just need to mark the "Display" Checkbox on the given Dictionary record of the table column. But be careful! This change is GLOBALLY, and will change the displayed value in EVER List and EVERY Form. This is a very powerful change!
The screenshot shows the "name" column Dictionary of the [sys_user] table.
You can see, that the "Display" flag is NOT set, but as the system uses a field with the name "name" by default as one of the "Fallbacks", it will still work.
Also, you can see, that you can script what will be shown if you define a field as "Calculated".
Let me know if that answers your question and mark my answer as correct and helpful.
BR
Dirk
