Filtering table rows based on multi-choice dropdown values

[Ramya_Venkatesh]

Hello,

I’m trying to build a search interface for my data. I want the users to be able to filter data based on different criteria. I have an Athletes table with some columns like Programme, State, Sport etc. I’ve created a page for search as shown in screenshot. When I select multiple values in the dropdown, I want all rows matching both the values to show up. But it’s showing no results. I guess it’s because the column entry does not match exactly due to multiple options selected. I’m not sure how to fix this though. Then, I want to extend the search to the other dropdowns as well. Basically, I want the search to behave exactly like Google Sheets’ filtering on multiple values.

[Darren_Murphy]

Use a Split Text column to convert the list into an array, and then use that in the filter.

[Ramya_Venkatesh]

Hi Darren,

Thank you for your response. That’s one step closer to what I’m looking for. I was saving the user filtered options in a different table called ‘Search Helper’ table with user specific columns. The filter condition for the list view was comparing source column value with the screen values. But the source for the list is coming from a different table. Are you suggesting I add the split text column to the source table itself?

[Darren_Murphy]

No, add it to your Helper Table.

[Ramya_Venkatesh]

The Helper Table values are not available to filter on. All values are from the source table. Attaching screenshot.

[Darren_Murphy]

Okay, so you have two options:

• Use Single Value columns to pull the filter values into your data table
• Write the filter values to your User Profile row instead, and then they can be accessed directly from anywhere.

[Ramya_Venkatesh]

I tried having a split text column in the user profile row itself, but these columns are not showing up in the filter dropdown. Any idea why?

[Darren_Murphy]

mmm, my bad sorry. I forgot that User Profile arrays cannot be referenced on the left hand side of a filter.

What you can do, however, is move the filtering logic to the Data Editor, where you can reference them in an if-then-else column.

So in your Data Table, you could have something like:

• If User Profile->[Search] Programme Filter doesn’t contain Programme, then null (leave empty)
• If User Profile->[Search] Sport Filter doesn’t contain Sport, then null
• Else true

And then on your filtered Collection, refer to that if-then-else column, only showing rows where it is checked.

I actually prefer this approach myself, especially if you need similar filter conditions on multiple components.

[Ramya_Venkatesh]

This works as long as something is selected in each dropdown. But I also want to allow users to not select anything in the dropdown which is an equivalent of ALL for that dropdown. This seems more complicated than I expected with If Else conditions here. I tried having an ‘is empty’ condition but once there is a true condition, the rest of the checks don’t happen.

[Darren_Murphy]

Yes, that is a little more complicated.
The way I normally do it is with multiple if-then-else columns, one for each filter, and then a final if-then-else column that ties them all together.

The technique is demonstrated in the below video:

Custom Filters using Data Editor logic

[Ramya_Venkatesh]

Thank you, Darren. It is tedious, but it solves the problem. The video link was very useful! I’m also building some charts, so will be taking some tips from there