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
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.
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]")
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.
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?
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!
hi @Kenn-95a0, just following up on the second item related to the #VALUE message. Our engineering team has been made aware and we've added it as a feature request. We all agreed with your sentiment, which is that this is not a user friendly message. Thanks again for reporting this.
In the meantime, there is a workaround! Check out Conditional Styling. To demonstrate one option see below. Basically what I've done is add a conditional styling of turning the text of the picklist white (the background color) of =ISERROR($[Picklist_Originator]). This basically hides the text, as shown in my GIF.