How to get max value of a set of records (in Airtable or via API)


#1

Hello,
I’m struggling with a “simple” project. I would like to use Airtable to suggest number for customers proposal.
I have a table with an autonumbering field and would like to display in a field in a form the next number available for a proposal (max from auto number +1).
To do that I have to know the max number of that field within the complete set of records.
I have not been able to use the max() function because it is only working on a single record.
My question: how to get the maximum value of a field in a set of records.
Any idea ?
Thanks for your help.


#2

I sometimes use the technique of linking all the records in a table to a single record in another - then you can use Max in a roll up field to get the largest value - and if you need this in a form you could build a URL in a formula to repopulate a field.


#3

As @Julian_Kirkness says, the easiest way is to link all records in your main table to a single record in a [Calc] table and then rollup the value from the main table using MAX(values) as an aggregation function.

Adding that link to an existing table is exceedingly easy, and maintaining it on an ongoing basis requires two mouseclicks extra for each new [Main] record or a two-step (re: free) Zapier Zap.

I’ve documented this technique in a number of recent posts; for instance, Steps 1 through 5 of this reply provides detailed, step-by-step instructions on creating just such an every-to-one link from an existing table. In addition, Step 9 defines a MAX(values) rollup as Julian describes.


#4

Thanks for your help.
I will try your solution. I have had confirmation from Airtable that this fonctions are only available in the summary bar. I hope they will develop this soon.
Best Regards
Thierry


#5

Hi @Thierry_Gattlen

It’s actually quite straightforward to have a summary table which gets the max, min, average, etc values from all records in another table - I’ve done this many times. It gives you the opportunity to create a table with many summaries from child tables and do further analysis on the results. All you need to do is connect your records to the summary record in the summary table and use rollup, lookup and formula fields to perform the magic. You can even get the value back from the summary table to the main table to do additional logic…

Here’s an example of a table where all records are linked to a single record in a Summary table. You can see that I’ve retrieved the max value back from the summary with a lookup field and used a formula to indicate the record(s) which are at the max value (and also used record colouring to highlight this further. Here’s the summary table:

The Max Value field (and the others) are simply roll ups:

And the Max Value field on Projects is just a lookup:

23

This simple technique enables you to achieve quite a lot - and of course to generalise it to where there are many categories of information over which you summarise is also simple (just more than 1 record in the ‘summary’ table.

I hope this helps?

Julian