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 · Accepted solution]

Sorry, Gergely...

You are correct.   The only true place the User -> Company relationship exists is on the User table.   That related list at the bottom of a company record is just a query of the user table where company = parent record (in this case, the company record you are looking at).   That is how reference fields work.

[szollo]

Hi Mike,

Thank you for getting back to me. It's all clear now!

[szollo]

Sorry for not getting back to you earlier. Thank you for your response!

It is not completely clear for me yet, but if you could answer the following questions, that could help:

- How should I imagine then the relationship between the 2 tables? As you could see I thought that from a User record I can retrieve the belonging Company data and from that Company record I could retrieve all the Users that Company field matches with the name of the company. So basically I could list all the relevant users on a Company record and not just select 1 user.

- Am I right that if I want to list all the Users which Company field matches with the current Company table record, then the way to do it is to query all users in the User table and filter them based on the Company field value(so there is no other way to reach those users through a Company record)?

If you could clarify this/explain it in more details, that'd be greatly appreciated!