Filtering a dropdown based on another dropdown

[Jamie_Laing]

Hi there, 

I'm trying to create a form that includes two dropdown items that are in a parent/child relationship.  I want the user selection in the first drop down (parent) to create a filter in the second drop down (children).  My parent is "Category" and my child is "Product".  Each Category has many Products.  I'm trying to make it easy on the user so they can select the Category on the form, and the second drop down automatically filters to include only the Products that belong to the selected Category.  Currently both drop downs are populated with the "Ref" type but they do not interact.

Perhaps I'm thinking about this upside-down somehow.  Any suggestions?

[SkrOYC · Accepted solution]

I imagine your form is to add a row to another table, a third one.

If this third one has CategoryId and ProductId columns, you could create your dropdown this way:

CategoryId Valid_If:

Category[Key]

ProductId Valid_If:

[CategoryId].[Related Products]

 This should be the most eficient way of doing it.

Since your products are children of your Category, you should have a [Related Products] column with all the key of the Products records.

Check if it applies and come back if you need more help

[SkrOYC]

Valid_if

https://help.appsheet.com/en/articles/961211-valid_if-column-constraint

https://help.appsheet.com/en/articles/961554-dependent-dropdown

[Jamie_Laing]

Thanks, I saw that article, but that only flags items as invalid if they are not children of the selected parent.  I'm looking for the Products dropdown to be filtered, not flagged as invalid, which would be quite cruel to my users.

Hi again, it looks like you edited your response to include the dependent dropdown article.  I saw that days ago and that doesn't work. 

[SkrOYC]

It works if your expression pulls a list of values

Please post your tables schema

[Jamie_Laing]

The first link is not useful.  The dependent dropdown link is close, but the problem is that Lead Tracking app example stores "Lead Country" as a text value.  I am storing the Key.  I need a solution that shows the Name but after the user selects the item in the child dropdown, the Key is stored.  So a text value doesn't work.

[SkrOYC]

I have made dozens of dependant dropdown/buttons but I can't help if you don't post your schema

[Jamie_Laing]

Thanks Skroyc, how do I post my schema?

[SkrOYC]

Basically I mean screenshots of your tables and their column types

[Jamie_Laing]

I see.  Thanks.  I've got normalized data with each row having a key value that is a GUID.  

Parent table: Category

Columns: Key, Name

Child table: Products

Columns: Key, Category, Name (Category includes key of parent Category from first table)

Data collection form includes various entries but two dropdowns, both currently populated by REF.  I want the selection of the first (parent) dropdown to filter the values in the second (child) dropdown, and in the end I want the selected Key for the Product (child) to be stored.  If that doesn't make sense I can post pictures.

[SkrOYC · Accepted solution]

I imagine your form is to add a row to another table, a third one.

If this third one has CategoryId and ProductId columns, you could create your dropdown this way:

CategoryId Valid_If:

Category[Key]

ProductId Valid_If:

[CategoryId].[Related Products]

 This should be the most eficient way of doing it.

Since your products are children of your Category, you should have a [Related Products] column with all the key of the Products records.

Check if it applies and come back if you need more help

[Jamie_Laing · Accepted solution]

Aha!  So, my schema is a little more complicated than I described and I suspect that caused the [Related Products] virtual column to NOT get created.  I added it manually, and now it worked.  For the next person, here is what you need to do with minor corrections to Skyroc's syntax...

1 - Set the first dropdown as type REF.  No other validity requirements on that in my particular case, as any parent selection is valid.

2 - Set the child column as type Ref (Enum will also work) and for "Valid If" enter "[Category].[Related Products]" ... and yes this solution does create a filtered list. 

Thank you Skroyc!

[SkrOYC]

It can be Ref indeed, actually the first one can be Enum basetype Ref also. It shouldn't be a problem

[Jamie_Laing]

I double-checked and you are correct, I will update my explanation to myself, thank you.

[thelimp]

Well, I have a bit the same problem but I can't have my dropdown list to be filtered as I want.

Here is the problem: I fill in a form of "promotions" with a place, a client, a user. Users are trained for some clients, meaning they can conduct a promotion for the clients they've been trained. So when I select a customer in the form, i want that the dropdown list of users only contains the users that are trained for this customer.

Here is the Data Structure: 

Table "Promotions"
Columns: id, store_id(ref to Stores table), client_id (ref to Clients table), user_id(ref to Users table)

Table "Clients"
Columns: id, name

Table "Users"
Columns: id, Email, Name, Last Name

Table "Trainings"
Columns: id, User_id(ref to Users table), Client_id(ref to Clients table)

i couldn't manage to understand which expression to put in the Valid_if field to filter the users that have a training for the selected client. 
I tried [clients_id].[related trainings] but it does another thing --> i can select only the id from the Training table of the entries matching the client_id..

Training table: where client_id:1 is the client_id from the client i selected in the form.

If anyone can help, i'd be glad. Maybe my Data structure can be improved as well? let me know!

Many thanks in advance and let me know if you need more information.

[thelimp]

I litterally found a solution 5mn after posting this message.

If it can help someone, i Put in the Valid_if field this formula:

SELECT(Trainings[User_id],[_THISROW].[client_id]=[Client_id])

It selecrt in the Trainings dataset, the column User_id, where the Client_id matched the client_id selected in the form.