Tracking Latest Values

[Neil_Gonzalez]

Hello Everyone, 

I am having a methodology issue.

I am working on a personal price tracking project, in my case, it is computer parts.  But I believe this could translate to almost anything. 

I have multiple tables, one for each component type (CPUs, RAM, Motherboards, etc).  These tables display the characteristics of the product. 

There is an additional table that is called Updates, the updates table links to each of the component type tables, and tracks, price, link to vendor and date the entry was added. 

What I'm trying to achieve, is on the components tables, I'd like to show the most recent price for each component.   I've read a few examples that people have said about sorting by having a Rollup of the date:

max(values)

However, I'm not able to discern how I will retrieve the price if I did that.   

Can anyone orient me toward solving this problem?  

Many thanks

[Neil_Gonzalez · Accepted solution]

Hi all, 

I have answered my own question. 

I created a column in each Components table.  "Latest Update" which is a rollup of the date with max(values) being the result of the column.

In the Updates table, I have a new column LookUpDate (to simplify) that does a rollup which queries the component table against the date in "Latest Update" the result is:

IF(Date = values, 1, 0)

Which is comparing the date of that row to the "Latest update" date column, it returns a 1 or a 0 depending on a match. 

In the components table, I have a rollup on the price, comparing to whether the LookUpDate has 1 or a zero

It certainly is looping a lot, but works fine without any automation.

I had created automation when an entry was submitted to change the value of "latest" from 1 to 0 if the field was a duplicate of the same component, but that eats into my automation, and I figured there was more than 1 way to solve this problem.