Ignoring case in filter by formula


#1

Hi there,

I have a multi-condition if formula:

IF(OR({playerFirstName}=“thomas”,{playerLastName}=“thomas”,{playerTeamsPlayedFor}=“thomas”),1,0)

The data says “Thomas” not “thomas” so unfortunately the above returns nothing. If I change the values to “Thomas” it returns records.

How do I convert everything to lower case or ignore the case?

I know from reading related topics I need to try to utilise LOWER() somehow but I can’t work out what to wrap in the formula above despite trying a lot of different things.

Any help would be greatly appreciated.

Cheers,
Thomas.


#2
IF(
  OR(
    LOWER({playerFirstName}) = "thomas",
    LOWER({playerLastName}) = "thomas",
    LOWER({playerTeamsPlayedFor}) = "thomas"
  ),
  1,
  0
)

#3

Thanks again Jeremy - this works.

Also worth noting for anyone reading that part of my problem was that playerFirstName and the other two fields were lookup fields which I think LOWER() can’t affect. A workaround for this was to create formula fields in the linked base which lower cased the data e.g. LOWER({playerFirstName}) - and then look up that new field.

Then there is no need to apply the LOWER() function within the if formula.


#4

Lookup fields are passed as arrays, even if they only contain one element. You can cast a lookup field to a string by appending &'' to it — so LOWER(playerFirstName&'') should work.

@Jeremy_Oglesby’s formula, then, made lookup-friendly, would be

IF(
  OR(
    LOWER({playerFirstName}&'') = "thomas",
    LOWER({playerLastName}&'') = "thomas",
    LOWER({playerTeamsPlayedFor}&'') = "thomas"
  ),
  1,
  0
)