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.
Hello @bdrogers333, welcome to our community !
There a few ways this can be solved, using references, list operations, or just abominable expressions.
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:
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.
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.
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.)
But you could also include any number of other columns to store additional info about each category.
Any others you may wish to include.
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:
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.
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!
User | Count |
---|---|
26 | |
25 | |
24 | |
23 | |
20 |