Skip to main content

FIND Formula

  • July 4, 2023
  • 6 replies
  • 164 views
J
Forum|alt.badge.img+3

I want to use an airtable search formula, I already have one working, but I need to upgrade it a bit and make it a little more complicated. 

Current: FIND("*{{1.postcodeZone}}*", {Postcode Zone})

I want to add 2 more requirements, so the first is a particular checkbox field must be set to yes/true.

IE "Field 1 = true"

AND then additionally, I want another requirement to be that a longer field, contains certain text, or the phrase "all"

IE "Field 2 CONTAINS abc or 'all'" 

So I want all these 3 above parts to be required for anything to be returned

6 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Yeap, you could do this with the AND() and OR() functions, documentation here

Are you facing any specific difficulties in creating the formula?

Adam, Airtable Consultant - thetimesaving.company
J
Forum|alt.badge.img+3
  • jtpr
  • Author
  • New Participant
  • July 4, 2023

I simply can't get my head around how to structure it. I'm not very techie.

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • July 4, 2023

Once you get past the first wall, you will understand

If I put it into a formula as you have explained, it would be this.

IF( AND( FIND("*{{1.postcodeZone}}*", {Postcode Zone}), {Field 1}, OR( FIND("all",{Field 2}), FIND("abc",{Field 2}) ) ), "true", "false" )

 

J
J
Forum|alt.badge.img+3
  • jtpr
  • Author
  • New Participant
  • July 5, 2023

Once you get past the first wall, you will understand

If I put it into a formula as you have explained, it would be this.

IF( AND( FIND("*{{1.postcodeZone}}*", {Postcode Zone}), {Field 1}, OR( FIND("all",{Field 2}), FIND("abc",{Field 2}) ) ), "true", "false" )

 


Thanks, I really appreciate that.

I think maybe I didn't explain well. I ALWAYS need the postcode zone to contain the input variable.

So with the above in mind, I had a play around, but I get an error.

 

IF(
AND(
FIND("*NN1*", {Postcode Zone}),
{Test Field 1},
OR(
FIND("*NN1*", {Postcode Zone})
AND(
FIND("all",{Test Field 2}),
OR(
FIND("*NN1*", {Postcode Zone})
AND(
FIND("abc",{Test Field 2})
)
),
"true",
"false"
)

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • July 5, 2023

 

I still can't see what your goal is.
Add a check for postcode and if postcode is empty, it will be false.

IF( AND( AND( FIND("*{{1.postcodeZone}}*", {Postcode Zone}), {Field 1}, OR( FIND("all",{Field 2}), FIND("abc",{Field 2}) ) ), {Postcode Zone} ), "true", "false" )

 

J
Forum|alt.badge.img+3
  • jtpr
  • Author
  • New Participant
  • July 11, 2023

 

I still can't see what your goal is.
Add a check for postcode and if postcode is empty, it will be false.

IF( AND( AND( FIND("*{{1.postcodeZone}}*", {Postcode Zone}), {Field 1}, OR( FIND("all",{Field 2}), FIND("abc",{Field 2}) ) ), {Postcode Zone} ), "true", "false" )

 


Ok, so the use case is, I'm using the make.com airtable formula search field.

 

What I'm trying to do, is distribute leads to local clients. IE, they cover that postcode AND then they offer the service the lead is looking for...

 

So, they need to have the postcode listed as something they cover AND then they need to have the service offering, hence the AND/OR, ie they offer that service specifically, or offer "all" services.

 

I've run this formula, and it just returns a bunch of records that don't even contain the postcode zone...

Terms & ConditionsAccessibility statement