How to create a table search engine based on a question?

Hi there! :slight_smile:

Context:

My team and I are exchanging a lot of questions and answers every day and sometimes we tend to forget certain answers that have been answered a few times in the past.

I created a Coda doc where I gather all the questions and the answers in a table.

I created a little search engine where I can type a keyword and it’s filters the table bollow to show me every rows that contains this keyword and it works great

BUT! :slight_smile:

I’d like to bring this to another level and be able to type a random question (Instead of a single keyword) in the search engine and get the results based on the words used in my question.

And if possible that would be great if it would not be “Case sensitive”

Any help would be VERY appreciated :stuck_out_tongue:

Thank you!

Hi Dom,

After I read this an idea jumped into my head so I figured I’d give it a shot. There’s a some complexity to it, but overall it’s not too bad.

The idea is to score the search results based on the number of words in common with the search phrase. To do that, we split all text at the spaces, then replace a few things like punctuation, then we have two lists we can compare. We can also use Lower() to make sure that results are not case sensitive.

Check it out and give the accuracy slider a try to see how you can limit search to only very accurate results!

2 Likes

@BenLee you are a genius man! :exploding_head:

Thank you SO much, you just save my team a whole LOT of time :pray: :pray: :pray: :pray:

It’s even better than what I needed! :star_struck:

2 Likes

If you can create an option to give you a raise let me know, I’ll click it a few times hahaha :crazy_face:

2 Likes

Thank you!

I ran this idea by @Paul_Danyliuk because he knows far more about Regex than I do and he has a much better substitute for that part of the formula. I edited my example doc to include it.

What I had was…well…a mess :laughing:

Here’s the suggestion Paul mentioned using instead and it indeed does a better job of removing punctuation from the mix…

RegexReplace("\s+", " ").RegexReplace("[^a-zA-Z0-9 ]", "")

The idea is that “Here’s” and “Heres” would count as the same word since we often type quick and/or inexact for search terms.

Glad it’s working for you all!

It works really great thanks to both of you guys! @Paul_Danyliuk :innocent:

Is it possible to create a column that automatically general a unique serial number to each row?

That way if a teammate find a error in a Q&A, they can mention the serial number that needs to be corrected? :thinking:

Use the RowId() formula, you’ll see that it prompts you for RowId(thisRow) when you start typing to add it in the column.

This is a unique value that isn’t duplicated. Say you have a row with ID “11”, even if you delete that row, the next row added will pick up at “12”, so it’s completely unique.

1 Like

Thank you it works perfectly :star_struck: