Find last entry


#1

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?


#2

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.

  1. 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.
  2. 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’.
  3. Then, in ‘table2’ create a Lookup field which references your ‘Calculations’ table Rollup (the maximum auto number).
  4. 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.
  5. 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!


#3

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


#4

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:


#5

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.


Car Maintenance database