Filter with two controls with fallback to show all

1

Hey all -

I’ve tried to figure this out using a ton of the incredible feedback you’ve all provided for other users. Alas, I’m stuck.

I’ve built a growingly complex but super useful CRM. I’m using a field called tags to drive a lot of automation.

I’ve created three controls:
filteron = checkbox
FilterTag = a single selection control that pulls items from the Tag list table
FilterCity = a single selection control that pulls items from the City list table

Rows:
Tag = lookup to Tag list table
Current City = lookup to City table

The filter formula:

If(filteron=true,And(And(thisRow.Tag.Contains(FilterTag)),And(thisRow.[Current City].Contains(FilterCity))),thisRow.Tag.Contains(""))

The controls and filters appears to be working properly in a basic way. But there are three issues:

  1. The only options in the select control are a specific item or Blank, which pulls records that are blank. I can’t figure out how to reset the box or insert an option for ALL.
  2. I can’t select between And/Or in terms of filtering by Tag AND City. One way to accomplish that would be a toggle, but another way is to actually solve #1 so that I could filter by Tag then just select all for cities or whatever.
  3. The fallback if the If logic is false is currently “” because I don’t know how to add “show all records” to the filter formula.

I’m sure this isn’t too complex I’m just hitting a wall after working on it for some time.

2

@zachw

Play with this and see if it gives you any ideas:

2 Likes
3

Wow @Ander, thanks! You nailed it. Way above and beyond.

The only logic change I made was changing the OR to AND which takes advantage of the select alls so you can do things like isolate a single tag in a single city.

You rock!

2 Likes
4

Hey Ander - this looks amazing - but I can’t figure out how it works. Would you mind explaining how selecting and deselecting the check box is adding and clearing the filter?
I tried opening the Coda doc to get a better look - but since it’s read-only I can’t get to the underlying logic.

5

whoops - never mind - I can get to it in the view here. - :pray:

6

Hi everyone! You might be interested in our new Set control value action :slight_smile: