Reference two lists with variable attribute and reference qualifier (Record Producer)

Ahlgreens · ‎04-01-2019

Hello there. This is my first time using Reference Qualifier and Variable Attributes

In the process of creating a Record Producer, I've created two variables to determine Country and Site where I use two different location lists. What I want to accomplish is when Country is chosen, for an example USA, the Site Lookup Select Box will update and only show the sites belonging to that country.

FYI, the lists name below:
Country list: core_country.list
Site list: cmn_location.list

I went to check core_country and get the ID for the ISO Short (core_country.iso3166_2) which is the column for shortcode of the country (US, UK, DK, etc). I went to cmn_location and found the equivalent (cmn_location.country), i.e the shortcode (column Country) for Country. My idea was to find a "shared column" in both lists.

To sum up, core_country.iso3166_2 only have one entry pr. shortcode. Cmn_location.country has any many entries as there're sites at the specified location.

With that, I made the below Reference Qualifier. Which did not work at all
ref_qual_elements = core_country.iso3166_2;

Do any of you have any idea what I could try?

Best regards,

Stefan

Mark Roethof · ‎04-01-2019

Hi there,

Because the dotwalking on the variable won't be possible, you would have to use a Script Include. Not that hard to setup, just tested the below scripting, works instantly.

Though, as I already mentioned, because the Country field on Location is a String, this could be very tricky. Already with testing out of the box, I'm encountering stupid differences. For example, United States of America being USA (matches country ISO Long, Australia being... Australia instead of AUS (doesn't match country ISO Long).

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark

Kind regards,

Mark Roethof

Independent ServiceNow Consultant

10x ServiceNow MVP

---

~444 Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field

View solution in original post

Ahlgreens · ‎04-01-2019

Hi Mark!

Thank you so much, it's working like a charm.

You're a true hero!

I appreciate a whole lot.

Have a wonderful day 🙂

Ahlgreens · ‎04-01-2019

Hello again, Mark.

I have one final question I really hope you mind answer.

Some of the fields in the Country list doesn't have any Sites connected to it. How can I remove these redundant entries (Countries) in the Country List?

Ahlgreens · ‎04-01-2019

Hello again, Mark.

I have one final question I really hope you mind answer.

Some of the fields in the Country list doesn't have any Sites connected to it. How can I remove these redundant entries (Countries) in the Country List?

Ahlgreens · ‎04-01-2019

Hi Mark.

An advanced reference qualifier is exactly what I am trying - I am not able to come up with a solution in regards to that picture. It is a Record Producer indeed, core_country.iso3166_2 is the Country Shortcode of the core_country list, so yeah, current.variables.core_country.iso3166_2 I assume it would be.

Maybe with below info you'll have a better shot?

Country list info:

- Type: Lookup Select Box

- Lookup from table: Country [core_country]

- Lookup Value field: Name (OBS! This is the full name of the country, not the ISO Short which is the country code)

- No reference qualifier

Site list info:

- Type: Reference

- Reference: Location [cmn_location]

- Use reference qualifier: Advanced