4 Replies Latest reply on Feb 11, 2011 7:37 PM by RobinXiao

    Multiple location inventory tracking via relationships, L4 help!



      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?

        • 1. Re: Multiple location inventory tracking via relationships, L4 help!

          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.

          • 2. Re: Multiple location inventory tracking via relationships, L4 help!

            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 in

            Qty out

            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

            • 3. Re: Multiple location inventory tracking via relationships, L4 help!

              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

              • 4. Re: Multiple location inventory tracking via relationships, L4 help!

                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.