New Formula Field Functions


#1

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 :wink:

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 :smiley:

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









Comparing two arrays and remove values that appear in both
Support lookups and references/links within formulas
COUNTIF AND COUNTIFS Formulas
PRODUCT rollup aggregation function
#2

Remember you can essentially write a formula within the rollup aggregation function window; simply use the keyword 'values' to reference the rolled-up field. The one relatively constraining limitation is that you can’t address directly more than one rolled-up value, which presumably your suggested enhancement could.


#3

Exactly - the ability to reference multiple Rollups within a formula would be nice, but also, I think that to make it compatible with the ARRAY_EACH() function being proposed, you’d have to be able to address the Rollup within the ARRAY_EACH() function itself - I’m not sure it would work if you just referenced a distinct Rollup field. Maybe I’m not thinking properly about that.

Also, I’ve always had trouble with writing formulas in Rollup fields - perhaps I haven’t been thinking about using 'values' in the right way


#4

I’ve lost the ability to edit this post, sadly.
(explanation here)

Until (hopefully) I can edit it again, here’s another addition to the list:

Function

Formula Field Version

PROD(value 1, value 2, value 3, etc...)

Rollup Field Version

PROD(values)
  • Takes any number of numerical values and multiplies them together to obtain the product of those values

Reference Link