I have an asset that is linked to an owner with a % holding and to many valuations. I want to view the data for a selected owner, with a current value of the asset calculated from that owner’s % multiplied by the LATEST valuation - ie from the valuation record with the latest date. Filtering is easy enough and rollup field looks promising but there seems no way to use the latest valuation record in the calculation. I need to select the person and then print or export the results that values their entire estate by combining latest asset valuation with % owned. Is a custom app needed (that feels unlikely given what I want to achieve would be trivial in SQL)? interfaces don’t seem to do it and views neither as far as I have been able to determine so far. It seems like something that would be used frequently but I’m in my first few hours with AirTable so a pointer to a help article would be great, thanks in advance!
I’m assuming that your “owners” table is linked to the “valuations” table, so each owner can be linked to many different valuations.
First, you’ll need to go into the valuations table, and you will need to create some sort of a date field so you can determine which valuation is the latest valuation.
Then, go back into your owners table and create a Rollup field which points to that date field in your valuations table. Use the rollup formula MAX(values), which will give you the latest valuation date that is associated with that particular owner.
Next, go back into your valuations table again, where you will now need to create a lookup field that looks up that brand new rollup field that you just created in the owners table. So now, for every record in your valuations table, you will see the latest valuation date for its linked owner.
Then, while you are still in the valuations table, you will need to create a formula that compares your brand new lookup field to the date field, and returns some sort of a result to let you know if that is the latest valuation for that particular owner. So, for example, your formula could look something like this:
Then, once you have that formula created, you have a lot of flexibility to do whatever you’d like to do.
For example, if you stay in the valuations table, you could create a new view in your valuations table that filters your records to only show you the records which have the value “Newest Valuation” for that formula field.
Or if you go back into the owners table again, you can now create conditional lookup fields or conditional rollup fields which are set to only pull in values from the records that are marked as “Newest Valuation”. (There is a little toggle switch in lookup fields & rollup fields where you can control this.)
Hope this helps! :slightly_smiling_face:
p.s. If you have a budget for your project and you’re ever in need of hiring an expert Airtable consultant to help you out, please feel free to contact me through my website:
I’m assuming that your “owners” table is linked to the “valuations” table, so each owner can be linked to many different valuations.
First, you’ll need to go into the valuations table, and you will need to create some sort of a date field so you can determine which valuation is the latest valuation.
Then, go back into your owners table and create a Rollup field which points to that date field in your valuations table. Use the rollup formula MAX(values), which will give you the latest valuation date that is associated with that particular owner.
Next, go back into your valuations table again, where you will now need to create a lookup field that looks up that brand new rollup field that you just created in the owners table. So now, for every record in your valuations table, you will see the latest valuation date for its linked owner.
Then, while you are still in the valuations table, you will need to create a formula that compares your brand new lookup field to the date field, and returns some sort of a result to let you know if that is the latest valuation for that particular owner. So, for example, your formula could look something like this:
Then, once you have that formula created, you have a lot of flexibility to do whatever you’d like to do.
For example, if you stay in the valuations table, you could create a new view in your valuations table that filters your records to only show you the records which have the value “Newest Valuation” for that formula field.
Or if you go back into the owners table again, you can now create conditional lookup fields or conditional rollup fields which are set to only pull in values from the records that are marked as “Newest Valuation”. (There is a little toggle switch in lookup fields & rollup fields where you can control this.)
Hope this helps! :slightly_smiling_face:
p.s. If you have a budget for your project and you’re ever in need of hiring an expert Airtable consultant to help you out, please feel free to contact me through my website:
@ScottWorld awesome answer, thank you. All understood but yeah, that’s a lot of workaround. Still easier than Excel though and faster/nicer/more interesting than firing up a web stack and sticking with what I know.
