How to dot walk from user table to core country table

Souvick A
Tera Contributor

‎03-17-2022 04:57 AM

Hello All

I have a requirement as below:

Navigate from user table to core country table for a field value.

sys_user > cmn_location > core_country.

The user table contains a OOB field 'Location'  from there I will direct to a customized field 'Country' on cmn_location table and then I need to fetch the data on ISO Short field value on core_country table.

Note: Al these are reference fields. 

I want guidance on formulating this in a script include to compare the value of ISO Short field with incoming data.

Regards

Souvick

Labels:
0 Helpfuls
  • 974 Views
5 REPLIES 5

Oleg
Mega Sage

‎03-17-2022 07:40 AM

To be exactly, country field of cmn_location table has type String(40) and it's not a reference to core_country table. Moreover, the text in the column, even in standard ServiceNow instance, contain texts, which not corresponds the value of name field in cmn_location table. You can easy find locations with countries Brasil (instead of Brazil in cmn_location table), UK (instead of United Kingdom in cmn_location table, which has GB and not UK as ISO Short) and USA (instead of United States of America in cmn_location table).

So I can suggest you either to add (and fill) new column to core_country table with reference to cmn_location table or to clean-up the country names of locations and to get ISO Short like

var grCountry = new GlideRecord("core_company");
if (grCountry.get("name", String(grUser.location.country))) {
    // String(grCountry.iso3166_2) - ISO Short
    gs.debug(grCountry.iso3166_2);
}

If your cmn_location table already extended with, for example, u_country field, which is reference to core_country table, then you can use grUser.location.u_country.iso3166_2 directly.

0 Helpfuls