Hi @ScottWorld
Many thanks for the additional reply and the LinkedIn recommendation!
Ok so I did that, and tried that before you replied, and it didn't work.
However, I have since found the solution, one that was no so obvious but thought I would share it here in case anyone else reading this comes across the same or similar issue.
The original formula was (with some hard coded test data):
AND(Lower({Email (from Members)}) = "{{lower(test@testing.com)}}", {Order Number} = "101")
The Email field, as you can see, is from another table. Therefore it is a Lookup field. What I didn't realise is that Airtable treats these Lookup fields as Arrays.
The solution in this case is, as it is only ever one email per person, is to essentially make the Email Array a string. And the way to do that is by concatenating an empty string to the Email address as follows:
AND(Lower({Email (from Members)}&"") = "{{lower(test@testing.com)}}", {Order Number} = "101")
Not an obvious issue to discover or an obvious one to solve. I'm not sure if this solution would work with a Lookup field with multiple entries (for example a multiple select field) but it does for a single line text or Email formatted field.
