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.
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!