Multiple location inventory tracking via relationships, L4 help!
I'm new to FM11 and working my my towards a solution that could manage inventory at many locations, also invoicing. Ideal goal would be able to host this solution for multi-user on iPad.My approach to accomplish multi-location is similar to most invoice solutions, "TRANSACTION" table between "RECORD" table which covers invoice, move & purchase and "PRODUCT" which saves only description.
Difference is I had all inventory info store & updated as "TRANSACTION" detail, like the way a bank statement lists all the credit debit & balance, followed with "location", and I added a flag to "TRANSACTION" for deleting, 0 is deleted, 1 is active. So there's no script to update the inventory in product table with every invoice or move, and there will always be continoues track record as nothing really deleted.
Ideally, in list view of the inventory (or product), I wish to have a drop down to choose locations to report.
Problem is from "PRODUCT" to "TRANSACTION" via "product serial" is a one to many relationship, 1 product serial would pull all its transactions in all locations, I can sort the relationship so the first related records (depending on how its sorted) of each product is displayed, but I need to display 1 record of the product for each location, and be able to filter to a specified location with the drop down.
How can I do this? Or the way I structure the inventory is not right?