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
If you just want to have a list of all table columns, that ARE Reference Fields, navigate to
> System Definition > Dictionary
and filter the list like shown in the screenshot below:
This will show you all existing database columns which refer to the given table respectively.
There may also be more references defined with table "Relationships", which you can find by navigating to
> System Definition > Relationsships
(see screenshot below):
The details (1) show you, that you can join two tables ("Applies to table" and "Queries from table"):
The "Query with" (2) script defines how the tables are joined.
Let me know if that answers your question and mark my answer as correct and helpful.
BR
Dirk
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:
