Pulling in text from another table with multiple search keywords

[tothemoon]

Hi!

I have two tables, and would like to pull certain results from one table into the column of another. Here’s an example:

Order Form

• Name
• Item
• Notes
• Special Instructions

Search

• Keywords
• Notes & Special Instructions (these would be all Notes and Special Instructions from Order Form that contain any of the words mentioned in in Keywords

I can currently do this by adding a desired keyword to look up under Keywords - but I can only do one word at a time with this code: [Order Form].Filter([Notes].Lower().Find(thisRow.Keywords.Lower())>0 or [Special Instructions].Lower().Find(thisRow.Keywords.Lower())>0)

So if I put allergy under Keywords, it’ll pull all text from [Order Form].[Notes] and [Order Form].[Special Instructions] that contain allergy. But I want to be able to do multiple words, like allergy, allergies, intolerance in the same row.

Whether I enter them separated by a comma, a list, or whatever, doesn’t matter. Any suggestions?

[Federico_Stefanato]

Hi @tothemoon,
and welcome to Coda Community!

There might be different approaches, but if I correctly understood your point, this might work:

Let me know if this goes in the right direction.

In the meantime…
Happy New Year!

[tothemoon]

That’s awesome! I got it working in my doc based on how you did it. Thanks so much.

• Is there a simple way to include phrases as the search words? For example, you have some things in your example that are space-themed and clothing themed. I might want to search about physical space instead of outer space. I might want to include a word that must be included for context, or search for a phrase like lot of space, no space, lots of space, etc.
• Is there a way to remove punctuation? If I search for summer and the response is I can't wait to go on vacation this summer!, summer wouldn’t be included because we break words up by spaces, so the summer in that sentence is considered summer!.

[Federico_Stefanato]

Hi @tothemoon,
Happy it was useful!

@tothemoon

Is there a way to remove punctuation? If I search for summer and the response is I can't wait to go on vacation this summer! , summer wouldn’t be included because we break words up by spaces, so the summer in that sentence is considered summer! .

Yes, just modify the Words column as follows:

ListCombine(thisRow.Notes.Split(" "), thisRow.[Special Instructions].Split(" ")).FormulaMap(CurrentValue.Lower().RegexReplace("[^\w]","" ))

The RegexReplace("[^\w]","" ) basically searches and removes any non-word.

@tothemoon

Is there a simple way to include phrases as the search words? For example, you have some things in your example that are space-themed and clothing themed. I might want to search about physical space instead of outer space. I might want to include a word that must be included for context, or search for a phrase like lot of space , no space , lots of space , etc.

Hmmm…
I wouldn’t say so, unfortunately.
However, have a look at this example by @BenLee and see if it is inspiring:

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

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 …