FM dynamic query

[Eric Von Kruse]

I'm new to FM and a bit confused on how to create a relationship that will allow me to compare and do calculations between two records in the same table.

Here's what I want - to input an item's physical inventory with a date, and compare a product's instock number to any previous date's instock number for all inventory items.

I've created an inventory table that is related to itself by productid and the date in inventory2 is greater than inventory1. This layout lets me calculate instock2 - instock1 = difference.

So, for an item I have 3 records in Feb. The dates are 2-28, 2-19, and 2-1. Right now it compares 2-19 with 2-1, and that's it. But I want to be able to tell it to compare 2-28 with 2-1, or 2-19 with 2-1. Is this possible?

[Dr. Evil]

What do you think about creating a CatalogTable?

This would store all the items you sell. Then have a portal for your inventory entries.

Then create a CatalogInventoryTable:

This table would be a child of the CatalogTable and log your inventory date and number of items at this date.

This would track the history.

We can elaborate if you like, let me know if you have any questions.