Issue creating dependent enumlist (multi-select dropdown) fields

[bdrogers333]

Hi There,

Really hoping I can get some help here. I have two Enumlist fields, Category and Theme. The intended functionality I’m trying to produce is when a particular category value is selected a dependent field is show and populated with the corresponding values.

Below are a couple of scenarios that should help explain the intended functionality:

Fields
Category (Enumlist / multi select, always visible)
Theme ((Enumlist / multi select, conditionally visible and displays values based on Categories selected)

Values
Category A (Show Themes) - Theme 1, Theme 2, Theme 3
Category B (Show Themes) - Theme 4, Theme 5, Theme 6
Category C (Do Not Show Themes - No mapped themes

Scenario 1
Category A is selected. The Themes field should be visible and display only those values mapped to category A.

Scenario 2
Categories A and B are selected. Themes 1 - 6 are displayed in the dependent field and available for selection.

Scenario 3
Category C is selected. The themes field is not displayed

Scenario 4
Categories B and C are selected. Themes 4, 5 and 6 are displayed and available to be selected.

For reference I have tried following this article: Dependent Dropdown | AppSheet Help Center

However, I’m not sure if that is only valid for single value down-downs ( not two dependent multi select dropdowns.

Thanks so much for your help.

[Rafael_ANEIC-PY]

Hello @bdrogers333, welcome to our community !

There a few ways this can be solved, using references, list operations, or just abominable expressions.

bdrogers333:

Below are a couple of scenarios that should help explain the intended functionality:

If you have a very limited number of scenarios, then an abominable expression can solve it pretty fast, considering that you have 3 categories right now you would have:

1. A
2. B
3. C
4. A+B

A+C=A , and B+C=B, and A+B+C=A+B, so since C changes nothing except when it is selected alone, we can ignore it most of the time.

If you have more scenarios and there’s the possibility for more to come, you’re gonna need an auxiliary table for storing those, your [Category] column will be a simple enumlist ref to that auxiliary table, your [Theme] column however, will require some expression magic.

Your auxiliary table will require at least an [ID],[Category] and [Themes] columns, where you will save each category and it’s respective themes, if you are a cultist of the current_user system like me (Current_User (Slice) - How to conform your app around WHO is using the app) you could also add a [user_ref] column for making certain categories or themes available only for certain users.

So, which option do you prefer? so we can get into the more specific specifics of this specific question.

[bdrogers333]

Thanks so much for the reply Rafael.

I actually have several different scenarios so I will need to go the route of creating the additional table.

[MultiTech]

I would advise the table version of things, creating two “supporting” tables.

What I would do is create two “auxiliary tables” as @Rafael_ANEIC-PY calls them above. (I refer to them as “supporting tables” a lot of times.)

• Categories
• Themes

---

Inside the Category table, you’ll only really need two columns:

• Category_ID (to store the GUID for that record); and
• Category_Name (the name of the category)

But you could also include any number of other columns to store additional info about each category.

• Category_Image
• Category_Description
• Etc.

Then inside your second supporting table, this one for Themes:

• Theme_ID
• Theme_Category (ref to the Category table)
• Theme_Name

Any others you may wish to include.

---

Then you’d populate these tables with records, the idea being you’re ‘encoding’ into your system the information it needs when working on things “downstream.”

• Because the Themes is ref-connected to the Category, you’ll get a “Reverse Reference” on the Categories table of all the associated Themes.

From here… you can easy use list-dereferences to populate the values you’re wanting inside your other forms.

Col Name	Type	Valid-If Formula
Selected_Categories	EnumList (base type Ref to Category)	Categories[Category_ID]
Selected_Themes	EnumList (base type Ref to Themes)	SPLIT(CONCATENATE([Selected_Categories][Related Themes]), " , ")

This will give you:

• A drop down of all the categories (or more specifically: a list of all the category IDs for each of the records in the Category table) that a user can pick from.
• A dependent dropdown that pulls all the associated [Related Themes] from each selected Category.

---

There’s obviously a lot more involved, but this what Rafael was alluding too - and what I would advise.

• Sometimes it’s better to “build a system” inside your app that allows you to ‘encode’ various bits it needs to function. (Things like a list of the Categories, their associated Themes, States, Cities, Tasks, etc…), as apposed to hard-coding them.
  • This allows the app to expand in functionality (think adding new tasks, new categories) based on the data entered by the user.

Otherwise when things need to be updated, you have to hard-code them again; and when they want to enter another category… again. And again, and again.
Or you give users the ability to enter this data themselves; leaving me out of it.

[bdrogers333]

Thank you so much for the reply. I’ve attempted using your approach with creating the two supporting tables. I’ve gotten to the point where I apply the Valid-If formula for the selected_themes but not understanding the logic here and unable to get it to work:

SPLIT(CONCATENATE([Selected_Categories][Related Themes]), " , ")

Is there any additional detail you can provide? Here is the error I am receiving:

Column Name ‘Themes’ in Schema ‘Case_Schema’ of Column Type ‘Ref’ has an invalid data validation constraint ‘=SPLIT(CONCATENATE([Categories][Theme_Name]), " , ")’. Unable to find column ‘Theme Name’.

I’ve tried deleting and re-importing the table but still receiving issues. Also, to make sure I understand, is [Related Themes] actually suppose to be Theme_Name in your example?

Thanks again for all your help!

[MultiTech]

bdrogers333:

Column Name ‘Themes’ in Schema ‘Case_Schema’ of Column Type ‘Ref’ has an invalid data validation constraint ‘=SPLIT(CONCATENATE([Categories][Theme_Name]), " , ")’. Unable to find column ‘Theme Name’.

It looks like you’re calling the [Theme_Name]…

• Is that column marked as the “Key” for that table?

You’re trying to set a ‘ref’ column, so you can’t put a name there - it needs to be the ID of the record (that has the name you want).