I would like to use a variable Filter passed as an argument to a Column List to filter results based on an input variable. I have a dashboard that shows a summary count of each category based on a picklist. When I click a category in the dashboard, it navigates to the page with the Column List and I want it to only show the results filtered by that category I chose.
I pass the variable to filter on to the second screen but there I don't know how to create a conditional filter. Basically what I want in the Column List is:
Use the $[FilterCategory] variable to filter in the Column List using FILTER()
Use the default filters (i.e. no filtering) in the Column List if the variable is empty or not passed by another screen.
I tried using something like these but obviously they do not work and I get a syntax error: =FILTER(Clients,"Clients[Category]=%", IF($[FilterCategory]<>"",$[FilterCategory],""))
The following formula as a data source for the list says missing reference. I guess because the List does not see the $[FilterCategory] variable at all. =FILTER(Clients,IF($[FilterCategory]<>"","Clients[Category]=$[FilterCategory]",""))
Which says: If there's no value for the filter category entered, show all of the Clients table rows. Else, show the Clients table rows filtered by that selected category.
I'll also note that Honeycode has an auto-filter and sort feature, which may meet your needs too. Under the properties tab of the list, click on the Display tab, and you'll see options to add user controls to sort or filter by certain columns.
Thanks for your reply @Alyssa . Yes, I'm aware of the auto-filter feature but I wanted the user to be able to see a summary of different categories on a dashboard page, then clicking a category would take them to the list page that's already filtered by the category they clicked on.
The Dashboard looks like this, it's a List based on the Category picklist, the left column shows the categories (=[Category]) and the right column shows the client count (=[Clients]) with that category.
Clicking on a category row takes you to the Client List page that gets filtered based on the category the user clicked using an Action taking data from =THISROW()[Package Level] and writing to =$[FilterCategory]:
On the Client List page I had to add a hidden Data cell as a Variable type below the Column List and name it FilterCategory to store the $[FilterCategory] variable. Otherwise your formula gave the same missing reference error as the one I tried. After adding this data cell, even the formula I tried before started working.
So it all works by clicking a category and passing it as a variable for filtering. Navigating to the Client List page without clicking a category continues to shows all Clients without filtering as expected.
