Skip to main content
Solved

Confusion with simple formula using FIND() and SEARCH()

C

Hello, I'm trying to check if my "inputValue_1" field occurs in a string pulled from a lookup field (named 'test' in the screenshot below). I'm trying to use this to check if inputValue_1 has a valid input, as defined from this lookup field.

The formula I'm using is `FIND({inputValue_1}, {test})`, which should ideally return the position where `inputValue_1` occurs inside of `test`.  I've tried SEARCH(), but just get errors. This leads me to believe that FIND() is right, but something is off, since I am just getting position zero no matter what. I would expect `new south wales` to return 1, and `victoria` to return 18. Any help is appreciated.

Best answer by Ben_Young1

Hey @coltsh3lby! 

I have a love-hate relationship with Airtable's implementation of lookups.
You will almost never see me use lookup fields anymore due to a myriad of reasons, but I digress.

If you don't care about the finer details and just want to get from point-to-point, then look no further and toss this formula in:

 

 

FIND( TRIM({inputValue_1}), "" & {test} )

 

 

I'm a huge believer in the importance of tribal, community knowledge, so even if you don't care for the details, I'm going to write this out anyways for others who might run into issues in the future.
This is also a bit of a venting session about my frustrations with Airtable's lookup field.

The important thing to note here is that despite appearing like they do, lookup fields do not return strings.
Lookup fields return arrays. Don't get caught up on the details here if you're not immediately sure what that means.
This only matters because Airtable's FIND() and SEARCH() functions require string-typed (text) parameters in order to successfully behave as expected.

With what you were trying to do, you thought you were doing this:

FIND("Value To Find", "valueOne, valueTwo, Value To Find")

But what you were actually (unintentionally) doing was this:

FIND("Value To Find", ["valueOne","valueTwo","Value To Find"])

Now, when you passed an array to the FIND() function, it didn't throw you an error.
This is because Airtable did something on the backend called type coercion.
All you need to know is that Airtable noticed that something wasn't technically correct about what you fed it, but it did its best to try and turn the array into a string value in a last-ditch effort to make it work.

Now, when Airtable's backend did this, it "popped" the last element from the array to use for the FIND() function.

Want to see this happen in action? Take a look: 

You think Record 1 would be the equivalent of:

 

 

FIND( "Value 1", "Value 1, Value 2, testValue1, testValue2, testValue3" )

 

 

But it's actually...

 

 

FIND( "Value 1", "testValue3" )

 

 

We can confirm this by making the "Value 1" array element the last element.
When we do this, we should get the found value.

Just as we expected.
Okay, so like... how can we get around this?

Here's the first method. This is also the method that created the formula I posted at the top.
We can force a more controlled type coercion by pairing the array (lookup field) value with an empty string.

That's what you're seeing in this line:

"" & {test}

This will create a string with the array values in a comma-separated value.
If I utilize that function from the top of this post, we go from this...

To this:

Okay. With this, we arrive at the method for doing this that I actually recommend.

You can actually do this entire thing from a single rollup field and you can eliminate the lookup field.

Here's the formula in that rollup field:

 

IF( AND( values, {Input} ), IF( FIND( TRIM({Input}), "" & ARRAYUNIQUE(values) ), "✔ Found", " Not Found" ) )

 

 

View original
Did this topic help you find an answer to your question?

4 replies

Ben_Young1
Forum|alt.badge.img+9
  • Ben_Young1
  • Brainy
  • 520 replies
  • Answer
  • March 13, 2023

Hey @coltsh3lby! 

I have a love-hate relationship with Airtable's implementation of lookups.
You will almost never see me use lookup fields anymore due to a myriad of reasons, but I digress.

If you don't care about the finer details and just want to get from point-to-point, then look no further and toss this formula in:

 

 

FIND( TRIM({inputValue_1}), "" & {test} )

 

 

I'm a huge believer in the importance of tribal, community knowledge, so even if you don't care for the details, I'm going to write this out anyways for others who might run into issues in the future.
This is also a bit of a venting session about my frustrations with Airtable's lookup field.

The important thing to note here is that despite appearing like they do, lookup fields do not return strings.
Lookup fields return arrays. Don't get caught up on the details here if you're not immediately sure what that means.
This only matters because Airtable's FIND() and SEARCH() functions require string-typed (text) parameters in order to successfully behave as expected.

With what you were trying to do, you thought you were doing this:

FIND("Value To Find", "valueOne, valueTwo, Value To Find")

But what you were actually (unintentionally) doing was this:

FIND("Value To Find", ["valueOne","valueTwo","Value To Find"])

Now, when you passed an array to the FIND() function, it didn't throw you an error.
This is because Airtable did something on the backend called type coercion.
All you need to know is that Airtable noticed that something wasn't technically correct about what you fed it, but it did its best to try and turn the array into a string value in a last-ditch effort to make it work.

