All About That Base

Helping you understand Airtable more deeply. Newsletter ➡️ • •

[All About That Base @AllAboutThtBase]

@N_Loia Hi Nelson! Sorry for the late reply. I almost never visit X any more. I just posted a reply in the forum. Let me know if it'll work for your use case!

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Want the first or last item in a rollup array? If your items don't contain commas, REGEX makes it easy. For the first:

IF(values, REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*"))

To get the last:

IF(values, REGEX_EXTRACT(ARRAYJOIN(values), "[^,]*$"))

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Take this title:

A Tale of Two Cities

...and change it using REGEX_REPLACE() for more useful sorting:

Tale of Two Cities, A

Kamille Parks shows how in this thread:
https://t.co/m0bLewBYZv

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Did you know that the LEFT() and RIGHT() functions have a default length of 1? For example, LEFT(Name) does the same as LEFT(Name, 1)!

Get the first initial + last name (e.g. Bob Smith => BSmith):

LEFT(Name) & REGEX_EXTRACT(Name, "(?:.* )(.*)"))

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Spaces are your friend! If you're inserting something from the autocomplete list, and there's no space between your cursor and the existing formula text, the chunk after the cursor might be replaced. Add a space first to prevent this!

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Airtable's formula editor checks for errors! If you see a red underline in your formula, that means something is wrong. Hover over that red line to see an error message. No more guesswork about formula errors!

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ The formula field editor supports multi-line formulas! To add a new line, hit the Enter key. Some indentation is added automatically, but you can indent more with the Tab key (if the autocomplete list is visible, hit Esc to dismiss it first.)

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Do you need to create custom labels for things based on their age? Here's a video with some tips on how to build such a formula: https://t.co/gAQp4wAgah

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Are you confused about how to use the AND() and OR() formula functions? They're actually easier to understand than you might think. Here's a look at how they operate: https://t.co/JcPhrxaTZZ

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ The AVERAGE() function is more optimized than you might think. Learn more about it in this video:
https://t.co/arvHvFzxLB

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Simplify searches with regex! The vertical bar token acts like "or". Place it between options to see if any exist.

IF(OR(State = "WA", State = "OR", State = "ID"), "Region 1", ... )

vs

IF(REGEX_MATCH(State, "WA|OR|ID"), "Region 1", ... )

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Using DATEADD to find a new date, and the offset is based on other data? Condense! Nest the criteria *inside* the DATEADD function:

DATEADD(
{Source Date},
SWITCH(
Option,
“A”, 9,
“B”, 18
“C”, 27
),
“days”
)

#TheMoreYouKnow

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ What is a practical example of using the T function?

After years of deep formula spelunking, I'm still not sure. 🤣

Has T saved the day for you? Let me know and I'll spread the word like it's a holiday! https://t.co/6ouVJ3skUZ

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Why write CONCATENATE when you can use the & operator? Before:

CONCATENATE({First Name}, " ", {Last Name})

After:

{First Name} & " " & {Last Name}

Same result. Less typing. #TheMoreYouKnow

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ The SWITCH function is a *much* easier way to set specific output based on options from a single input. Go from this:

IF(Option="A", "First", IF(Option="B", "Second"))

...to this...

SWITCH(Option, "A", "First", "B", "Second")

#TheMoreYouKnow

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Did you know that you're *not* limited to using just a handful of suggested functions when building aggregation formulas for rollup fields? It's more work without autocomplete to help, but you can build FULL formulas! Give it a try! 👍

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Do you know why lookup fields don't always work easily in formulas? They often return *arrays* of items, even when only pulling from a single linked record. That's why I made this base to break it down: https://t.co/RR2WtV5Zmv

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Do you know the shortcut for testing for an empty field? The field name itself:

IF(Field, "Filled", "Empty")

Fields returning numbers are the only exception, where 0 interprets as False. In that case, use this:

IF(Field & "", "Filled", "Empty")

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Did you know that the third argument in an IF() function is optional? Omit it, and the function returns nothing.

Why not just put an empty string? Because that also force-converts the other output into a string. #TheMoreYouKnow

[All About That Base @AllAboutThtBase]

#Airtable #FormulaFriday! ➡️ Avoid making a formula field empty with an empty string. It can sometimes have unintended side-effects. This video shows more:

https://t.co/bD7WY94u56