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

[Ahlgreens]

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]

Hi there,

Have you tried applying an advanced reference qualifier on "Site list"? I don't know your exact variable names, so here's an example of what you could do.

I also noticed your using "ref_qual_elements = core_country.iso3166_2;". Within this code, I'm missing the variables part? (you are talking about a record producer right?)

Have a look at especially the javascript and variables part.

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

Kind regards,
Mark

[Ahlgreens]

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

[Mark Roethof]

Ah I see, I will try to reproduce this.

Thoug could be tricky. Out-of-the-box, Country is not referenced on the Locations. It is string field on locations unfortunately, so typos in this field will hurt 😞

Kind regards,
Mark

[Mark Roethof · Accepted solution]

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