Hack to filter lots of data

Here’s something you might enjoy, I updated this example to make the search box personal (i.e. each user’s search won’t disrupt anyone else’s):

11 Likes

That’s really clever! I’m definitely going to try that trick for a couple use cases!

I am trying to use the LOWER() to combine columns like above but I get a weird output that has the word grid mixed into it. I think it may have to do with the fact that the columns I am trying to combine are look up columns and a selected list column that references another table.

The lower() works fine for each column individually, but once I add some together with +","+ it does not play nicely.40%20AM

@Catherine_Infantolino

Since I can’t read your formula or a see screenshot of what you’re attempting :stuck_out_tongue_winking_eye:, here’s a guess: try using Concatenate() instead of +.

Hi,

For the lookup columns I added ToText() and then it works fine.

Lower(Description+","+FormulaSelect.ToText()+","+[Doc<>])

Does this work for you.

1 Like

.ToText helped! thanks!

1 Like

Can I get a link to this doc. Please :slight_smile:

This post has a doc link with several examples in it…

Would any of you be willing to walk me through how to set up/write a formula for a super basic regex search? It would only need to search in one column based on the user’s input. I’ve been all over the playground docs and the discussion above, and it’s all about five tiers more complicated than I can handle yet… :confused:
Thanks!

@Bria_Fleming I am around and though not a regex expert can give it a try. Want to share a basic doc here (with edit access) and we can work on it?

Thanks @mallika I tried to create a doc to share but the domain associated with my work email won’t let me share it with anyone outside our organization. :expressionless:
I don’t suppose there’s just a formula template we could work on that I could plug the names of columns and tables into later or something?

Did this example help?

This gets me most of the way there, thank you! I just have to play around with putting my own table/column names into the formula you have in the table filter. If I have any specific issues I’ll post here. Thanks again!

1 Like

@BenLee , I’ve got the formula plugged into my own table filter, showing no errors. When my search field is blank, it shows my whole inventory list which is great. However as soon as I add anything at all in the search bar I get “No results from filter” Here’s the formula as is with names from my tables plugged in, just in case you see obvious mistakes: If(SEARCH.[Column 2].First().IsBlank(),thisRow.IsNotBlank(),thisRow.Description.RegexMatch(SEARCH.[Column 2].Lower()))

Any ideas? Thank you again for your time.

This could be a couple things.

When you type in your “search box” the value you want to search for, you need to then click outside that cell somewhere so it registers with the filter in the table.

The other thing that might be happening is Regex is case-sensitive. If you have a “Description” column, it might be worth making another column called “Description Lowercase” and using the formula…

Description.Lower()

You’ll see that this will make everything in description a lowercase value so it will match anything you enter in search.

The Lower command did it! Thank you!!

1 Like

Why are you all using regex for basic substring search though?

Use thisRow.[Full Text].Find(@Search.SearchFor.ToLowercase()) != -1 instead.

  1. This will look for exact string, while regex is in fact full of special symbols and patterns, and not necessarily an exact string
  2. This must be more performant than evaluating regex
2 Likes

So funny. I came here today to ask for help with a filtering problem and discovered the perfect solution…submitted almost two years ago by ME! Fortunately Coda has a better memory than I do!

2 Likes

@shishir my intuition is that this require a new row to be added for ea subsequent user. Hmm maybe I could build an automation that when a user is added to the user table it adds a row to the search table? I have several searches throughout my doc and ideally wouldn’t have to be adding and removing as people come and go

Yes, you do need a row per user. For my docs, I’ve added a button that creates a row for the user and it’s disabled if one already exists. It’s also helped steer people towards the search feature because they interact with the doc and see it pop up.

2 Likes