Hack to filter lots of data

#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
8 Likes

Filtering Question for a Text Field
Partial Match Search Box Filter
Search from Within the App
Filtering Question for a Text Field
#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

1 Like

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

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

5 Likes

Playground Doc - A Collection of Tips, Tricks, and Examples
Wildcard in filter?
#6

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

0 Likes

#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

0 Likes

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

0 Likes

#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

Set filter with a button
#10

.ToText helped! thanks!

1 Like