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

[Thierry_Gattlen]

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.

[Julian_Kirkness]

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.

[W_Vann_Hall]

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.

[Thierry_Gattlen]

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

[Julian_Kirkness]

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:

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

[Sid_Wray]

Thank you for sharing this! I’m trying to accomplish the same thing. The only problem, is that I’m trying to summarize the min, max over 500+ records. In your example, it looks like you create a “Totals” record by linking to every record in the “Project” table. How do you do that when you’re linking across a bunch of records?

Here is my example:
I have a table with 500+ items in rows, and through an external process we have a count of support cases for each row (item). I’m trying to create a normalized score between zero and 1 for each record (support_cases/Max(support_cases).

What is the best way to do the linking in this case?

Thanks!

[Sid_Wray]

Nevermind! I was able to make the first link in my summary table which had one row, called “score” and added one linked record from the main “items” table. This link then showed up in the primary “items” table as a new link column, and I was then able to grab the lower corner and drag it down across all 500 rows. I’m not sure on the perf hit for this kind of situation, but it worked for now!

[W_Vann_Hall]

There are a number of ways to achieve such a many-to-one link. In addition to using the fill handles, as you discovered, you could also

1. Manually link the first record, select the linked-record cell, press Ctrl-C to copy the value, left-click on the column header (that is, the top-most cell containing the field name), which selects the entire column, and press Ctrl-V to paste the copied value into every cell in the column.
2. Go to the table containing the record to which you wish to link; select the record’s primary field; press Ctrl-C to copy the value; return to the table from whose records you wish to link; left-click on the column header, selecting the entire column; and press Ctrl-V to paste the copied value into every cell in the column.
3. It’s also possible to define a text field in the table from which you wish to link, use one of the preceding techniques to fill all cells in that column with the same value, and convert the field to a linked-record field. This both creates the appropriately named record in the target table and links all of the originating records to it. More detail can be found here on this last method.