Resolve Primary Key to value on a record

EB7 · ‎02-14-2019

I have two tables in ServiceNow that has company information and Contact information.

Company table:

Company Key	Company name	Company Address
1	Example Company 1	123 Sample Drive, City, ST 12345
2	Example Company 2	124 Sample Drive, City, ST 12345
3	Example Company 3	125 Sample Drive, City, ST 12345

Contact/User Table:

Contact key	Name	Contact Address	Company Key
11	John Smith	Xyz address	2
12	Adam Abel	Abc address	3
13	Rice Rich	Cde address	1
14	Nick Jay	Fgh address	2

Question is, how can I have Company name returned on a Contact/User form instead of Company key?

I have Oracle DB administration background and this is approached differently as I have direct access to the DB, but not the case in ServiceNow. Any suggestions will greatly be appreciated.

Thanks,

EB

EB7 · ‎02-22-2019

Thanks,

I'm still on the initial loading of the data into ServiceNow. At this point that field is empty and I want it to load automatically from the transform.

I have solved this issue by adding this script to the transform map:

answer = (function transformEntry(source) {

var companyID = "";

// Get company record using manufacturer seq number
if(source.u_company_key){
var mfr = new GlideRecord("u_manufacturer");
mfr.addQuery("u_company_key", source.u_company_key);
mfr.query();
if(mfr.next()){
companyID = mfr.getValue('u_company_name');
}
}

return companyID;

})(source);

View solution in original post

EB7 · ‎02-22-2019

I had to manually select it from the search box:

Then I get this:

I would like it to load automatically on the form without me having to select it.

dvp · ‎02-22-2019

As you have 4 manufacturers how does the script know which company to populate for Rice rich?

Is there a relationship or a logic behind what company to populate?

EB7 · ‎02-22-2019

I have 3 manufacturing companies.

Please see my initial post for the complete data.

It's my understanding that's all what a reference field should do. When I load data into the contact user table, it will read a company (number) that corresponds to a manufacturer.

Since this is a reference field to the manufacturer table, I'm expecting it to load the Manufacturer name as I specified by setting the display value to true.

My expectation is for this to be the default behavior.

dvp · ‎02-22-2019

Here is what reference field does

A reference field can refer only to records from one other table. For example, the Caller field on the Incident table is a reference to the User [sys_user] table. Source

In the example data provided above there is no relation between company and contact table except a key field.

When I load data into the contact user table, it will read a company (number) that corresponds to a manufacturer.

So are you saying that you will enter company key and based on that you would like to populate the Company reference field

Setting the display to true will make the respective field value to show on reference fields.

EB7 · ‎02-22-2019

Yes, when I loaded the User table, John Smith for instance. From the data I loaded, John Smith works for a Company called 'Example Company 2' referenced by the number 2 in the Contact User table.

My guess is I need some type of further configuration/logic for this to load on the form automatically.

What would be your recommendation to accomplish this?

I have set the display value to true for 'Company name' field the Manufacturer table.

Thanks,

EB