Lookup Partial Value In Another Table

[paulsmartdev]

Hi Everyone,

I'm new to Airtable, so please forgive me if this is a silly question. I'm a big Google Sheets user and trying out Airtable for the first time.

I have two tables:

• Content
  • Text
  • User
  • Date
  • ID
    
    
• Word List
  • Word
  • Club

When a new item is added to "Content", I would like to know if the field called 'Text' contains any word listed in my Word List (separate table).

Example:

"My favourite player is Cristiano Ronaldo" is added to Content.

I would like the "Club" value in my Content table to update with "Portugal" based on the mention of Ronaldo (which is in my "Word List" table).

From the reading I've done, I may need an Automation or a Formula - I'm unsure.

Thanks in advance

Paul

[Alexey_Gusev]

Hi,

I suppose your Content table also has Club field, and you want to fill it from Club field of Word List table according to word.
It can't be done by simple automation, because it can find all records in a given table containing value of you new entered record, but you have an opposite goal.
I would solve it using third table with 2 linked fields - to all records in Content and Word List, and then using rollups. But other solution I like more - add some fields to each table and run 2 automations:

first added field is link (it will be added to both tables), then lookup. in Word List add lookup of Text and formula field FIND(Word, CONCATENATE({Name_of_lookup_Field}).   Name by default will be smth like {Text (from Content)}. CONCATENATE used to convert lookup value to string (alternatively you can add empty string to it, FIND(Word, '"" & {Name_of_lookup_Field} ).  In Content table, add lookup of Club, but use 'Only include linked records....' when formula field = 1.

now about automations: first link each new created record to all Word list. second runs when Club lookup field updates and copy its value to a static 'Club' text field. Next step is to clear all links from linked field.
It will work until Word list became 100+ records. Then you have to add a little scripting or invent an easier solution. I beleive easier solution exists, I just wrote first that came in my head.

[Lom_Labs]

Hello paulsmartdev, I have created an Airtable template for just this purpose and you can purchase it on my Gumroad here

Let's say you have a table where each record contains text you want to find matches for like this:

And so whenever a new string gets added to your base, you want to check if that new string contains any of the text in the table above, and if it does, link them together:

This template helps you do exactly that, and you can test it before buying it!

First, check out the view of all the text that we're searching for: Text to find view
Then, check out the view of all the strings that we want to do the search through: Strings view
Finally, submit a new string to search through via this form: Form

The automation will take 1-15 seconds to trigger, so please refresh the page if you do not see any changes! You will be able to see whether the automation has ran via the "Checked" field, once the automation has ran the "Checked" field will be marked.

Here is an example: