I’m trying to filter reference choices as described here. According to the sample workspace, I can do that by clicking on “Set dropdown condition” and entering a formula such as choice.Country == $Stadium_Country.
However, when entering choice.Country == "China" (or any other country), the dropdown menus from the “Stadium City” column are now displaying a “no choices matches condition” message. I wasunder the assumption that by using a specific string on my formula, I would have filtered the dropdown choices according to this fixed criteria. What I’m doing wrong here?
That’s because “choice.Country” in the sample is a reference to the Countries table. It stores the entire record on that table and not the string with the country’s name.
Recently I watched the May 16, 2024 Webinar - Reference and Choice Dropdown List Filtering that helped me in similar situations:
So, I understand trying the demo to see how it will work for your project.
While it seems intuitive that you see a value in Stadium Country and want to simply filter the dropdown by a constant, “China” the problem becomes how that data is stored in the source table. Because the Country column in the Cities table is, itself, a reference list, the value stored in Country for each city is a list, not a constant. Therefore, even though there may only be one value stored in the list, that list has to be iterated through.
You would need to do one of the following to filter the dropdown choice for Country == "China"
Change the Country column type in the Cities table to text or choice column type. You’d have to manually populate the choice list. Then your dropdown condition choice.Country == "China" would work.
Use a helper column to hold a list of values that the dropdown condition would match. In this case I created a formula column named A in the Stadiums table. The formula in A is:
records = Cities.lookupRecords()
records = [
r.City for r in records if r.Country.Country == "China"]
return records
and the dropdown condition in Stadium City is:
choice.City in $A
In column A you’ll see a list of all the cities in China, built using Python list comprehension. The dropdown condition compares the City column in table Cities to the list of Cities in column A and gives you that list in the dropdown box.
Method two may seem a bit round about, but it’s also kind of odd to use a reference column (dynamic values) and then try to use a constant value.
Thank you for your answers! I went for a different approach by splitting my choices into different tables, but your explanations helped me a lot to grasp the rationale behind these formulas. Again, thanks a lot!