3 Replies Latest reply on Jan 4, 2012 2:14 PM by philmodjunk

    Multi-Location Multi Product Inventory System

    StefanCohen

      Title

      Multi-Location Multi Product Inventory System

      Post

      Hi There,

      I am new to filemaker but not database design. I am trying to build a simple system for our business. I am struggling though to figure out the best way to do this in filemaker.

      Essentially we want a transactional/ledger based inventory system with multiple locations and multiple products.


      I invision having a table that has transactions with to - from and quantity in and quanitity out. Ideally when someone would save a record/ transaction it would create a record in a related database with a date of transaction and the quantity subtracted from the "from location" and the quantity added to the "to location". We would like to be able to report on the inventory stocks at the end of each month (carrying forward the prior months inventory) . I am struggling significantly with this. Does anyone have any ideas, am I approaching this the right way?

      -one of the issues I am encountering is how upon saving a record in one database do you have another record automatically created in a related database.

      -Also it is important to note that these are batches of products we are not concerned with the serial numbers or anything that uniquely identifies specific products.

       

      thanks in advance

        • 1. Re: Multi-Location Multi Product Inventory System
          philmodjunk

          Use the Search tool at the top of this screen to search for "Inventory Ledger". You'll find a number of threads discussing such a transactional inventory management system. It is intended to work with groups of items identified by serial number where the serial number is specific to the type of item rather than the individual item--which would appear to be what you need here.

          After checking those out, feel free to return to this thread with any additional question you might have.

          -one of the issues I am encountering is how upon saving a record in one database do you have another record automatically created in a related database.

          The method we are discussing does not actually require that, but this can be scripted via the OnRecordCommit script trigger to perform a script that creates the related record. Typically, the primary key of the current record is copied into a variable so that the script can change to a layout based on the related table, create the record, copy the value from the variable into the new record's foreign key field and then returns to the original layout.

          • 2. Re: Multi-Location Multi Product Inventory System
            StefanCohen

            Hey Phil Thanks for the response. I read the other posts earlier and the thing that I was having a hard time grasping is how to build in the multi location component. Do you have any thoughts. Would a flat database or a relational database work best for this?

             

            thanks

            • 3. Re: Multi-Location Multi Product Inventory System
              philmodjunk

              You are almost certain to need related tables to complete your solution, but the basic "ledger" is a single table. I would add one more field to the ledger file to identify the location. You can then log an inventory change by ID, qty, date and location. You can then manipulate your found set of ledger records with finds and sorts to get inventory totals specific to a location, specfic to an item type, or both.

              In such a system, a related table might have one record for every type of item (Serial numbers for them are generated there). Another related table might list locations (and have serial numbers for them as well).

              Other tables might be needed to facilitate specific types of events that modify inventory such as production reports, purchase orders, sales invoices, Shipments recieved or shipped, etc. In many of those cases, you use a portal to the ledger table (It's the line items table in a typical invoicing system) to log the item type by item type inventory changes.