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.
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
Check out Ragic.com… it is not as clean as airtable, but if you are looking for something that is still quick to set up, AND allows the implementation of more powerful formulas and forms … all for about the same price as airtable, then Ragic.com is what you need. Airtable is great for what it does… but dont try to use a hammer when you need a mallet. Choose the right tool for the right job. I’m tired of fighting airtable to get these features. Airtable is great…just going in another direction.
Hi everyone,
I am pretty new to Airtable so in the midst of generating a few bases. What I have noticed so far and can see that people have been requesting for it for a while is COUNTIF, COUNTIFS, SUMIF and SUMIFS.
Linked to the above formula and applicable to current SUM and COUNT is a function that I use in Power BI called ALL. ALL forumla basically take any filter out of the equation and is really useful for taking the grandtotal to calculate any ratio or present it on its ok.
For example, the sum and rollup can show a told and when we filter the information or group them, we can see the total for that group alone. However, the only place to see the grandtotal is at the bottom. Moreover, the grandtotal that appears at the bottom of the base cannot be refered to in any formula. Therefore, adding the ALL function will transform the capability of Airtable in terms of creating calculated fields.
Happy to provider further clarification if not already clear.
In short - create a field in the table being rolled up that reproduces the value in question, (say, an amount), but only if some condition is met. Otherwise, the field is blank. Then in your summary table that is rolling up the records, do a SUM(values), AVG(values), etc. rollup on that conditional field.
In support of the lookup function, I think a huge value in this formula, would be to enable iterations. There are some extremely elaborate base structures just to overcome the fact that Airtable currently does not support any type of iterative formula.
A simple example: I have two tables
Projects Table
Tasks Table
I assign records in the Tasks table to a project, lets say 3-50 tasks per project. Then each task is linked to the next and previous task (a pain in itself currently, but lets not worry about that here). I simply want to sort by task number in the sequence. This sounds easy, but currently isn’t!
If I could have a formula field that simply looked up the same field on a linked record and added 1, I would be golden! Unfortunately, instead I had to create 50 lookup fields to achieve an illusion of this capability limited to a maximum of 50 records.
Of course a circular reference might occur if someone linked the record to itself on accident, but if that could just throw an “error” I would be very happy :slightly_smiling_face:
I think one of the key missing features is the ability for us to use Airtable’s own internal summary functions within our own formulas!
Airtable cosmetically displays summary fields in both grouping headers and view footers, but we have absolutely no way to grab those values and use them in our own formulas.
Airtable only gives us access to summary functions in Rollup Fields and Count Fields, which require us to link to another table.
But we have no access to summary functions for the records in a single table.
In essence, Airtable’s summary functions have no concept of other records within the same table, and they have no concept of groupings within the same table.
Cosmetically speaking, Airtable displays the values that we need onscreen, but we can’t use those values in formulas.
We really need access to these onscreen summary functions in our formulas.
And, to be clear, there are 2 different types of summary functions here:
Groupings
Entire view
This would be a killer feature which would save us tons & tons of headaches, because currently we have absolutely no way of creating formulas that can summarize data within a single table. We are always required to link to other tables to get summary functions.
Agreed! I was just banging my head on this wall today. I want to summarize data in a table without having to link every record from that table to another table! That is not an option for a large set of records. The group summaries are great, but not accessible.
What would be great is if functions defined in Scripting Blocks are allowed to be used in Formula fields.
That way, if there is a function that is needed but missing, we have the option to write a new function that we can use.
Found this thread when I was trying to find an array difference formula, where array 1 and array 2 are compared and then the difference is returned within array 3. Looks like I’ll need to automate the solution. :frowning:
Thinking about the Summary shortfall you’ve mentioned, this too has annoyed me over the years. It would be great to see either Summary functions added to the Formula Field, or perhaps, even a designated new type of Field called “Summary” that returns specific summary data (albeit, duplicated for the cells within each group, perhaps it could have an array [“Count”, “Value”] ).
