According to the documentation, list column types can be filtered in lookupRecords with CONTAINS like so:
=Table.lookupRecords(haystack_column=CONTAINS("needle"))
It seems like this only works with columns of type Choice List, but not Reference List. Is this the expected behavior or is it a bug? If it is expected, what is the correct way to match for a string in a Reference List column?
This document shows what I mean:
I could never get CONTAINS to work for filtering records in .lookupRecords on a reference list.
I have used a combination of .lookupRecords and python list comprehension to accomplish what you’re describing.
Your example would look something like:
records = Table.lookupRecords()
filtered_records = [
r for r in records for x in r.haystack_column
if x.referenced_haystack_column == "needle"
]
return records
You could simplify this to:
return [
r for r in Table.all for x in r.haystack_column
if x.referenced_haystack_column == "needle"
]
Going to your sample document, the formula in # bar entries (reference) would be:
return LEN([
r.Reference_List for r in Records.all for x in r.Reference_List
if x.A == "bar"
])
The difference is that Grist returns a Choice List as a flat python list and a Reference List as a python list of lists. The first part of the sample code I provided:
r.Reference_List for r in Records.all
is the list comprehension that iterates through all items in a list. In this case Records.all is a list of all the records in the Records table. When using LookupRecords Grist is able to iterate through the Choice List because it’s essentially searching the list of values for a match and returning the index of each match. It can’t (or won’t) return matches to a reference list because it is looking at the list of lists and the top list contains no matching values. You have to go into the list of lists and iterate through each of the sublists to find matching values.
In the second part of the sample code I provided:
for x in r.Reference_List
if x.A == "bar"
it uses the list comprehension to read the sublist. x contains the sublist of Reference List and iterates through each of the list items and “keeps” that item if it matches “bar”. x.A refers to the reference column [A] in the Referenced Table. Then you just wrap that entire function in LEN() to find the length of the resultant list.
Hopefully that helps clarify it for you.