MAKE Airtable Search Records Formula not retrieving Record

[GLS-74]

I've come across a strange issue.
Using MAKE (aka Integromat) I'm using an Airtable Search Records module with the following formula:

AND(Lower({Email (from Members)}) = "{{lower(2.`recipient email`)}}", {Order Number} = "{{51.`$1`}}")

It accepts it but does not return the required record. The Order Number is a number field, and I've tried the formula with and without the "

I've done with this similar tables and records but for some reason this one is not working and I cannot fathom out why.
Any ideas?

[ScottWorld]

The best method for troubleshooting formulas in Make is to recreate the formula in Airtable, and see if it returns the number 1 for the record in question.

[GLS-74]

Hi Scott, thanks for this. I've not done that before, and am still fairly new to Airtable. So dumb question number 42, how would I Set that up exactly?
Via a view/button/automation/integration? 

[ScottWorld]

Hi @GLS-74,

You would just create a formula field in your table, and use the same formula that you're using in Make. Of course, since you won't have the Make variables to use in Airtable, just hardcode those values into your formula.

Since you're new to Airtable, you might enjoy taking my free Airtable training course, which you can take for free by signing up for a trial membership with LinkedIn Learning:

https://www.linkedin.com/learning/learning-airtable/ 

[GLS-74 · Accepted solution]

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.