Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Complex Switch using AND?

Topic Labels: Formulas
Solved
Jump to Solution
1209 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)

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!