How to look for words

[Aparicio_Pineyr]

Hey!

How can I get specifics words from a row and add a value for thouse words?
Eg: There is a column for comments and I want to create a workflow that if some of several words are being added to the comments, set a numeric value for thouse words and then triger the email in the worflow.
For this I´m thinking to combine a VC with the expression filtering condition and then the action if the condition in the VC is true.

[Bellave_Jayaram]

If your match words list is not long, you can do it using CONTAINS(). May be slow but it could work.

[Steve · Accepted solution]

Create a separate table with the words to look for and their respective scores, then:

SUM(
  SELECT(
    Trigger Words[Score],
    IN(
      [Word],
      SPLIT(
        [_THISROW].[Comments],
        " "
      )
    )
  )
)

This very inefficient: the SPLIT() would be better moved to a virtual column. Punctuation in the Comments column value will be a problem.

Alternatively:

SUM(
  SELECT(
    Trigger Words[Score],
    CONTAINS(
      [_THISROW].[Comments],
      [Word]
    )
  )
)

That’s probably the better choice.

[Aparicio_Pineyr]

Thanks!
I will try those options

[Aparicio_Pineyr]

@Steve
Works perfect! Thanks

[Aparicio_Pineyr]

@Steve

The formula is working fine, but here is the thing.
Eg: I had a word in the Trigger words table that is “Late” and another word that is “Hate” both word had a minus value, so if in Comments there are no such words, the formula is taking the “ate” as minus values for any word that has the letters “ate” or “lat” or “hat”, so the result for this would be wrong.
How can I filter the condition on the formula to mach the correct word?

This is kind of mining data formula what I am trying to fetch, that why I need too get the exact word not any extract of the letters,

[Steve]

Unfortunately, I cannot see a way to accomplish what you want: AppSheet doesn’t have the mechanisms needed to adequately separate words reliably.

The first of the two options I offered uses SPLIT() to separate individual words and IN() to match whole words, but any punctuators (e.g., period, comma, semicolon) that are immediately adjacent to a word would be captured as part of that word, preventing an exact match.

For instance, the simple sentence, “Hello there, John.”, would be split in the words, “Hello”, “there,” (with a trailing comma), and “John.” (with a trailing period). If you then asked whether the whole word “John” occurs in that sentence, you’d be told no because “John” and “John.” (with a trailing period) are different.

[Aparicio_Pineyr]

Hey Steve,
I was thinking about this, is it posible to play with an equalsIgnoreCase, or something similar to it?

[Steve]

What do you mean?

[Aparicio_Pineyr]

Eg:

If I have the word John or john or john. can I use an expression such as IgnoreCase or something similar to it so then I can get the word ignoring if the word had upper case or period, comma or semicolon…etc…?

The most important thing is to get the word itself so I can create a filter and a workflow if that word or other words appear in a text.

[Steve]

All textual comparisons are case-insensitive (i.e., they ignore case). There is no mechanism for ignoring non-word characters (such as punctuation).

[Aparicio_Pineyr]

Is there a way to add an expression to avoid (comma, punctuation, semicolon) between words?
Eg: an expression that only allow comma, punctuation, semicolon only if there is an space between words.

[Steve]

There is no way to do it given the current functions. You could create a feature request post to request it.

[Aparicio_Pineyr]

Yes, that would be something to think about