Is there a way to identify multiple keywords in a string and add the corresponding tag from a multi-select option?

[zhiliang_chen]

Hello all!

I would like to ask if anyone have an idea how to automate the following scenario or a formula.

Eg. I have different strings like

1. This is a Christmas tree.
2. This is a Xmas tree

I want to identify both the keyword “Christmas” and “Xmas” and tag them to a Multi-select option as “Christmas”

Is there an existing formula or automation to this?

Thank you all!

[Justin_Barrett]

zhiliang_chen:

I want to identify both the keyword “Christmas” and “Xmas” and tag them to a Multi-select option as “Christmas”

Is there an existing formula or automation to this?

Formulas cannot change the contents of other fields. A formula can only collect data from other fields in the same record, and the output is in the formula field itself.

That aside, the basics of what you want to achieve are doable using a combination of formulas and automations, but my main question is about scale. How many keywords do you need to identify? Is it only “Christmas” and “Xmas”, or are there a lot more options? The more detail that you can provide about your use case, the more specifically we can help.

[zhiliang_chen]

Hey Justin,

Thanks for getting back. Maybe the example listed isn’t the best. I would need to match 5-6 different keywords per theme over 100+ records. Another example will be

Total Records: 100 over extracted sentences from user database.
Mapped to a topic: Rewards
Recurring Keywords: Rewards, Cash, Incentives, Benefits, Free Downloads, Gifts

These keywords are now in a string. Eg. I will want to get cash rather than other gifts with my points

What will be the best way for this? Thank you!

[Justin_Barrett]

Thanks for the added detail. You said “5-6 different keywords per theme.” How many themes are there? Could each record be tied to a different theme, meaning matching against a different set of keywords? Any more info that you can share on how the different themes play into this?

Another question: how are records added to the table? Are they coming from a form? Is someone manually typing them in? Something else?

[zhiliang_chen]

Yes you are right.

1. Each record can be added to different theme which is why i decided to use a multi-select option.
2. The themes are just for filtering when I need to find all the records under the same theme
3. These records are manually extracted from a user transcript to be precise.

[Justin_Barrett]

Thanks. My first question didn’t get answered, though: how many themes are there? Here’s why I’m asking: if there are 5-6 keywords per theme, and there are (guessing for the sake of this example) 10 themes, that’s 50-60 keywords total, which is a much bigger problem to solve.

In short, I’m trying to get an accurate picture of the scope of this problem to determine whether it would be best handled via a formula or a script. The bigger it gets, the more unwieldy a formula solution would become, and the more I would lean towards a script-based solution.

[zhiliang_chen]

There are 12 theme so far and it may increase as we expand the dataset.
Thanks for being so patient.

[Justin_Barrett]

With that in mind, this definitely feels like something I would do with a script. To make the script more compact and not have to hard-code all themes and their related keywords directly into the script itself, I recommend creating a [Themes] table. Each record would be a single theme, with the primary field containing the theme name. In a text field you could add all keywords for each theme as a simple comma-separated list. You would then link each incoming record in your main table to its appropriate theme record in the “Themes” table. The creation of that link could trigger a script that reads the related theme keywords from the linked record, parses the transcript, and updates a multiple-select field.

You might also consider adding another table called [Keywords], creating and maintaining your master keyword list there. Then in the [Themes] table, you could link to keywords instead of typing them, allowing you to connect keywords to multiple themes if that works for your use case. The multiple select field in the main table would then become a link field tying to this same [Keywords] table, and the script would update those links after parsing the transcript for a given record. You would then have the added benefit of being able to use count, lookup, and/or rollup fields in that table to track stats for each keyword if desired.

[kuovonne]

Justin_Barrett:

You would then link each incoming record in your main table to its appropriate theme record in the “Themes” table.

I think the use case is the other way around. Thw script needs to search for the keywords and then determine the theme based on the presence of keywords.

[Justin_Barrett]

Interesting…I hadn’t thought about reversing the search like that. My only concern with that approach would be the match process coming back with multiple matching themes. @zhiliang_chen Would that be a problem?