Partial Match Search Box Filter

Hi Guys,

I was wondering if anyone had a solution for a partial search filter:

I’ve tried:

Contains(Code,Search.[Search Box])

But doesn’t really work. What i’m looking for is two points:

  1. If the search box is EMPTY show the whole list (i.e. filter is disabled).
  2. If the search box is filled, show all rows which have a partial match. Currently what I have only works for an exact match.

Anyone found a work-around?

Karim

Here is a cool search filter using Regular Expressions that is probably more of what you’re looking for: Hack to filter lots of data

I made a “Playground” doc where I have examples of cool strategies I’ve seen on the forum and this search strategy is one of them. You can check it out here:

6 Likes

Awesome!

I changed the sheet up a bit. Now there’s also a dropdown. The behaviour I want to achieve:

  1. If the dropdown is selected, show me what matches the dropdown.
  2. If there’s something in the search box, show me those rows as well
  3. If there’s nothing in the dropdown, or the search box, show me everything

Right now, if something is searched, and the dropdown is selected, it works.
If theres only something in the search, it works
If there’s only something in the dropdown, it fails

You can see here: dropdown shows all rows:

No change except a search in box:

@mallika

How do we run RegexMatch() searches that are case insensitive?

Also, where can we find documentation for the regex syntax available for our use in Coda?


EDIT: Converting both to Lower() appears to effectively create a case insensitive search.

RegexMatch(Text.Lower(),Search.[Search Box].Lower() )

Still curious about syntax documentation.

1 Like

Hi @Ander - we’ll update our documentation but in the meantime please use this for reference - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions#Using_special_characters

2 Likes

@mallika Perfect, thanks!

Am I missing something obvious on why the filters in the example are functioning together as they are, but not independently?

Anyone have an insight?

@Karim_Noseir - can you elaborate on which filters are working and what are you trying to do that is not working.

@mallika I made some screenshots below… for some reason I can’t change the share permission on the documents. Not sure if people can see / play with the embedded example.

1: nothing selected everything shows (as expected):

2: just a search, 1 row shows (as expected)

3: just the dropdown, everything shows (not expected). Expected behaviour: only widgets rows shown

4: add a search to that, then the dropdown matching rows show and search row shows (as expected)

Hope that’s clear? Formula used was in a post above.

@Karim_Noseir - This is most likely due to the Value in the dropdown and the Category not being the same type. Can you make a copy of the doc and share it with me. Else I’d like to see what the dropdown formula is ( how it is populated) and same for Category. Thanks!

Hi @mallika ,

It’s using lookups to the category table:

And the canvas button:

Also; why am I unable to change the sharing settings on any docs I have? When trying to mark: anyone with link can view or play:

@mallika Did you guys fix a bug or something? It started working with the same formula now.

EDIT: now it’s stopped working again?? Not sure what’s going on.

EDIT 2: it’s got to be something with the regex string returning some weird value when the search box is empty causing it to break the rest. if you add one ‘spacebar’ character to the search box it works:

Hi Karim,

I’ve updated the document. Here’s a behavior now.

  1. If there is only category supplied, results are shown for category
  2. If there is only search supplied, results are filtered to show records matching search text
  3. if BOTH are supplied, then results that match both are shown

Thank you.

For those coming to this thread later for a solution:

  1. If searchbox is empty, it returns true, so it was matching ALL rows regardless of what the dropdown was selecting, making it look like the dropdown wasn’t working.
  2. Solution was the following formula with an if statement to account for an empty search box:

If(Search.[Search Box].First().IsBlank(), Matches(Category,categoryFilter), Matches(Category,categoryFilter ) OR RegexMatch(Text,Search.[Search Box].First()))

1 Like