Lookup formula does not equal

Ask for Help Formulas
1

Hello All,

I’m curious if anyone has tried a non-equivalence check in a lookup formula. I’m trying the following formula with a != (does not equal) operator:

State_Machine_Log.lookupRecords(Contract=$Contract_ID, Plan_Version_ID != $Plan_Version_ID)

This returns SyntaxError : positional argument follows keyword argument

If I replace the != (does not equal) with = (equals), it works so I know the column names are correct. I just wanted to see if anyone knew if non-equivalence checks were possible in lookup formulae.

Thanks for the help!

2

Hi! No, Python syntax does not allow this, so you’d need to achieve it using a different way. You can using Python list comprehensions to filter out records with not-equal ID:

all_versions = State_Machine_Log.lookupRecords(Contract=$Contract_ID)
return [v for v in all_versions if v.Plan_Version_ID != $Plan_Version_ID]

If you only want a single version, say, one that’s right after or before the current one in sorted order, you can do it more efficiently using NEXT or PREVIOUS, e.g. PREVIOUS(rec, group_by="Contract", order_by="Plan_Version_ID") (see Function reference - Grist Help Center)

If you just want to know if there are any other versions for the purpose of detecting duplicates, you can check the count like len(State_Machine_Log.lookupRecords(Contract=$Contract_ID)) > 1.