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
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.
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
I just got done writing a concatenation formula to create every date between a User entered {Start Date} and {End Date}, with support for up to 1 year (365 days) difference between start and endโฆ :confounded:
fellow forum users, feel free to steal this and reuse if it can help youโฆ
The use case here is a booking system that needs to allow for future reservations of a particular site to be checked against all dates for which that site has existing reservations, to avoid double booking.
I would have killed to have had either one of:
a function for checking a date against a span of time, given the start and end of the span; perhaps something like:
DATE_IS_IN(dateToCheck, startOfSpan, endOfSpan)
an ITERATOR and VARIABLES of any sort to be able to automate the formula I had to write by hand
Thank you for considering! Blessings :grinning_face_with_smiling_eyes:
Takes a number as either integer, decimal, or field reference, and a currency symbol ('$', 'โฌ', 'ยฃ', etcโฆ); returns the number rounded and formatted as currency
I would love some sort of DATE_RANGE({Start Date},{End Date},'unit') function to list all the days, months, years, etc. between two given dates. I have a Vacation Planner base where Iโm planning several week-long events across a year, and a function like this would come in handy.
Is there no way to automatically get the value of a linked records field automatically now? An API populates my table at the moment from orders on my online store and I need to look up shipping costs per shipping method on a linked table. Right now I have to manually copy the shipping method value to the linked record field to get the lookup to work. Itโs a pain.
I havenโt yet played with the API, but using Integromat (which uses the API under the hood) to edit Airtable data, I can pass text into a linked record field and it will make the link if it finds a record with a matching name in the primary field. Not sure how much extra work it would take directly with the API to do something similar, but perhaps @Bill.French can offer some insight on this.