Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Complex Switch using AND?

Topic Labels: Formulas
Solved
Jump to Solution
1641 4
cancel
Showing results for 
Search instead for 
Did you mean: 
dvanhook
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello!  I'm relatively new to AirTable, we're migrating here from FileMaker Pro.  I'm forced to duplicate a calculated field in FileMaker, which is essentially the same a a Formula in AirTable.  Basically it does some complex looking at a zipcode value and either displays a Metro Region if it's a certain zipcode, or instead, just displays the City name.  

I've been trying to do this:

SWITCH(
AND({l_zip} >= 94100, {l_zip} <= 94199), "North/San Francisco",
AND({l_zip} >= 93600, {l_zip} <= 96199), "North/Other Cities",

{l_zip} = 91604, "South/Los Angeles & Vicinity",
{l_zip} = 91711, "South/Los Angeles & Vicinity",
{l_zip} = 90755, "South/Los Angeles & Vicinity",

AND({l_zip} >= 90000, {l_zip} <= 90199), "South/Los Angeles & Vicinity",
AND({l_zip} >= 90200, {l_zip} <= 90599), "South/Los Angeles & Vicinity",
AND({l_zip} >= 90800, {l_zip} <= 90899), "South/Los Angeles & Vicinity",
AND({l_zip} >= 91100, {l_zip} <= 91199), "South/Los Angeles & Vicinity",
AND({l_zip} >= 91500, {l_zip} <= 91599), "South/Los Angeles & Vicinity",

But it appears that SWITCH does not like having ANDs in the match part of the statement.  How would I do something like this?  I need all zipcodes between 90800 and 90899 to say "South/Los Angeles & Vicinity", for example.

Thanks so much!

 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Hello @dvanhook ,

Formulas cannot be used for values evaluated by the Switch function.

How about this?

 

IF(NOT(l_country),
  SWITCH({l_zip},
    91604, "South/Los Angeles & Vicinity",
    91711, "South/Los Angeles & Vicinity",
    90755, "South/Los Angeles & Vicinity",
    IF(
      AND({l_zip}>= 94100, {l_zip}<= 94199),
      "North/San Francisco",
      {l_city}
    )
  )
  , {l_city}
)

 

 

 

See Solution in Thread

4 Replies 4
dvanhook
5 - Automation Enthusiast
5 - Automation Enthusiast

Duh, sorry, I cut and pasted from an earlier incorrect version.  Here's my code, which is improved but still not working:

IF(NOT(l_country),
SWITCH({l_zip},
91604, "South/Los Angeles & Vicinity",
91711, "South/Los Angeles & Vicinity",
90755, "South/Los Angeles & Vicinity",
AND(>= 94100, <= 94199), "North/San Francisco",
l_city
)
, l_city)
kuovonne
18 - Pluto
18 - Pluto

I recommend using nested IF() functions instead of SWITCH().

Sho
11 - Venus
11 - Venus

Hello @dvanhook ,

Formulas cannot be used for values evaluated by the Switch function.

How about this?

 

IF(NOT(l_country),
  SWITCH({l_zip},
    91604, "South/Los Angeles & Vicinity",
    91711, "South/Los Angeles & Vicinity",
    90755, "South/Los Angeles & Vicinity",
    IF(
      AND({l_zip}>= 94100, {l_zip}<= 94199),
      "North/San Francisco",
      {l_city}
    )
  )
  , {l_city}
)

 

 

 

dvanhook
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks everyone — looks like nested IF statements are the way to go!