How to get the latest CPI data located in a table to use as a formula in another table

[iaskenazi]

Trying to do an airtable base for tracking lease contracts information. 

On one table I have the contracts data: Client, date of lease, term, area, price etc.

On another table I have inflation data. (Either CPI or INPC), Monthly data.

What I would like to do is to get the latest row on inflation table (let's say April 2023) and divide that value by the cpi data of the inicial term date. (I tried linking the contract table with the inflation table and instead of having the date of contract I would select the correct date from the inflation data).

I did get the original cpi data with a simple lookup but I don't know how to get the latest row data so I can divide and get accumulated inflation since the lease was signed.

[ScottWorld]

Unfortunately, there is no built-in way in Airtable to get the latest data from another table. You may want to submit this as a feature request to support@airtable.com.

In the meantime, you would need to use the workaround that I describe in this episode of the BuiltOnAir podcast:

https://www.youtube.com/watch?v=T9RWQndgKoQ

[iaskenazi]

Thank you Scott. I saw the video and for me it's more complicated than I thought. 

Can you explain how can I define a column in this same inflation table where I divide each row by its latest row. I know it's kind of the same question but isn't it possible to use the summary row data in the formula?. 

best 

Isaac 

[ScottWorld]

Unfortunately, Airtable doesn’t give us programmatic access to the summary bar data.

And Airtable can only access data from other rows or tables through linked record fields, which must be manually linked by you.

In your case, using a spreadsheet app like Excel or Google Sheets would be more powerful & easier than using Airtable.

[iaskenazi]

My problem is that in Excel I would not be able to have all the powerful features of grouping, filtering and having all the related information. 

Maybe I can try to do an automation to get just the inflation table with the results I need from Google Sheets. 

Or try to do the longer turnaround method you described in the video. But in the video you are doing it with 2 tables and I just don't know what to do. Would it be possible for you just to describe the way I can do this extra table and then roll up the results I need? 

I think this is a major request that should be addressed by Airtable because it's very powerful but doing something like this seems to be very useful to many applications. 

[iaskenazi]

I am thinking that if this is not possible since anyway I need to monthly update the inflation data I can have the calculation made in excel and jut copy all the calculated column with the acumulated inflation up to date and copy all the data to Airtable. Not the ideal thing but it works.