Skip to main content

Hi Guys.



Here’s my question, quite simple actually… like the title says, i am trying to get the highest value inserted in a column, I have tried the MAX() function, but it doesn’t give me the highest, it just gives me the same number from the column im using. For example: I have a column named: “number of quotes” and each row has a random number, the highest being 200. in another column named “highest quote number” I wanted to get that number(200) from “number of quotes” and apply to all other rows…since im trying to use a formula in witch one of the params is the highest current number in the “number of quotes” column.



I hope I made myself clear, here’s the formula im trying to use, if it helps:



new_value = ( (old_value - old_min) / (old_max - old_min) ) * (new_max - new_min) + new_min

The spreadsheet metaphor for Airtable is an imperfect one — and I’m going to make it even more imperfect. If you think of a spreadsheet as supporting calculations addressable in two dimensions — row and column — then Airtable supports only one-dimensional calculations: that is, only within a row. (Of course, Airtable offers functionality spreadsheets lack.) There is no way to address directly a cell in a given row of a table in a calculation performed within a cell from a different row.



However, this is a way to do so indirectly. Elsewhere in the forum you’ll find a long post and example bases I wrote explaining how to perform such multi-record or cross-record calculations. Fortunately for you, your application doesn’t require as complex a solution as that post describes — but you will need a few of its components:





  1. First, every record in your table needs to be linked to the single record in a second table. I’ve provided Ipossibly too] detailed instructions on how to do this, but here’s a quick-and-dirty breakdown of the steps:



    1. Create a linked-record field in your main table that links to a table called aCalc]. (Create a new field; select ‘Link to another record’ as the field type; select ‘Create a new table’; name the new table.)


    2. Somewhere — in a text field in the base; in Notepad; from a web page; anywhere — mark and copy a period character (that is, '.', not someone dressed as in a Dickens’ novel).


    3. Left-click on the header for your newly created linked-record field, which will select every cell in the column.


    4. Press Ctrl-V to paste the copied character into each cell of the column. This will create a new record in lCalc] with the {Name} of '.' and link it to every record in your main table.






  2. In Calc], create a rollup field called {MaxQuotes}. It should follow the link from oCalc] to your main table and roll up {Number of Quotes} using the aggregation function MAX(values).


  3. In your main table, create a lookup field. This one should be called {HighestQuoteNumber}; it should follow the link to sCalc] and look up {MaxQuotes}.




That’s it. Keep in mind this will work for all records currently in your main table; any records later added to Main] will need to be linked to the '.' record in {Calc}. This can be done manually by clicking on the plus sign ('+') in the linked record field, by copy-and-pasting a ',' into the new record’s linked-record field, or by clicking on the fill handle (the small '+' in a cell’s lower-right corner) of the previous row in the linked-record column and dragging it into the newly-added record.

Reply


Terms & Conditions