Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 05, 2023 09:47 AM
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!
Solved! Go to Solution.
Oct 05, 2023 04:23 PM
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}
)
Oct 05, 2023 09:48 AM
Duh, sorry, I cut and pasted from an earlier incorrect version. Here's my code, which is improved but still not working:
Oct 05, 2023 04:15 PM
I recommend using nested IF() functions instead of SWITCH().
Oct 05, 2023 04:23 PM
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}
)
Oct 06, 2023 06:23 AM
Thanks everyone — looks like nested IF statements are the way to go!