@MikeEaster There are several reasons why that formula isn’t creating the output that you want.
First, you need to wrap curly braces around your field reference because it contains a forward slash. CA/FL should be {CA/FL}.
Second, the IF() function only accepts three arguments:
- The condition that you want to test
- The output if that condition is true
- (optional) the output if that condition is false
Your structure uses four arguments, with the apparent assumption that the first two form the condition to test. To compare the field value against a static value, the first function should be something like this (truncated)
IF({CA/FL} = 0, "ca.", ...
Third, I can’t quite tell from your description if your single select field actually contains “0” and “1” to represent “ca.” and “fl.” respectively, or if you’re using the literal text of those options and assuming that a single select field returns a number based on the index of the selected item (which is not the case). Just to clarify, a single select field returns the literal text of the item. If your options are actually “0” and “1”, then comparing those strings against 0 and 1 (numbers) will technically work because even though the single select field returns a string, Airtable auto-converts your 1 into “1” (number to string) for an even comparison. If the field is empty, though, comparing that emptiness against 0 will actually match because an empty field is treated as false, and so is 0, which will then add the “ca.” prefix when you don’t actually want it.
However, if you have the literal text “ca.” and “fl.” as your single select options, that’s a safer option for formula comparisons, and your formula should look like this (again truncated):
IF({CA/FL} = "ca.", "ca.", ...
Finally, your second two IF() functions have spaces between “IF” and the opening parenthesis, which will lead to an error.
Those are the only actual issues with the formula design that could lead to errors or unexpected output. That said, there are some things that you could do to optimize it.
First, remember that the third argument of the IF() function is optional, and will cause the function to return nothing if the tested condition isn’t true. Therefore there’s no need to check for a blank field and return BLANK() if the other options aren’t found. With that (and the other fixes mentioned above) applied, your formula could be this:
IF({CA/FL} = "ca.", "ca.", IF({CA/FL} = "fl.", "fl."))
The next optimization that I would normally mention here is changing from nested IF() functions to the SWITCH() function because all that you’re really doing is switching the output based on what you find in the {CA/FL} field. However, it can be even simpler than that. If all that you want to do is add the selected prefix if one is chosen, then your formula could be this:
IF({CA/FL}, {CA/FL})
By only using a field reference for the first argument, Airtable treats a non-empty field as true, and an empty field as false (as long as it’s not a numeric field). That formula effectively says, “If the field isn’t empty, return its contents; otherwise return nothing.” So if your single select options are “ca.” and “fl.”, it will return the exact text of whichever one is chosen, or nothing if there’s no selection.
FWIW, I’m in the process of producing a course all about Airtable formulas, including lots of tips like those above. It’s a month or two away from completion, but if this walkthrough has helped, then keep an eye out for the announcement of the course going live.
