Issue with Substring Matching in lookupRecords

Ask for Help Formulas
1

Hello Community,

I’m facing an issue with substring matching in lookupRecords for a text column. Specifically, I want to filter rows where a text column contains a specific substring. Here’s the setup:

Tables and Columns

  1. Movies Table

    • Genre (Text column): Contains values like "Drama", "Action-Drama", etc.
    • Box_Office (Numeric column): Box office revenue for the movie.

  2. Totals Table

    • I want to calculate the total Box_Office for all movies where Genre contains "Drama".

Formula Attempted
In the Totals table, I’ve tried:

SUM(Movies.lookupRecords(Genre.contains("Drama")).Box_Office)

But it throws an error:

NameError: name 'Genre' is not defined

What I’ve Tried

  1. Verified the Genre column exists and is of type Text.

    But it still doesn’t seem to work with lookupRecords.

  2. Explored using CONTAINS, but that’s only applicable to list-type columns like Choice Lists or Reference Lists. (does not seem to work either if I convert the column to a Choice Lists)

Expected Behavior
I want to filter rows where the Genre field contains "Drama" as a substring (e.g., "Action-Drama" or "Musical-Drama") and sum the corresponding Box_Office.

Questions

  1. Does lookupRecords support substring matching for text columns?
  2. Is there a workaround for substring filtering in lookupRecords for text columns?
  3. Is this a limitation, or am I missing something in the formula syntax?

Any insights or suggestions would be greatly appreciated!