Hitting you up in yet another topic
I have a similar setup, inside a custom form of course.
I’m stuck at allowing the user to also see all options. I’ll explain a bit more:
We have a “type” sheet which lists all the client types (lead, active client, etc).
On the screen in which we list the clients, there’s a choice component is asking if the user would like to filter by a specific client type
Once they select a type, the list is filtered perfectly.
How do we make it that the list will show all clients if the user hasn’t selected anything?
This is something like an opt-in selection, in which if the user doesn’t opt-in a type, all types are shown.
One idea that I had is adding two inline lists:
All types - visible if user didn’t select a specific type
Specific type - visible only once a specific type has been selected
but wait, there’s another layer for this challenge:
Another filter we need to apply is the client status. Similar setup here as well, i.e. status sheet, each row in clients sheet has a column for status, etc.
How do we drill down here? Specifically, there are the following scenarios for the clients screen:
User didn’t select anything, so we show him everything
User selected a client type, but haven’t selected a client status. We show him all clients from that type, regardless of their status
User selected both type and status, we show him only those clients that match both selections.
It’s probably a lot longer to explain than to solve. I’m sure your solution will be very simple, and elegant, as always
Okay, so here is what I would suggest. Note that this assumes that you have both Client Type and Client Status columns in your Clients table.
You will need to add 3 columns to your Clients table.
The first two columns will be Single Value columns, with each one taking one of the User Specific column values that each of your choice components are writing to.
The 3rd column will be where a little bit of magic happens This should be an if-then-else column, that will resolve to a boolean type. If it resolves to true, that means the associated row will be filtered (not shown). Configure it as follows:
– If single value Client.type is empty, then empty
– If single value Client.status is empty, then empty
– If single value Client.type is not Client.type, then true
– If single value Client.status is not Client.status, then true
– Else empty
NB. “empty” above means exactly that. Empty, no value, nil, nothing, blank.
Now you just need a single inline list on your form screen that uses your Clients table as the source. And the filter condition on that should be "where if-then-else column is not true"
I haven’t tested the above, but I’m fairly confident it should work, if I’ve understood your use case correctly.
As @Darren_Murphy said in his comment inside the topic you shared: this is very sophisticated logic and it’s truly impressive.
If I understand correctly, I believe that actually logical AND suits my case better, no?
We have three tiers/levels of filter: First level - Account manager
Here we have two ways to select the filter:
Switch (Boolean) for “show my accounts”. This is meant to be used by, you guessed it, the account managers. Very easy for them to narrow down to their client list.
If Switch is false, we show a choice list of all account managers to choose from.
I have added an ITE column to properly mark the account manager: either “current user” or the name selected in the choice list
Second level - type
We have three options: Lead, Client, Inactive client. User will choose on a sliding choice.
This should be narrowed down to only the client types the account manager has. i.e. he/she might have just clients so there’s no point in showing them a slider with leads and inactive clients. I am using the filter option for the screen component and I think it’s working fine.
Third level - status
Each client type has its own list of possible statuses. Again, using filter inside the components to show only the statuses relevant based on the two previous levels.
I’ve tried your AND logic (brilliant), but it’s not working for me. Tried to copy 100% of the logic from your demo, but no luck
PS - Sorry I cannot share the app, it’s confidential.
Yes, tried it earlier today and couldn’t get it to work. Moved on to the 2nd idea from @gvalero, which didn’t work as well. Now circled back to your idea and, not surprisingly, it’s still not working for me.
Don’t get me wrong, I’m sure both ideas are perfect and the fault is on my side.
I think this will need to be broken down into several IF columns. One for each filter to return true if the filter is empty (not selected) for that particular column, else return true if the filter matches the value in that row, else return false. Then set up a final IF column that checks all other IF columns. If any one of the other IF columns is false, then return false, else return true. Set your filter to only show rows with a final IF value of true.