Now, when Airtable's backend did this, it "popped" the last element from the array to use for the FIND() function.

Want to see this happen in action? Take a look: 

You think Record 1 would be the equivalent of:

 

 

FIND( "Value 1", "Value 1, Value 2, testValue1, testValue2, testValue3" )

 

 

But it's actually...

 

 

FIND( "Value 1", "testValue3" )

 

 

We can confirm this by making the "Value 1" array element the last element.
When we do this, we should get the found value.

Just as we expected.
Okay, so like... how can we get around this?

Here's the first method. This is also the method that created the formula I posted at the top.
We can force a more controlled type coercion by pairing the array (lookup field) value with an empty string.

That's what you're seeing in this line:

"" & {test}

This will create a string with the array values in a comma-separated value.
If I utilize that function from the top of this post, we go from this...

To this:

Okay. With this, we arrive at the method for doing this that I actually recommend.

You can actually do this entire thing from a single rollup field and you can eliminate the lookup field.

Here's the formula in that rollup field:

 

IF( AND( values, {Input} ), IF( FIND( TRIM({Input}), "" & ARRAYUNIQUE(values) ), "✔ Found", " Not Found" ) )

 

 

K
Matthew_Lanni
J
7 people like this
  • K
    Konrad_Schafers
  • Matthew_Lanni
    Matthew_Lanni
  • J
    Jessica_Plautz
  • H
    Hendrik_Yang
  • Charles_111696
    Charles_111696
  • C
    coltsh3lby
  • K
    KoovyG
H
Ben_Young1 wrote:

Hey @coltsh3lby! 

I have a love-hate relationship with Airtable's implementation of lookups.
You will almost never see me use lookup fields anymore due to a myriad of reasons, but I digress.

If you don't care about the finer details and just want to get from point-to-point, then look no further and toss this formula in:

 

 

FIND( TRIM({inputValue_1}), "" & {test} )

 

 

I'm a huge believer in the importance of tribal, community knowledge, so even if you don't care for the details, I'm going to write this out anyways for others who might run into issues in the future.
This is also a bit of a venting session about my frustrations with Airtable's lookup field.

The important thing to note here is that despite appearing like they do, lookup fields do not return strings.
Lookup fields return arrays. Don't get caught up on the details here if you're not immediately sure what that means.
This only matters because Airtable's FIND() and SEARCH() functions require string-typed (text) parameters in order to successfully behave as expected.

With what you were trying to do, you thought you were doing this:

FIND("Value To Find", "valueOne, valueTwo, Value To Find")

But what you were actually (unintentionally) doing was this:

FIND("Value To Find", ["valueOne","valueTwo","Value To Find"])

Now, when you passed an array to the FIND() function, it didn't throw you an error.
This is because Airtable did something on the backend called type coercion.
All you need to know is that Airtable noticed that something wasn't technically correct about what you fed it, but it did its best to try and turn the array into a string value in a last-ditch effort to make it work.

Now, when Airtable's backend did this, it "popped" the last element from the array to use for the FIND() function.

Want to see this happen in action? Take a look: 

You think Record 1 would be the equivalent of:

 

 

FIND( "Value 1", "Value 1, Value 2, testValue1, testValue2, testValue3" )

 

 

But it's actually...

 

 

FIND( "Value 1", "testValue3" )

 

 

We can confirm this by making the "Value 1" array element the last element.
When we do this, we should get the found value.

Just as we expected.
Okay, so like... how can we get around this?

Here's the first method. This is also the method that created the formula I posted at the top.
We can force a more controlled type coercion by pairing the array (lookup field) value with an empty string.

That's what you're seeing in this line:

"" & {test}

This will create a string with the array values in a comma-separated value.
If I utilize that function from the top of this post, we go from this...

To this:

Okay. With this, we arrive at the method for doing this that I actually recommend.

You can actually do this entire thing from a single rollup field and you can eliminate the lookup field.

Here's the formula in that rollup field:

 

IF( AND( values, {Input} ), IF( FIND( TRIM({Input}), "" & ARRAYUNIQUE(values) ), "✔ Found", " Not Found" ) )

 

 


Wow I didn't know we can use this advance formula in the rollup field! Thanks for the tip. 

TheTimeSavingCo
Forum|alt.badge.img+18

I think it might be due to {test} being a lookup field.  Could you try the following? 

FIND({inputValue_1}, {test} & "")
Adam, Consultant @ The Time Saving Co
C
  • coltsh3lby
  • Author
  • New Participant
  • 3 replies
  • March 14, 2023

Thanks for the help everyone! Yes, the issue was that lookups return an array, rather than a string. I wish it was easier to test these things, or receive sample outputs / error messages. I'm very used to doing this kind of stuff in Gsheets and Excel. In that case, I could have immediately spotted the output of a field was an array, instead of getting a nondescript error message 🙂 Thanks for all the help!

Reply


Most helpful members this week

Terms & Conditions