AnsweredAssumed Answered

Calculation between 2 records in same table.

Question asked by John@buyliquorbetter on Oct 11, 2009
Latest reply on Oct 12, 2009 by philmodjunk

Title

Calculation between 2 records in same table.

Post

FMP 10, Mac OS 10.6, newer user with a basic understanding of databases.

 

I have 2 tables, one (pricing) has 2 years of monthly pricing for a product. The other (product) has the product and its attributes. What I would like to do is to track change over time to the pricing of the product. 

 

I have a portal in my product table that shows all the price records that match the product item code. That works great. What I need is to calculate the price change this month from the previous month. And, another field that calculates the difference this month from 2 months ago. So...

 

If a product is $10 this month and was $9 last month, I want it to calculate the difference of $1. And, if the price was $8 2 months ago, I want it to calculate the difference this month of $2. But I can't figure out how to tell FMP which records it needs to subtract.

 

Thank you for your time.

 

John 

Outcomes