Help

Tracking Latest Values

Solved
Jump to Solution
884 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Neil_Gonzalez
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
Neil_Gonzalez
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Neil_Gonzalez_0-1670616927872.png

 

 

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. 
 
Neil_Gonzalez_1-1670616983114.png

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

Neil_Gonzalez_2-1670617044924.png

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. 

See Solution in Thread

1 Reply 1
Neil_Gonzalez
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Neil_Gonzalez_0-1670616927872.png

 

 

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. 
 
Neil_Gonzalez_1-1670616983114.png

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

Neil_Gonzalez_2-1670617044924.png

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.