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!

2

As far as I know:

  1. No
  2. Yes :slight_smile: See below.
  3. This is just a limitation.

You’ll need to get all records from Movies and do the filtering manually. Fortunately, Python is a very well-suited language for that kind of task, it’s really quite simple:

result = []  # List of records that match. We'll populate it below.
for record in Movies.lookupRecords():   # .lookupRecords() without arguments is equivalent to .all, it fetches everything.
   if "Drama" in record.Genre:
      result.append(record)
return result

If you prefer, you can turn that into a one-liner using Python’s list comprehensions:

return [record for record in Movies.all if "Drama" in record.Genre]

Hope it helps!