Airtable formulas
Airtable is a low to no code automation app which works like a spreadsheet, but infinitely better. It can sync in real-time. Airtable is also a database. You can use formulas over the fields to create new fields, which could help you understand your business efficiency better.
In this article, we are going to discover how airtable formulas work. These formulas would be easy for you though you don't have any previous experience of working with any of the programming languages.
There are various kinds of formulas in Airtable. A formula can include a numeric expression, operations, or functions. Some of the formulas are text functions, date and time functions, array functions, regex (regular expression) functions, numeric functions, logical functions, and record functions.
Text functions
Text functions are those functions that only work with textual data. Airtable text functions make it easy for you to retrieve collective data from the text field.
Let’s go through some of the text formulas.
CONCATENATE(text1,text2,...)
CONCATENATE() is used to concatenate two or more textual fields together.
For example, Let’s say we have fields called ‘First_name’ and ‘Last_name’.
First_name contains ‘Jane’ and Last_name contains ‘Augsten’.
We can create a new field called ‘Full_name’ with the formula
CONCATENATE({First_name}, {Last_name})
This will give us the “Jane Augsten”.
ARRAYJOIN(array, separator)
It joins the items that are in the array with a separator.
For example, let’s say we have an array called ‘products’ which contains the list of all products Products = (“Soap”,”Brush”,”Shampoo”).
We use the formula ARRAYJOIN(products,”-”). It will give us “Soap-Brush-Shampoo”
FIND(stringtoFind,String)
It will help us in finding the starting position of the mentioned string.
Example - FIND(“you”,”how are you”) will give an output of “9” because ‘you’ begins with the 9th character.
REPLACE(string,ind_start,no_of_char,replacement)
This formula will replace text.
It starts replacing from the ind_start which is the start of the index from where you want to start replacing.
For example, REPLACE(“We made profit”,9,6,”loss”) will output “We made loss”. ‘Profit’ is replaced with ‘loss’.
There are many other text formulas such as LEFT, RIGHT, REPT, MID, LEN, all of which you can find here. You can use these airtable formulas for your business processes. For example, if you want to send an email with the full name instead of only the first name, you can concatenate first and last names to get the full name.
Array functions
Array functions are functions that can only be used with array data.
Here are some of the array functions in airtable.
ARRAYUNIQUE(array)
This returns only unique values.
For example, an array named ‘Category’ = [“A”, “B”, “C”, “D”, “A”, “C”]
ARRAYUNIQUE(Category) will return [“A”, “B”, “C”], i.e only the unique values.
ARRAYCOMPACT(array)
This removes the empty strings and null values from the array.
For example, ‘Sale_Complete’ = [“True”, “False”, “Null” , , “ “]
ARRAYCOMPACT(SALE_COMPLETE) will return [“True”, “False”, “ ”]
Note, the string with blank characters is not removed.
ARRAYFLATTEN(array)
This function removes any nesting from the array.
For example,
Array = [ “I” , “You” ,[“Me”, “Her”], [“She”] ]
ARRAYFLATTEN(Array), we return [“I”, “You”, “Me”, “Her”, “She”]
ARRAYJOIN(array, separator)
This function joins the array into a single string with a separator.
Array = [2,4,5,6,7,8]
ARRAYJOIN(Array, “-”) will give us “2-3-4-5-6-7-8”
Logical and Numeric functions
Calculations in airtable can be easily done using numeric and logical functions.
AVERAGE(num1,num2,...)
This returns the average of the numeric field you mentioned.
AVERAGE(1,2,3) -> 2
SUM(num1,num2,...)
This returns the sum of the numeric fields mentioned.
SUM(1000,2000) -> 3000
IF(logical, 1stvalue, 2ndvalue)
It is a conditional function that will return the 1st value if the mentioned field is true or it will return the 2nd value.
For example - If({Participants}>10, “Full”, “Not full”)
If Participants = 9, the above formula will return “Not full’.
If Participants = 11, the above formula will return “Full”.
SWITCH()
This function is useful for many cases because it will give output according to status.
For example - if we want to know if an employee is hired, being interview, or fired, we can set a status for each. Being Interviewed =1, Hired =2, Fired =3 .
Note: The first field in SWITCH is the STATUS.
SWITCH(2, 1, “Interviewee”, 2, “Hired”, 3, ”Fired”) will output “Hired” because status is 2.
Date & Time functions
Airtable date formulas and time formulas form an important part of Airtable. A business runs on money, human resources, and timelines. No business can function without a timeline. How do you keep a tab of important dates? Using airtable date formulas.
Here are some Date & Time functions available on Airtable
CREATED_TIME()
This return the time and date a particular record was created
CREATED_TIME() -> 2021-09-08T14:00:00
WORKDAY_DIFF(date_start,date_end,[holidays])
This returns the number of workdays between the 2 days mentioned. It excludes all weekends. You can also include holidays as a list of holidays formatted as comma-separated values.
WORKDAY_DIFF(‘09/08/2021’,’09/21/2021’,’09/10/2021, 09/11/2021’) will output 9 as there are 9 workdays between 8th Sept 2021 and 21st Sept 2021, excluding the weekends (Sat-Sun) and the holidays mentioned.
DATETIME_DIFF(date1,date2,units)
This returns the difference between two date times in the units mentioned.
DATETIME_DIFF(“09/10/2021 15:00”, “09/08/2021 14:00”,”hours”) -> 49
DAY(date)
This returns the day of the month from the date.
DAY(‘09/11/2021’) -> 11
MONTH(date)
This returns the month from the date.
MONTH(‘09/11/2021’) -> 09
YEAR(date)
This returns the year from the date.
YEAR(‘09/11/2021’) -> 2021
The rest of the formulas can be found here.
Please fill in the below form to get free access for the Airtable base covering all formulas. Fill in the form with details of any complex formulas, for which you need any assistance.
How can KwikSync help?
Airtable formulas can help you keep a closer tab on your business processes without extra work. We at KwikSync consulting can help you set up Airtable for your business.
Check out our success stories here.
Contact us today at vijayd@kwiksync.org