I've recommended such a "ledger style" method for managing inventory to many other forum users. I think it's a very good approach and much better than that modeled in the starter solutions that come with FileMaker.
Do you have a field in the Transaction table that shows the location?
When you want to see "1 record of the product for each location", what else do you need to see? The inventory total for that product at that location?
It's possible to set up a summary report on a layout based on your transactions table where you'd put the product fields in a sub summary sorted by your location field and then delete the body layout part so that instead of listing every transaction record, you'd see one line per product and location like you specify.
You'd sort your records for this report first by Product, then by Location so that you group your records by Product first then sub group them by location.
Thanks Phil, I haven't really looked in to summary field, as everything is new to me, will go have a dig now.
Just to explain my thoughts and hope other new people doing similar solution may find helpful
The problem in general is how to find the transaction I want for specific products at specific locations (satisfy 2 foreign key combined), once its locate pulling any related info is very simple.
In invoice or move or any other actual happened records (this is the records in business flow, not filemaker records in a table), it's quite simple as each happened records will relate to specific transactions and locations, just sorting the relationship with dates would bring the lasted one which has the current inventory level to top.
But in reporting, I need all products and all locations and the ability to show partial of them. The relationship is getting tricky here, it not "AND" nor "OR" but mixture.
My transaction table is setup with following field:
Product ID - foreign
Record ID - foreign
Transaction ID - primary
Flag (0 for deleted, 1 for active)
Transaction date : separate from creation date so I can backdate if necessary
Qty on hand
Location : I had all warehouse saved as customer, so this is customer ID actually
All the rest: note, price, price sum etc
When I've set up this kind of thing, I define a fieldl, cBal as Qty In - Qty Out. Then I define Qty on Hand as a summary field computing the running total of cBal, and set it to restart on the Product ID field. That allows me to see both the current Qty on Hand for all products and also shows me a record of how the inventory levels fluctuate over time.
Here's a tutorial on summary reports you may find a useful introduction to summary fields and sub summary layout parts: Creating Filemaker Pro summary reports--Tutorial
Hi Phil, I followed your tutorial and got the transactions listed as sub summaries, almost everything I want, next I will try add some drop down to filter or manipulate the report.
Thanks again for your help.