Help

Ignoring case in filter by formula

3935 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Thomas_Grist
6 - Interface Innovator
6 - Interface Innovator

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.

3 Replies 3
IF(
  OR(
    LOWER({playerFirstName}) = "thomas",
    LOWER({playerLastName}) = "thomas",
    LOWER({playerTeamsPlayedFor}) = "thomas"
  ),
  1,
  0
)
Thomas_Grist
6 - Interface Innovator
6 - Interface Innovator

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.

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
)