How to create a relationship on a specific field

[Wim2]

I am looking for a way to create a relationship between two tables on a specific field. Most Youtube instructions and ServiceNow instructions arequit streit forward.
Give a name
Source table
Query table
Thats it, but how do I define the relation field.

Example.
We store entries from the system log in an audit table so we can save them for a longer time. The table is named "u_admin_auditlog".

So, if someone is impersonated, that impersonated user is stored in the field "target account"
If an *admin user is loggin into the system, the *admin user is stored in the field "target account".

Now if we open a record, I want to add a related list where we can see the performed actions (URI).

So, the relation should be: "u_admin_auditlog, target account" to "sys_audit, user".

Resumé, if I open a record in the u_admin_auditlog than in the related list all record of the user in that record should be shown from the table sys_audit.

Now I created a Relation:
System Definitions > Relationships

[New]
Name = AuditlogToSys_audit
Applies to table = u_admin_auditlog
Queries from = sys_audit

But how do I set the relation to the specific fields?
The relation should be: "u_admin_auditlog, target account" to "sys_audit, user".


I think this have to be done in the relation under [Advanced] but I need some assistance in how to do that.


Also, in a later fase of our project, the records shown in the related list shoud be from the date "u_admin_auditlog, created", so we only see actions performed by the user from a certain date.

Greets
Wim

[Community Alums · Accepted solution]

Hi @Wim ,

A Parent-Child relationship can be created by adding a reference field, that references the parent table on the child table. Same like OOB Change and Change Task relationship.

It can be created by adding a reference field on the child table and then you will find that relationship in the related list (right click->configure-> related list) of that parent table, from where you can add it to the form.

If there is a requirement for a child to have more than one parent, then you have to create a many-to-many relationship.

Go to System Definition>> Relationship

check the link below :

Attachments Related Lists — ServiceNow Elite

Mark my answer correct & Helpful, if Applicable.

Thanks,

Sandeep

[Wim2]

Sandeep,

We used your code and came up witj this.

(function refineQuery(current, parent) {

      // Add your code here, such as current.addQuery(field, value);

    var myuser = parent.u_target_account;

    var startDate = parent.u_audit_created;

    current.addQuery('user', myuser);

    current.addQuery('sys_created_on','>=', startDate);

})(current, parent);

We now see all entries in the sys_audit tabel of the selected uset as of the time the impersonation or login started.

We are now working on getting the impersonation.end (date/time) in the same record, so we can show the records of the sys_audit table in the timeframe

impersonation.start

impersonation.end

Greets

Wim