I’m trying to write a formula to auto-fill the “Total spent on fruits” column with the proper values ($178.65 for Mom and $40 for Dad). I’ve tried SUM(Spendings.lookupRecords(Client=CONTAINS($id), Product=("Apples" or "Oranges")).Spendings), however this only returns me the spendings for “Apples” and not “Oranges”. Am I missing something?
So this is a bit complicated. The reason you only get “Apples“ is becouse in python “Apples“ or “Oranges“ equals “Apples“. The or operator is a bit strange that way, it will always return the first string (unless the first string is empty and the second one contains some value). So effectively you’re just checking if Product=”Apple” .
Now to get to a solution for your problem. The deeper issue is that honestly lookupRecords is not a great function, you can only check for equality and if you can’t repeat a parameter.
The neatest solution here is to use python list comprehension.Something like this:
SUM([ x.Spendings for x in Table1.lookupRecords(Client=CONTAINS($Client)) if x.Product in [“Apples”,“Oranges”]])
If you’re using a summery table, you can even replace the lookupRecords part with $group. Ending up with this:
SUM([ x.Spendings for x in $group if x.Product in [“Apples”,“Oranges”]])
The whole thing is not exactly Excel intuitive I’m afraid, but it does work. For anyone who (like me) struggles with python list comprehension, this is a nice resource:
And here’s a short mockup of the oritinal proble for further reference:
In my case, since the “Client” column in my Table1 is actually a reference column populated by a formula, and the “Product” column is actually a “Numeric” type (I’d argue a “Text” type would be best suited wince we’re talking about an ID there, but I’m not the owner of that column), I just had to slightly edit your formula to make it work as intended:
SUM([ x.Spendings for x in Table1.lookupRecords(Client=CONTAINS($id)) if x.Product in [30001,30042]])