New function: SWITCH() (and a recap of new summer features)

[Katherine_Duh]

Hi everyone! I know we’re not always as consistent with posting in this forum category as we could be (to say the least), but I figured that this latest new formula was especially worth signal-boosting, given how many people who come to the forums are either formula experts or aspiring formula experts.

Using the new formula:

SWITCH(expression [, pattern, result]... [, default])

you can take an expression, a list of possible values for that expression, and for each one, a value that the expression should take in that case. It can also take a default value if the expression input doesn’t match any of the defined patterns. This is formula is particularly helpful if you often find yourself fumbling through complicated nested IF() statements, since this can replace IF() in situations where you’re comparing a value to a series of explicit values.

Consider the following example, in which you want to return different emoji flags depending on the value in a status field. With a nested IF() formula, it might look like this:

IF(Status="Ideation","💭",IF(Status="Awaiting Review","🤔",IF(Status="Design Phase","🎨",IF(Status="Awaiting Final Approval","💬",IF(Status="Done!","🚀",IF(Status="On Hold","💤",IF(Status="Canceled","💀","")))))))

You could instead construct this in a simpler way using SWITCH(), like so:

SWITCH(Status,"Ideation","💭","Awaiting Review","🤔","Design Phase","🎨","Awaiting Final Approval","💬","Done!","🚀","On Hold","💤","Canceled","💀","")

The results will be the same either way, but with SWITCH(), you won’t be grappling with nearly as many parentheses.

Unrelated to this new formula, we’ve also released a bunch of new features/updates in the last two months, both big and small, that are worth checking out. You can always head on over to our What’s New page, but here are the highlights:

Pro plan features:

• Locked views: this one is kind of a big deal! I’ve posted about this in a couple of forum threads already, but if you missed it, users with creator permissions can now lock views to prevent all collaborators from altering a view’s configuration. Now you can stop other people from messing with your carefully filtered views, or the views you’ve created to power Zapier integrations! If you want to learn about even more uses for locked views, you can read through this blog post on view management in large teams for some inspiration.
• We’ve updated record coloring for gallery and kanban cards. Now, the color flag takes up the entire length of the left side of the card.

Blocks

• Org chart block now supports using attachments as cover fields, putting multiple secondary fields on cards, and record coloring.
• Page designer block now supports sending layout elements forward/backward.
• Contact import block now supports uploading a vCard to update existing records in a table. (Previously, you could only add new records.)
• Map block pins will now cluster instead of stacking on top of one another, making it easier to see the density of pins in a location.

Other

• New account and workspace settings pages: in addition to refreshing the appearance of the account page and of the workspace settings pages, we’ve made it a lot easier to get a high-level overview of all the workspaces you own. From your account page, you can click on any of the workspaces in the sidebar to modify your billing settings or see a list of all billable collaborators for that workspace.
• You can now prefill forms with linked record fields (when you couldn’t before).
• New keyboard shortcut! Use Ctrl/Cmd + Shift + D to open the grouped records menu while in grid view.
• Icons for forms in the view menu are now pink instead of cyan (we were getting a lot of feedback that the cyan was too similar to the grid view’s blue).

[W_Vann_Hall]

Well, ain’t that cool?

Seriously, this is a great addition to the product — especially when using Airtable to write its own formulas. (Currently, every code generating table I create has to include an autonumber field and an explicit ‘END’ statement to calculate the number of closing parentheses required… and, as such, is easily broken by a deleted record or unexpected sort.)

A couple-three questions:

1. When you say 'SWITCH(expression [, pattern, result]... [, default])', what exactly is meant by ‘expression?’ In the examples, ‘expression’ is simply the name of another field in the table. Could it also be a, well, expression, such as a calculation involving two or more fields — à la
   SWITCH({Field1}-{Field2},1,'one',2,'two','something else')
2. Similarly, in the examples, ‘result’ is simply a fixed value. Can it also be a calculated value; that is, could it be a segment of code that gets executed if ‘expression’ equals ‘pattern’?
3. Is there an official (or unofficial) maximum number of pattern/result pairs supported? IF() statements seem to crap out at 350-odd branches; do SWITCH()s support more or fewer clauses?

Thanks for publicizing this — and other — recent changes!

[Katherine_Duh]

Still investigating re: 3. and character/pairing limits, but both expression and result can be arbitrary formulas!

[W_Vann_Hall]

Katherine_Duh:

both expression and result can be arbitrary formulas!

That’s great!

I think I finally have a chance to wale on the new SWITCH() function in an upcoming configurable checklist; if it works (i.e., if it can support 190-some branchings), it should simplify matters greatly in comparison with the IF() statement I’d otherwise have to use.

I’ll report back on anything fun I find out…

[Kim_Prisk]

Re pre-filled forms.
They would be more useful if linked fields could also be pre-filled.
Regards, Kim

[Katherine_Duh]

Re pre-filled forms.
They would be more useful if linked fields could also be pre-filled.
Regards, Kim

You can do this now; that was the update. :slightly_smiling_face:

[Noel_Howell]

HOW exactly do you do this? The prefill form instructions page says nothing about linked fields.

[Katherine_Duh]

You do it exactly the same way that you would do it for other text field types.

Start by adding a ? to the end of the URL. Then, type prefill_ and the name of the field you wish to prefill. The part after prefill_ must exactly match the actual field name as it appears in the table, not the label used in the form. (Note that spaces in the name of the field will need to be converted to %20 s or plus signs ( + ).) Once you’ve typed in the field name, you can then type in an equals sign (=) and the information you wish to prefill. (If there are spaces in the information you wish to prefill, you will need to replace those spaces with %20 s or plus signs ( + ).) If you wish to prefill more fields, enter an ampersand ( & ) and then enter more prefill_ parameters.