Skip to main content

Hi

I would like to compare 2 text fields. The first in "Internal" and the second is "External".

I saw this solution

https://community.airtable.com/t5/formulas/comparing-of-two-values-in-rolled-up-fields-and-showing-true-if/td-p/61679

IF( ARRAYJOIN({Internal}, {Internal}) = {External}, 

  "same", 

  "different"

)

which works only if the text fields are excatly the same including lower and apper case. ,eaning it is case sensitive.

I am looking for a non case sensetive solution.
IF( LOWER(ARRAYJOIN({Internal}, {Internal})) = LOWER({External}), "same", "different" )

but "= LOWER({External})" is illigel

How can I use

IF( ARRAYJOIN({Internal}, {Internal}) = {External}, "same", "different" )

to be non case sensetive?

 

 

 

I also tried:

SEARCH(LOWER({Internal}),LOWER({External}))

it should yeild 1 if the strings are identical but this results in error.

I also tried:

SEARCH(LOWER({Internal}),LOWER({External}))

it should yeild 1 if the strings are identical but this results in error.


I found the solution.

The prolem seems to be that Airtable doesn't store lookup fiels as text (the internal was not text but lookup)

so this is the solution:

IF(SEARCH(LOWER(CONCATENATE({internal})),LOWER(external)),"Good","Not Good").
 
CONCATENATE({internal}) transforns the field to text.
LOWER(CONCATENATE({internal})) and LOWER(external) transforms all to lower case
 
ans search function compers them (returs 1 if identical).

Hi there,

Glad to hear you found a solution. It seems to me though that this is a more complex formula then it needs to be.

Did you try just doing this:

IF(LOWER({Internal}) = LOWER({External}),
"same",
"different"
)

Hi there,

Glad to hear you found a solution. It seems to me though that this is a more complex formula then it needs to be.

Did you try just doing this:

IF(LOWER({Internal}) = LOWER({External}),
"same",
"different"
)

Hi James

Thank you but in case one or more of the fields is not plan text then I won't work since apperentaly Airtable does not see Lookups as texts but as references.

A solution is to add CONCATENATE before the lookup field.

 

IF(CONCATENATE(LOWER({Internal})) = LOWER({External}),
"same",
"different"
)


Thanks

 

Terms & Conditions