Hack to filter lots of data

Showcase Tips and Hacks
1

Hi…
I am new to Coda and not particularly savvy when it comes to formulas and filters…but I figured out something that might be useful for others:
I have a large database of disconnected information, sort of call notes from every call I do. I have found Coda to be better overall at handling this kind of info than Evernote or Keep.
I wanted to be able to quickly filter through all the records of calls to get to records with one person or one organization.
I created a view that shows all records in the database, but the search function in the upper left makes you go through the records one of a time. I wanted a filter, instead.
So I did a couple of things:

  1. I created a new column called Full_text with a formula that combines all the relevant info into this column separated by commas and in lower case: Lower(Who+", “+What+”, "+[Notes (Paste from below)])
  2. Then I figured out how to filter this info, with a filter like this: Full_text.RegexMatch("")
  3. This filter is case sensitive, which is why I converted everything to lower case in the new column. (I think this is why the new column with all text converted to lower case is necessary)
  4. I use Keyboard Maestro, so I made a keyboard shortcut for this filter that pastes Full_text.RegexMatch("") and then arrows back two spaces to put the cursor between the “” so I can trigger my keyboard shortcut and just start typing what I want to filter.
    Works great for me and it was fun to figure out, I hope it helps others.
    Overall, Coda has been an incredible gift for us.
    Gregg
17 Likes
2

Neat trick @Gregg_Stebben, thanks for sharing it! Would you mind posting a screen capture of the result, or even sharing a sample doc?

Would also love to make this scenario more easily - if you could quickly to filter any table, would that meet the same need, or would you want something different? Would you want to the search filter to go across all columns, only certain columns, or would it depend on your scenario?

Thanks,
Nathan

2 Likes
3

Hi @nathan.
I would love something similar.
I imagine this could be done adding an input/search control and then choosing which column do you want to filter. This would be a nice feature allowing simple searches/filtering in any given table.

Thanks.
Jorge

1 Like
4

I’ve come back to this post a couple times and finally set up an example. Thanks @Gregg_Stebben!

Playground Doc - Search with Regex

(I set up a Playground Doc to try all these cools tricks. Playground Doc - A Collection of Tips, Tricks, and Examples)

3 Likes
5

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):

12 Likes
6

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

7

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

8

@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 +.

9

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
10

.ToText helped! thanks!

1 Like
11

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

12

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

13

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!

14

@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?

15

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?

16

Did this example help?

1 Like
17

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
18

@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.

19

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.

20

The Lower command did it! Thank you!!

1 Like