Cascading Dropdown

I am trying to build a cascading dropdown, that is, the values that display in a dependent or secondary cell are predicated by the value selected in an independent or primary cell.

For example, let say I have the following two fields:
Primary field - Document Category
Secondary Field - Document Originator

And I have the following table:

Insurance,State Farm
Insurance,GEICO
Insurance,AllState
Healthcare,Blue Cross-Blue Shield
Healthcare,Aetna
Healthcare,Cigna
Investments,Morgan-Stanley
Investments,eTrade
Investments,Charles Schwab

When I select Insurance in the primary Document Category field then only State Farm, GEICO and Allstate would show up as selectable values in the picklist for secondary Document Originator field.

Your advice appreciated.

Skip

Moderator's note: Edited out personal information.

Hello Skip (@Kenn-95a0), might I suggest a slightly different table setup? Below is how you can accomplish this.

Two tables (Categories and Originators):

AND

Link these tables by creating a rowlink in the Originators table, back to the Categories table.

This makes your drop-down configuration simple and scalable.

For Category:

For Originator: (use this filter expression: =FILTER(Originators,"Originators[Category]=%",$[Picklist_Category])

If you wanted to go one step further and ensure the Originator dropdown is sorted, you can do that too with this expression: =FILTER(Originators,"Originators[Category]=% ORDER BY Originators[Originator] ASC",$[Picklist_Category]). You could do the same for the Categories dropdown: =FILTER(Categories,"ORDER BY Categories[Category]")

And here it is in action:

Hope this helps!

aj,

Thanks for the help and that works. I was trying something similar using the FILTER function but here was my problem. I had imported the Originator table from a CSV file and then created the ROWLINK to the Category Table AFTER if ran the import. So, the values were in the cell BUT the links were not established - they were in black text not blue. I need to blow away the table and re-import it but this time create the ROWLINKS before I do the import so that the will get established when I run the import. That said - it would be nice if there were a quick way to run a program or script to establish those links for cells in a table that is already populated. I can foresee the need to create links like this for tables that are already populated.

Thanks,
Skip

aj,

One little cosmetic issue - when I change the Category value in my dropdown in the app, if I already have a value in the Orginator field - I get #VALUE in the text box instead of a nicer "Select an Option" message like you are showing in your screencam video. It makes sense that it is an error because the existing value in Originator no longer matches an associated Category - but - the #VALUE message is not very user friendly. Am I doing something wrong?

Continuing the discussion from Cascading Dropdown:

Hello Skip, couple things. For your first reply, I understand what you are asking about regarding the ability to run a program or script to automatically establish rowlinks. There are a couple options that I wanted to make sure you were aware of. One is the ability to import data to an existing table and automatically perform the rowlink mapping. You may have been referring to this already which is where you had to blow away the existing table. Here's the link: Working with Table. The other option is to use our picklist/rowlink wizard and that's described here: Automatically initialize rowlinks for imported tables. Neither may work for your specific scenario but wanted to bring them up just in case.

The second issue you brought up is a good find. I am able to replicate this and after trying several different things (IFERROR, ISERROR, etc.) the pesty #VALUE does not go away. I agree that this is not user friendly and quite unexpected. I have since reported it to our engineering team to review.

Hope this all helps and keep us posted on how things are going and if you need any additional assistance!

1 Like