Calculation between 2 records in same table.
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.