Table relationship and reference field type

[szollo]

Hello,

I'm new to ServiceNow, so please handle my question accordingly

Just trying to understand how the table relationship works in ServiceNow along with the reference field types.

If I understand well, then the Tables are connected together using reference fields. For example the Company table(core_company) references the User table(sys_user) through the Contact field(core_company.contact), while the User table uses the sys_user.company reference field to make the connection with the Company table.

Do I understand correctly that these reference fields are connecting the tables together?

I'm trying to write a script that first retrieves back a user record in the sys_user table that contains which Company does it belong to(this works fine) and then list all users that belongs to the Company. I thought if I do the following I'll get back some kind of reference to the users with the Contact field.

var company = new GlideRecord('core_company');

company.get('name','Adagio');

gs.print(company.contact); // this gives me back nothing in a vanilla dev instance

I've modified 2 users record to add "Adagio" to the Company field, but my theory about how the tables are connected appears to be wrong: I expected some kind of reference in the Adagio company record's Contact field to the User table records... but when I check the Adagio company record I can see it's empty.

I have the feeling I completely confused myself, so if you could help me clarifying how tables are connected with each other and how the reference fields works/their purpose, that'd be greatly appreciated! (I know I can query all the users and filter for user.company.name='Adagio', but not sure that's the right way).

Thank you in advance,

Gergo

[Mike Allen]

You are correct.   These are 1:many relationships back to the other table.

Your script looks fine.   This is what I have:

var company = new GlideRecord('core_company');

company.get('name','Adagio');

gs.print(company.contact);

*** Script: a2826bf03710200044e0bfc8bcbe5ded

This says to me that on core_company, the contact field was added, and I assigned Alejandro Mascall as the Contact.   I looked at the XML of the company and saw the contact was indeed Alejandro, and that his sys_id was a2826bf03710200044e0bfc8bcbe5ded.   When I put your script into background scripts, I got exactly that answer.   The sys_id is the primary key of any table, so that is what shows in all reference fields in the XML.

[szollo]

Hi Mike,

I think my confusion comes from the following:

I've assigned "Jewel Agresta" as a contact in the Company record, however as you can see in the bottom of the screenshot there are 2 records from the User table that I've created and filled the "Company" field with the value "Adagio".

Here are the records where Company='Adagio':

If I check "Jewel Agresta" User record, then I can see the Company field is not Adagio:

If I execute the following, then I'll get back "Jewel Agresta", as that is what the core_company relevant record holds:

var company = new GlideRecord('core_company');

company.get('name','Adagio');

gs.print(company.contact);

This causes me confusion as I expected "Jewel Agresta" record's Company field(in the User table) to be "Adagio" appear and also the Company table's Contact field as some kind of list where all the related users appear(so in this case 3 users).

I might simply overlook/confuse something, but this doesn't add up. That's why I think I might be wrong about how I see table relationship.

[Mike Allen]

There is no reverse relationship between Company's contact and the user record.   Generally, you would put a reference qualifier on the contact field that only allows you to choose users from that company, saying something like company = current.sys_id, or whatever.

[szollo]

Mike/Anyone, can you please confirm what I wrote earlier is correct or clarify it? Your help would be appreciated!

Thank you in advance,

Gergo