Calculation field and related fields

[markfmf]

Dear Forum members

This is quite possibly a simple FileMaker solution, but nevertheless as a beginner I have struggled to find a solution.

I have two related tables. Products (Primary key: Product ID) and Product Prices (Foreign Key: Product ID). There is a one-to-many relationship from products to prices, meaning that one product can have many different prices at different dates. So typically a Product Prices Table would have a record for a specific Product ID, a date field and a price field.

Now, I would like to produce a number of fields on the Products Layout that give the %change in prices related to that product for each month. You can assume that the Product Prices Table has a price as at the end of each month.

Is there a way to have a number of fields in the Products Layout that give the monthly percentage change in the Product Price.

How can I most effectively achieve this?

Thank you for your help

Mark

[comment]

This is not quite simple. To start with, you probably don't want "to have a number of fields in the Products Layout", because then the number of months shown will be fixed (or you will be endlessly adding fields to the layout as time progresses).

Now, here's one relatively cheap way to look at it, I think. Note that this might get slower as the number of months increases - so at some point you may want to limit the range shown, or move to some other method.

ChangesEOM.fp7.zip

[markfmf]

This certainly points me in the right direction.

The one downside with this method, is that the cChange field (the %change field) is merely based on the previous record. This assumes that asset prices and their dates are entered in succession. But lets assume that a user puts in another price records for an asset one year ago.

The way cChange currently calculates the %change, it would merely take the previous record's price and the current price and get the %change between them. This is clearly not intended.

Is there a way to make 100% sure that monthly %changes are based on only the previous month's asset price (and not the previous record's price).

Thanks for your help.

Mark

[markfmf]

Could I solve the above problem by maintaining a sort order in the relationship table (Prices 2) such that the records are always sorted by date.

This way, the previous record is always the previous date. Would this be the correct way of going about it, or are there any issues that I should be aware of sorting relationship tables in a different way to FileMaker default.

Thanks

Mark

[comment]

I believe that if you double-click the the portal in Layout mode, and set it to sort by the date field, it will solve the issue.