Match text from one column with list from another table

Hey all!

I’m building a nutrition database and am trying to find a way to get it so that people can free type the foods they’ve eaten and they system will automatically do a ‘best guess’ match to assign it to the food.

I’ve created a dummy doc to show you what I mean. It would be awesome if anyone has any suggestions on how I might be able to achieve this?

Here’s the example doc:

Thanks so much!

Hi @Ben,

Try with this:

[List of ingredients example]
        thisRow.[NLP identify].FormulaMap(CurrentValue.Name.Lower()

The thisRow.[NLP identify].FormulaMap(CurrentValue.Name.Lower() part could be an additional column (such as Processed Entity Names), just to simplify the formula.

Let me know if it helps.

1 Like

Awesome!! Thank you! It seems to be working perfectly!

1 Like

Sorry @Federico_Stefanato, another question on this if you do have a moment!

Do you have any thoughts on a better method of applying substitutions to strings of text?

As you’ll see above, I’m attempting to replace things like “one” with “1”, and “tablespoon” with “tsp” and a whole lot more. I’ve tried using the ‘substitute’ command, but it only replaces one instance of the required replacement, rather than many :confused:

In case it helps, here’s the full context - I’m aiming to have a submission page like this (please ignore the formatting for now haha), and when you submit it’ll automatically split out the submission and try to match it to three columns - quantity, ingredient, and portion measurement (i.e: 3 (quantity) tablespoons (portion) of coconut milk (ingredient)) and looks up the relevant entries from the database (I’ve added portion types on the additional page if it helps!)

Hopefully that makes sense, any thoughts you might have would be greatly appreciated, thank you!!


Dear @Ben_Swanson,

I felt to share this doc about proportional recipes from @Mikael_Mayer

Hopeful it will be somehow helpful :thinking:


Quick solution:

RegexReplace(RegexReplace(“I ate one tablespoon of sugar”, “\bone\b”, “1”), “\btablespoon\b”, “tbsp”)
(Evaluates to “I ate 1 tbsp of sugar”)

Use \b in regex to indicate a word boundary, so that you will not accidentally replace someone by some1.

However, let’s say you want to replace many things at once, you can use the regex trick in my document proportional recipes. See other post.

1 Like

Hi @Ben_Swanson,

have you tried RegexpReplace() function?

You can also chain it with all the occurrences:

[Your String].RegexpReplace("one","1").RegexpReplace("tablespoon","tsp")

Sorry, just posted a message that was ready since a while :slight_smile: I saw @Mikael_Mayer provided basically the same solution

1 Like

Thank you all very much!! That works great :smile:

Sorry again haha but I think I have one more problem which it would be great to get some help with!

Is there a way to change the original query which returns the item to somehow match based on contains?

For instance, if the list of ingredients contains ‘Plain yogurt’ and the person submits ‘yogurt’ the current query doesn’t return anything, whereas I’d like it to return ‘plain yogurt’.

Below is an example (also contained in the doc above) - as you’ll see, I’ve managed to get the desired results for portion size and quantity, but couldn’t figure out how to adapt the original formula that you (@Federico_Stefanato) gave me originally. So if you do have a moment, I would be really greatful!


Hi @Ben_Swanson,

if you strictly need it, we might find a solution, but I’m asking you if this is the actual way to achieve it…

Just from a mere semantic approach In my opinion, ingredients should represent the highest level of generalisation (e.g. why “plain yoghurt” instead of “yoghurt”?) in order to capture the essential components.

Afterwards, you (might want to) build up a way to potentially intercept “plain” of “fruit” or other variations for each component.

Am I say something meaningful?

Thank you @Federico_Stefanato for the thought and comment on this!

The reason that I’m having to work this way around is that I have access to a list of 16,000 different ingredients/meals with the nutrient breakdown already provided (e.g. the exact amount of vitamins for each item), and in that list, they have put things like ‘Plain yogurt’, rather than just ‘yogurt’, whereas, when a user comes to submit their meal, they’re more likely to put just ‘yogurt’

So my goal is to create a simple system here which provides the user with a ‘best guess’, listing the most likely match and then, the user can either accept it with one click, or reject and manually perform a search to find the right answer.

I think this piece is the final piece of the puzzle because, as you can see from the embedded above, I’ve got it performing all the other functions, I just need to figure out a way to get it to run a search on the item (e.g. yogurt) and then I think it’s all there :slight_smile:

Please do let me know if that makes sense or if you have any other ideas on how to achieve this, as it’s all very much appreciated!

Thank you!

Just to close this off, I figured out / hacked together a solution using @BenLee’s amazing work here.

Thanks again for your help!

1 Like