Skip to main content

Ignoring case in filter by formula

  • May 9, 2018
  • 3 replies
  • 148 views
T

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

    J
    Forum|alt.badge.img+18
    IF(
  OR(
    LOWER({playerFirstName}) = "thomas",
    LOWER({playerLastName}) = "thomas",
    LOWER({playerTeamsPlayedFor}) = "thomas"
  ),
  1,
  0
)
      T
      • Thomas_Grist
      • Author
      • Participating Frequently
      • May 9, 2018

      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.

        W
        Forum|alt.badge.img+5
        • W_Vann_Hall
        • Inspiring
        • May 10, 2018

        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
)
          Terms & ConditionsAccessibility statement