Dropdown Conditions: Multiple values for both the Choices and the returned values

Ask for Help
1

I have two tables that are essentially the same, however, the first table, Frequency Configuration, is a repository of amateur radio frequencies, and the second table is to select some of those frequencies to use when programming radios and add additional information specific to the radio(s) being programmed.

Table: Frequency Configuration

Columns:

  • Frequency Type (ID = freq_config_freq_type): Choice list
  • Lookup: Formula to combine several other columns; used in other table to select records from the Frequency Configuration table.

Table: Build CHIRP Profile

Columns:

  • Choose Frequency Type: Choice list; same values as Frequency Type column in first table
  • Lookup: Reference column to Lookup column in first table.

Ideally, in the second table, I would like to be able select multiple Frequency Types in the Choose Frequency Type column which would be used in the dropdown conditions for the Lookup column to filter the dropdown choices from the first table.

I can do a one to one match like:

choice.freq_config_freq_type == $Choose_Frequency_Type

but I cannot figure out the syntax for filtering the dropdown using multiple values, and iterating for multiple values in the source table.

I know it’s not a database best practice to store multiple values in a single field, but it seems like Grist and Python would support what I’m trying to do, I just can’t figure out the appropriate syntax.

Thanks in advance.

2

Just very quickly, maybe you’re looking for the in operator:

choice.freq_config_freq_type in $Choose_Frequency_Type

?

3

I have tried:

choice.freq_config_freq_type in $Choose_Frequency_Type

I’ve also tried several different iterations of for in loops.

4

As I kept digging into this, I found this does work for using a Choice List in the second table to filter the dropdown values from the first table.

choice.freq_config_freq_type in $Choose_Frequency_Type

I figured out that even though Reference or Reference List columns look like Choice (List) columns, the dropdown conditions won’t work with them, at least not using choice.x. I had changed the Frequency Type columns in both tables to a Reference column pointing to a third table so I could keep the types synched between the two tables. Once I reverted those back to Choice columns, the in operator worked.

That still only answers half of my question. I’m still trying to see if it’s possible to iterate through several selected choices in a source Choice List column, not just a single value from the source Choice column.

I’m also trying to work out how I can account for not selecting any filtering values in the Choose Frequency Type choice list. I’ve tried:

if $Choose_Frequency_Type:
  choice.freq_config_freq_type in $Choose_Frequency_Type
else:
  choice.freq_config_freq_type != None

each individual statement works as intended, but the dropdown condition box does not seem to like taking an actual execution block.

5

I’ve worked out a couple things in relation to my original question, for anyone coming across this thread with a similar question.

  1. The Set dropdown condition box only accepts a limited number of functions.
  2. choice. is a specific domain that is required for the dropdown condition to work. This domain does not apply to a Choice List, Reference, or Reference List column types, therefore the dropdown condition won’t work with these column types, in the source table.
  3. The dropdown condition will accept a Python list of values to match choice.[source column]

I came up with solutions for:

  1. In the second table: Select multiple values, from a reference list, as filter values
  2. Allow Dropdown to show choices if no filter values have been selected

I have not worked out how to filter on a Choice List, Reference, or Reference List column types in the source table.

Solutions

  • Note - I added a second column Choose County as an additional filter option for the Lookup dropdown.
  • For the dropdown condition in my second table Lookup dropdown box I have:
choice.freq_config_freq_type in $Frequency_Choices and choice.freq_config_county_choice in $County_Choices
  • In order to use the Reference List column type as a filter and to account for not selecting any values, I created a helper Formula column for each filter column, ie Frequency ChoicesandCounty Choices`

For the Frequency Choices column, the formula I used was:

IF($Choose_Frequency_Type,
 $Choose_Frequency_Type.configuration_frequency_type,
 Configuration_Frequency_Type.lookupRecords().configuration_frequency_type
)

And a similar formula for the County Choices column:

IF($Choose_County,
 $Choose_County.configuration_county,
 Configuration_County.lookupRecords().configuration_county
)

If some values were selected ($Choose_Frequency is a truthy because it has a value) then these columns will have a list of values like ['Selected Frequency Type']. If nothing has been selected, (($Choose_Frequency is a falsey because it has no value) then the entire list of values from the reference table is inserted into the helper columns.

This maybe a bit of a brute force method, but it’s working for me for the time being. I’m learning Python on the fly, in addition to trying out Grist.

I’m hoping the guru, @natalie-grist, can weigh in.

6

Hey Phil,

I finally got some time on my hands and looked into this some more. It seems choice is indeed a bit of an odd fellow. It is more or less a Record object (from the table that the reference column points to), but apparently it can’t be compared to normal, “full” Record objects. So this won’t work: choice in $some_list_of_records (where the latter is a reference list column). What does work, however, is choice.id in $some_list_of_records. So with that, here’s what I came up with: showcase - Grist

The column valid_configs determines what the dropdown for Lookup shows. Check out the formula in valid_configs to see what’s going on. This uses a Python feature called list comprehensions to keep things concise. Since you’re learning the language, it might be best to take a look for yourself and see if you can grasp what it does. Let us know if you need a hand. Good luck! :smiley:

7

Thanks Tom!

I’ve uploaded a copy of my project to the Grist server and made it public if you want to see the actual project. Your solution is similar to the one I ultimately came up with.

The dropdown in question is on the CHIRP Profile page and I unhid the helper columns Frequency Choices and County Choices. These columns serve a similar function as your valid_configs column.

I spent a bunch of time yesterday learning list comprehensions and nested list comprehensions for a budget project I’m also working on in Grist. I’ve got a basic+ grasp of what they do…enough to do what I was trying to do…I think.

I don’t know if you would want this as a feature suggestion, but it would be ideal, in my mind, if the dropdown could filter the list of values as you type. I hesitate to say “like a data validation dropdown list in Google Sheets” but like that. In my mind, Grist, is way better than Google Sheets.

It would also be nice if the dropdown conditions could take more direct Python code, like a formula column, but I think I understand why it can’t, from digging around in the source code. It looks like the function handling the dropdown condition is expecting a Python list. That would explain why I couldn’t use list comprehension to iterate through a reference list in the main table (I think).

Thanks again!