Find last entry

nathaniel_pulsi · ‎May 03, 2018

Hi-
I am moving an application from Fieldbook to Airtable, FB has lovely formulas that allow the user to look thru a link to a table and perform any formula on that table, similar to rollups but with more functionality. Recreating my systems in airtable, i am stuck on one i can’t work out.

i have a table of entries called 'table2 and want to find ‘price’ in the last entry. In fieldbook it’s simple:
last(table2.price)

I am not finding any equivalent in Airtable. there are various other data points in the row on table2, including a sequential integer field called ‘ownership Number’ where the ‘last’ would also be the ‘max’ however i am unable to get a formula to work in a rollup that says

'lookup ‘price’ on ‘table 2’ for the row with the max ‘ownership number’

Any suggestions?

W_Vann_Hall · ‎Jul 31, 2019

The easiest way to see what I’ve done is to load that base and click ‘Copy base’ in the upper right-hand corner. That will create a duplicate of the base in your own workspace. When you open the duplicated base, you can then right-click on each field and edit its configuration.

----but that base in particular was written a while back, before I became lazy :winking_face: , so each field has a defined description. If you hover over the ⓘ in the header for {latestNoteDate} in the [Companies] table, you’ll see the following description:

(Note: The ‘Note’ no longer applies. Presumably there was a change/fix/modification to Airtable — let me go check the release notes :winking_face: — that corrected this behavior. When you copy the base, it will continue not to display a time field – but if you disable the time field in the formatting tab and then re-enable it, the time field magically appears.)

That description is my way of saying the field should be configured as follows:

In the [Notes] table, {latestNoteDate} is simply a lookup field that retrieves the value of the field of the same name from [Companies]. It’s used by the formula in `{Notes::latestNote}’ to identify which note is the most recent.

See Solution in Thread

Alex_Wolfe · ‎May 03, 2018

Here’s my two cents:

First, if your definition of ‘last’ is the record with a maximum value of an automatically generated number, then you can make this work but it requires a work around.

You can use an Auto Number field in Airtable which will create a column/field with an autogenerated number for each record starting at the number 1.
Now to get the maximum. Are all records in ‘table2’ linked to another table somehow? If not, you may need to create a new table, for example ‘Calculations’, which you have a single record in. Then, link all ‘table2’ records to this single record in ‘Calculations’. Now you can use a Rollup field in ‘Calculations’ to get the Max(Values) for the Auto Number field in ‘table2’.
Then, in ‘table2’ create a Lookup field which references your ‘Calculations’ table Rollup (the maximum auto number).
Create a formula in ‘table2’ which checks if the record’s auto number equals the Lookup field (step 3 above), and if so, displays the record’s price.
Now, you can create another Lookup field in your desired table (linked to ‘table2’ records) which looks up the single value displayed in your step 4 column/field (the maximum, or in this case ‘last’, record’s price).

Long winded but basically, you are moving the data back and forth across 2-3 tables to filter/calculate the values you need.

Good luck!

nathaniel_pulsi · ‎May 03, 2018

Thanks Alex- that is basically the way i was thinking of doing if but was getting stuck. I think i need to use 2 helper cols to do this, as you say, one to accomplish the max value, and another to display the desired result, then do the lookup. Will reply back if i’m successful

nathaniel_pulsi · ‎May 04, 2018

I am unable to accomplish what seems like a simple request for a database. I’ve put a sample book here and would appreciate any assistance that can be offered. this is quite simple in excel and in other applications like fieldbook, but baffling me in airtable.

Basically, i want a vlookup to find a value contained on a related table where another value on the related table is the maximum. The scenario is quite simple- inventory may be owned by many investors over time, each delineated by an increasing integer (eg, owner 1, owner 2, owner 3, etc), and i want to look up the last (or max ownership number) investor’s price.

the book is here:

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

W_Vann_Hall · ‎May 05, 2018

Here are a few sample bases that use the same mechanism that @Alex_Wolfe described. You should be able to life code from them relatively easily.

Jeri_Vespoli · ‎Feb 08, 2019

Oh for heaven’s sake - this should be as easy as arraylast(value)! Shouldn’t have to jump through hoops and create multiple calculated fields to extract a single linked value from a database. Airtable Tech???

W_Vann_Hall · ‎Feb 08, 2019

Yep – array functions are high on my list of most-desired features…

Emma_W · ‎Jul 22, 2019

In the “Latest Note Demo” base (https://airtable.com/shrC0EP2qexnGNqMN/tbllOIbDj5gH1yfCL/viwPGxCYBzB0qR0O3?blocks=hide), could you share how you did the field “latestNoteDate” for each separate company?

W_Vann_Hall · ‎Jul 31, 2019

The easiest way to see what I’ve done is to load that base and click ‘Copy base’ in the upper right-hand corner. That will create a duplicate of the base in your own workspace. When you open the duplicated base, you can then right-click on each field and edit its configuration.

----but that base in particular was written a while back, before I became lazy :winking_face: , so each field has a defined description. If you hover over the ⓘ in the header for {latestNoteDate} in the [Companies] table, you’ll see the following description:

(Note: The ‘Note’ no longer applies. Presumably there was a change/fix/modification to Airtable — let me go check the release notes :winking_face: — that corrected this behavior. When you copy the base, it will continue not to display a time field – but if you disable the time field in the formatting tab and then re-enable it, the time field magically appears.)

That description is my way of saying the field should be configured as follows:

In the [Notes] table, {latestNoteDate} is simply a lookup field that retrieves the value of the field of the same name from [Companies]. It’s used by the formula in `{Notes::latestNote}’ to identify which note is the most recent.

Calvin_Young · ‎Oct 22, 2019

The lack of basic functionality is more than a mild inconvenience at this point. I’ve only been using AirTable for a couple of months and am continuing to stumble upon these limitations. Should I expect them to rollout any new features in this arena, or are we stuck with what we’ve got?