New Formula Field Functions

[Jeremy_Oglesby]

There are several requests floating around the forums related to the need/desire for more formula field functions - mostly related to arrays, linked records, or javascript like functionality.

I’m going to create this #feature-requests post with some of the initial ones that I have posted myself, or have seen in the forums, and I will endeavor to keep it updated with new ones that I find or see posted; and, I will even endeavor to cross them off the list as Airtable implements them :winking_face:

To keep the format clean, I’m going to make a section that lists just the function requested and a brief description of functionality - I think the intention for use and the use-cases should be clear by the structure of the function. This will be followed by a section with links to some of the posts I have seen addressing the need/desire for some of these functions.

Airtable users - If you have an idea for a function that is not currently in the list, post it as a reply here and I will try to add it to the list in this OP if the intention is clear and the functionality seems feasible.

@Airtable_Support, @Airtable_Team, @Katherine_Duh, @Howie, @Kasra - please consider :grinning_face_with_big_eyes:

Functions List

LOOKUP(table, field, linked-records-field)

• Lookup field functionality, but can be evaluated within a formula field

ROLLUP(table, field, ROLLUP-FUNCTION())

• Rollup field functionality, but can be evaluated within a formula field

ARRAY_DIFF(array 1, array 2)

• Takes two arrays (or strings of objects with a separator that can easily be converted to an array) - returns {array 3} which contains all objects from {array 1} that do not also exist in {array 2} (ie, subtract {array 2} from {array 1})

ARRAY_EACH(array, variable, FUNCTION())

• Takes an array and a compatible Function - loops over the array and for each object, saves the object in the variable, and allows the user to use the variable in the chosen, compatible, string function (such as SUBSTITUTE(), or FIND()) or in logical Functions (such as IF(), OR(), AND()); works like a JS for loop

ARRAY_INTERSECT(array 1, array 2, array 3, ...)

• Takes any number of arrays (or strings of objects with a separator that can easily be converted to an array) - returns an array with only objects that exist in every array provided to the function

Links List

Formula Fields: Values Based On Javascript Functions Product Suggestions

This one might be a little bit “out there,” but I think that if it were possible it might address a number of requests for additional functions supported by Airtable’s Formula Fields… I’m wondering if it might be possible support Formula Fields whose values are based on the result of some ad-hoc Javascript function. For example, the user might specify other Airtable fields as the Javascript function’s params, specify the JS function itself, and have the Formula Field’s value reflect the result. …

Array intersect formula Product Suggestions

I have 2 tables, and I need a result that shows only inputs that are the same in both tables. Hence, to compute the intersection of a number of arrays. array intersect ( array1, array2 [, array#… ] ) Returns an array containing all the values of array1 that are present in all the arguments. Where: array1 The array with master values to check. array2 An array to compare values against. … A variable list of arrays to compare. Any workarounds at this point?

Comparing two arrays and remove values that appear in both Product Suggestions

Hi airtable! I have a list (an array) of items in two columns. Each row represents two different lists (one in each column). I’d like to be able to compare the two columns and remove what is common in both. For example Row 1: Column 1: Apple, Orange, Pineapple, Kiwi Column 2: Apple, Kiwi Result: Column 3: Orange, Pineapple Row 2: Column 1: Banana, Orange, Grape, Kiwi, Pineapple, Passion Fruit Column 2: Orange, Grape, Pineapple Result: Column 3: Banana, Kiwi, Passion Fruit etc. The …

Look-Up from a Look-Up Field Product Suggestions

A very useful feature to have would be the ability to “drill-down” another level when working with Look-Up fields. We use AirTable to manage our company E-mail accounts and Distribution Lists (Distribution Lists are email addresses that contain members and distribute messages to all who are assigned to it, i.e. info@company.com can go to John, Mary, and Paul). Here is the basic set-up between 3 tables: Table PEOPLE contains a list of all employees, and links to EMAIL which contains all email a…

How can I make a lookup field show only unique results? Ask the community

The result I am looking for is similar to the ARRAYUNIQUE function for a Rollup field. However, the results are a link to another table, and it seems that using a rollup can only show text results as opposed to clickable records. How can I make lookup field show only unique results, or make the unique results of a rollup field clickable?

Dynamic (calculated) link to a record in a linked table Ask the community

How to create a dynamic (calculated) link to another table? There is a Table#1 (Daily Expenses) [ID, Date, Sum, subject,MonthNo]. Where MonthNo=MONTH({Date}) There is a Table#2 (Months of the year) just as follows: MonthNo MonthName 1 January 2 February 3 March etc. Linking Table#1 to Table#2 will allow me to calculate monthly expenses directly in a Table#2 by means of Lookup(Sum) function. How can I do this? If I choose a standard Link field then every time later I will have to select…

COUNTIF AND COUNTIFS Formulas Product Suggestions

It would be great if the COUNTIF and the COUNTIFS formulas were implemented in Airtable. I use them in most of my excel spreadsheets, and it is a little frustrating not being able to use it here. Are there any plans to add more formulas?

Support lookups and references/links within formulas Product Suggestions

First off, Airtable is great! One thing though: I’d really like to not have to create additional columns in order to simply do something like the following: Formula: if(lookup(row_10,"column_1"),lookup(row_12,"column_4"),lookup(row_53, "column_2")) Also, I have a lot of varied data and I’m trying to use one big table for everything with lots of different views rather than use lots of different tables with aggregate tables and views based on those smaller tables. I can link to a row within the…

[Alexey_Gusev]

You can use this way for your goal. Topic is here, but I don’t think it has more helpful info to add to my quote and picture.

[Jeremy_Oglesby]

Unfortunately, it’s been 4 years now and none of this has been added. While it’s true that scripting automations (which were implemented after this feature request was made) can basically address all the same use-cases, that is much less approachable to many people than a formula field, and a lot less convenient in a lot of cases.

[Karlstens]

The power of Formula fields is also from API responses. Through my initial use of them, I noticed that data sent to Airtable via an API call then returned the calculated field in that same operation - Although I’m yet to leverage this, it’s one thing to keep in mind when deciding whether data needs to be calculated in a formula field or returned by a scripting Automation.