How to reference related tables via primary and foreign keys?

Victor Sirianni · ‎04-27-2020

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 · ‎05-03-2020

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...

Account reference to u_customer_accounts table stores u_account_number instead of sys_id
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
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.

View solution in original post

Pradeep Sharma · ‎04-27-2020

The best way is to handle all the logic at the Server side i.e create a script include and call it at client side via CS. Reference:

https://community.servicenow.com/community?id=community_blog&sys_id=f8ccee25dbd0dbc01dcaf3231f961978

- Pradeep Sharma

Victor Sirianni · ‎05-03-2020

Thank you Pradeep; this is a little more involved then we have time for at the moment, but definitely seems to be the 'best' solution.

Priyanka Chandr · ‎04-28-2020

Hi,

Go through the below link that will help you to solve your issue

https://community.servicenow.com/community?id=community_question&sys_id=88125321db101fc01dcaf3231f96...

Kindly mark it correct and helpful if it is applicable.

Thanks,

Priyanka

Victor Sirianni · ‎05-03-2020

Thanks, Priyanka; we gave this a try but the load and transform performance was terrible, and we never were able to get the lookups to work quite right. Probably user-error on our part, so we'll have to look into it again in the future.