How to create a sub-picklist?

[Jeroen-71]

Hi,

I want to create two picklists, where the chosen option in picklist1 determines the available options in picklist2. I created a table1 which contains one column with the main categories. I created a table2 which contains column1 with the subcategories and column2 with the main categories.

I wrote the filter for picklist2 as follows : =FILTER(Table2,"Table2[Column2]=$[Picklist1]") , which gives me an empty picklist2.

If I replace the reference to picklist1 and just enter the chosen main category : =FILTER(Table2,"Table2[Column2]=""Main Category 1"""), it does give me the subcategories belonging to Main Category 1.

How can I make this work?

Thanks, Jeroen

[Khushboo]

Hello @Jeroen-71,

You can follow the steps below to achieve similar use case:

1. Create a table1 with Main category as the column.

2. Create a table2 with column1 and column2. Notice that column2 ie Main Category is a picklist.

3. Create a table3 with column1 as the Main Category picklist and column2. Notice that the column2 has a FILTER formula that pulls all the sub categories from table2 that are under the main category in column1 of table3. The column has a formula =Filter(Table2, "Table2[Main Category]=[Main Category]") that can be used to create a sub-picklist in this case.

Hope this helps! Happy building

Thank you.

[Akashgurnani]

HI, Could you please also share screenshots of how it will be implemented in Screen Builder?

[Sid]

Hi @Akashgurnani

This is my table setup:

• A table called MainCategories with the main categories
  
• Another table called Subcategories. This table has all the subcategories and their related main categories from MainCategories table. The MainCat column is formatted to contain rowlinks from the MainCategories table
  

Here is how you would implement this in the App Builder:

• Add an editable Datacell and set its type to variable. Change its format to rowlink from the MainCategories table. The Datacell's name is MainCat- the name will be used later.
  
  
• This gives you the first picklist of Main Categories
  
  
• To configure the second picklist, add another editable Datacell and set its type to variable as well. Change its format to rowlink, set source type to filter and set the filter source to =FILTER(SubCategories,"SubCategories[MainCat]=$[MainCat]")
  
  
• This gives your second picklist which only displays Sub Categories related to the picked Main Category.
  
  

Hope that helps! Happy building.

[Jeroen-71]

Yes , that works like a charm. Thank you Sid. Now I can start to build.

Greetings,

Jeroen

[Veda-78db]

this works, thanks

[John-d7fc]

@Khushboo

=Filter(Table2, "Table2[Main Category]=[Main Category]")

Can you show the code where you do the same filter for the list but it has to meet/match multiple conditions? I.e. Main Category & 2nd Category must match for the filter?

[Alyssa]

Hi @John-d7fc,

Thanks for chiming in here!

If you'd like to add more conditions or criteria for a filter, you would create a new table to store the criteria, and in the filter formula, you would use the AND() operator to add the conditions.

Building on the example in this thread, this is what it would look like:

Create a new table to specify the desired criteria:

Then add a new column to specify the criteria to relate to the main category:

And for the filters on the first table, you would use the AND() operator to specify what you'd like each filter to match with:

In my example, I used the following formula structure for the new columns:

=Filter(Table2,"Table2[Main Category]=[Main Category] AND Table3[Criteria]=%","Criteria 1")

You can use this concept to add as many new conditions and criteria as you'd like.

Let me know if this helps, and if you have any further questions.

[arammaliachi]

Does this work only because col SubCat is first in its table? What if I want to pick a column placed somewhere else within the table?

I was trying:

=FILTER(SubCategories,"SubCategories[MainCat]=$[MainCat]")[other column]

but without success... any help?

[Alyssa]

Hi @arammaliachi,

Thanks for chiming in here, and welcome to the community!

If you format a column in a table as a rowlink, you can set which column would be displayed. So for example:

In an app, a rowlink/picklist to a table will display the first column of that table.

One idea you can explore though, is to point to a column in a specified row that is a rowlink/picklist to another table. An example of this is the Simple Survey template.

In the template, the Survey table has columns already formatted to rowlinks:

And in the app, the picklists are specified in the source of the data cells:

Note too that it is pointing to the column of the specified input row:

Just an idea to explore.

Let me know if this info helps!