How to reference related tables via primary and foreign keys?

[Victor Sirianni]

Hello,

We are working on a variable set for ITSM catalog items and have a custom table from which users select a location using a reference variable. Based on that selection, we populate a few other string variables on the form using a client script, which has been working well.

var location = g_form.getReference('location_id', getLocation);

function getLocation(location) {
  g_form.setValue('other_variable', location.u_other_info);
  ...
  ...
}

The next requirement is that we pull back additional related information based on the location, but from a different custom table. The location_id is a primary key on the location table and a foreign key on the location_use table. There can be a one to many relationship between them.

u_location_id	u_other_info
1	abc
2	def

u_location_use	u_location_id	u_use_info	contact
12	1	ship	person_a
13	1	bill	person_b
14	2	ship	person_c

In the example above, when a user selects a location we want to populate variables called billing_contact and shipping_contact with the corresponding values. So if a user selects Location 1, Billing Contact would be person_b, and Shipping Contact would be person_a. If the user selects Location 2, Billing Contact would be null and Shipping Contact would be person_c

Is this even possible, and if so, what would be the best way to do it?

Thanks!

[Victor Sirianni · Accepted solution]

For anyone else that comes across this, our 'probably not right but works for right now' solution was to transform the data prior to importing to consolidate the tables into one. Not the most elegant solution, but functional enough.

The other piece I believe we were missing was the reference fields storing the sys_id. By defining the reference keys I'm hoping we should able to use the output of one as a reference qualifier input to the next reference variable and table.

https://docs.servicenow.com/bundle/newyork-platform-administration/page/administer/field-administrat...  

1. Account reference to u_customer_accounts table stores u_account_number instead of sys_id
2. Location reference to u_customer_locations table allows users to select related locations by filtering with a reference qualifier where account foreign key equals Account variable from step 1
3. Program reference to u_customer_programs table allows users to select related programs by filtering with a reference qualifier where location foreign key equals Location variable from step 2

Then it's hopefully a simple matter of using client scripts to populate any related variables needed using the respective reference variable.