ALL() formula

[Stephen_Orr1]

Airtable's power really shines when structuring a base in a relational way. In my experience working with many new Airtable users, this is not always immediately obvious and takes education (especially for users coming from Excel). Instead of requiring some mindset shifts and a (small) learning curve to get the most out of Airtable, it would be helpful to have some quick and powerful options when working with flat data in a single table, such as aggregations on all records in a view.

What is the proposed idea/solution?

A formula that returns a comma separated list of all values across records in a view for a given field: ALL({some field})

Some name suggestions:
ALL()
ALLVALUES()
LIST()
CSV()

How does is solve the user problems?

Allows creation of "share of..." calculations like:
{sales} / SUM(ALL({sales}))

or using record counts in the same table like:

COUNT(ALL({Project Name}))

or get a distinct list of field values existing across all other sibling records like:

ARRAYUNIQUE(ALL({Part List}))

How was this validated?

n/a

Who is the target audience?

Everyone, business users coming from Excel that aren't yet thinking about their data in relational terms

[Bill_French]

Love this feature request. Here's a nutty take on it.

[kuovonne]

Out of curiosity, how do you see this fitting in with Airtable's current design for how formulas work? Currently formulas only reference values in the current record and a single value in linked tables.

If I am understanding you correctly, it looks like you want something similar to a rollup field with a control record that is linked to all records in a view.

Are you asking for a totally different place to put a formula that is not a field for a particular record? Since records themselves have no knowledge of views and a record could exist in multiple views, current formula fields don't seem the right place to add this feature.

[Bill_French]

I just typed out a big response to your question, and got this - I give up.

[Stephen_Orr1]

Great writeup, @Bill_French! I'll trade you my suggestion for yours. ai() in a formula sounds cool and definitely would need to be able to access and aggregate all surrounding records in the same table/view.

@kuovonne, you make a really great point. Should records know details about the view they are in? This would mean values potentially changing via view filtering which I think would lead to some unintended behavior and possible user confusion. Perhaps it would be better to update on group by and ignore filters but I'm still thinking this through.

In Excel, SUM() ignores filters but SUBTOTAL() considers them. What I'm thinking of is closer to SUBTOTAL(), just without the aggregation always included.

I agree that this breaks how functions currently work and could also see this being a new field that behaves similarly to SUBTOTAL() in excel, like "Window" or "Context" maybe. It might look strange all alone as the same value for every record, but useful when used in other formulas.

Open to any thoughts 🙂
-Stephen

[Stephen_Orr1]

@Bill_French, I would love to hear your missing thoughts if not too much trouble at this point

[Bill_French]

Reluctantly I have posted a response (I fear Khoros now) ...

>>> ... looking for a totally different place to put a formula that is not a field for a particular record?

Airtable's inability to adopt many of the common spreadsheet formula patterns is directly related to an issue I have raised repeatedly - a cell in Airtable cannot contain a formula that references a collection of rows. This is a significant architectural departure from what most people regard as a basic spreadsheet tenet since the advent of VisiCalc in 1981.

Ergo... you cannot create a table of rows that formulate a collection of analytics and then populate the analytics with independent formulas that perform aggregations on one or more tables and rows within those tables. The support questions in the forum(s) over a decade have shown that this is what transitional users expect to find. And yet, Airtable is apparently comfortable with saying, "This is not a spreadsheet - it's a database". But the corollary is often used as well when you hit some harsh database ceilings. 😉

Roll-ups are the only out (besides scripting and extensions) to create dynamic data aggregations, but the roll-up approach is extremely limiting and rigid. Stephen is simply saying - the world has a preconceived notion of how to conditionally aggregate data and Airtable should embrace that notion.

[Stephen_Orr1]

@Bill_French well said and thanks for sharing!

Like Bill mentioned, the closest Airtable approach I've found is to use a roll up without an aggregation formula and then a lookup to bring this over. It requires a specific relational setup and deeper Airtable knowledge. It's not dynamic like SUBTOTAL() is in Excel (but it can be conditioned via the roll up field) nor provides a good user experience when months later folks forget to link every new record and risk bad insights.

A spreadsheet user would say this is totally unnecessary, a database user would say this is annoying without SQL but I at least get why, and an Airtable user deserves the best UX of both worlds! (and a solid argument when convincing enterprise Excel folks to try Airtable! 😉) Come on Airtable, help us proselytize the masses!