3 Replies Latest reply on Feb 27, 2017 4:50 PM by DanielShanahan

    Inventory Count DB help


      Hello Community,


      I need some assistance please. 

      I am long time FM user and have built some decent apps that work great and we use every day.  Though, I have been working on an inventory DB that I just can't get right.  I have modified it and changed it numerous times over the years and I am hoping someone here can help with the current versions of what this DB should do...


      OBJECTIVE:  A DB app that is used to count all the bottled beverages (items) on the bar, in the cooler, and in the storage.  The NEW RECORD is a list of all the beverages (items) that we have and we would select location, and select qty for item for this date that we count.  The ITEMS would be kept in a table that would auto update to the COUNT table.  As items change we would update them in the ITEMS table and then when we did a count the items would be listed there in the COUNT table.  Important,  the NEW RECORD would be based on the DATE.  This new record would have all the items in the ITEMS list in the table in one view.  Perhaps we would remove the items we didn't need or have per NEW RECORD count. 


      After we create two COUNTs (could have same technical day/date, or could be days apart) then we could get a difference calculation between the two COUNTS. 


      Ex.  If on 2/10/17 at 5pm we clicked "new record" and within this record a long list of all our beverages is accurately listed then we count Bell's Oberon at 20 bottles.  Then on 2/10/17 at 11:30pm we created another COUNT record and counted Bell's Oberon at 10 bottles. 


      Then we could somehow execute a calculation between the two records to show the difference in  Bell's Oberon as 10. 

      Not sure how this would be handled.  I assume there would be another table to pull any two counts up and execute the calculation and create new record of this DIFFERENCE COUNT??


      We could keep COUNTS and at any time create DIFFERENCE COUNTS and we could go back a look at these record over time and compare to sales.  Note:  we do have an inventory component in our POS but it doesn't do this.  It doesn't let you take snapshot counts and compare at any time you wanted.  We would want this to be usable in FM GO. 


      Now if I can get this working I would then want to modify to use with liquor weight units of g/oz (instead of bottle units). 


      I hope this makes sense and you can help.  Thanks in advance. 

        • 1. Re: Inventory Count DB help

          I suggest posting description or screen shot of your tables and relationships.


          I take it that you want to update your inventory via physical counts of the product rather than through logging sales transactions and shipments received?


          Using relationships and dates, it should be possible to do what you describe, but the devil is in the details of your data model.

          • 2. Re: Inventory Count DB help

            I would recommend, if you haven't already, white-boarding this task. Make sure you can get the results you want there, or on paper, before trying to code it. Spend as much time as needed to make sure you can get from "here" to "there" and can get the results you need.

            Break the project into smaller ones and get that working before combining (Step-wise refinement. aka: "Divide and Conquer").


            philmodjunk's suggestion of having an ERD is critical. How can you build a DB system without a model (or a house without blueprints....The construction metaphor works.)


            Suggestion: Use a REAL ERD program to model your data, not the Relationship Graph. A simple but effective tool I found (for the Mac, anyway) is SQL Editor. It's only $79 and WILL CONNECT TO YOUR LIVE FILEMAKER DB! This tool will also read and GENERATE your FM DB (from the tool) once you model it. (And, it works with most other DB models, too, like SQL Server, MySQL, Oracle, ...). Though there may be others, it's the only ERD tools that works with FMP I've found.


            SQLEditor: Product Support


            Again, take as much time as needed to get the model correct. On larger projects I've worked, this DB modeling phase can take a couple months with often daily meetings (DB size for this type of project is a hundred or more tables, however).


            HOPE THIS HELPS.

            • 3. Re: Inventory Count DB help

              I’m not clear on the NEW RECORD table.  I’m not sure you need it.


              I would set up the following tables:


              ITEM - for all your unique SKUs.

              LOCATION - for the different locations, e.g. Bar, Cooler, Storage are all unique locations.  Depending on your need, you may want to add fields for row, shelf, bin, etc.  It depends on how big your Bar, Cooler, Storage areas are.


              COUNT - for your quantity fields: On Hand, Allocated, Available, On Order, On Backorder, etc. - your fields may vary depending on your needs.  The COUNT table is a join between ITEM and LOCATION so:


              ITEM ——< COUNT >—— LOCATION


              I also recommend a TRANSACTION table to track the movements of your inventory.  This might be what you are describing with NEW RECORD and DATE tables?


              You also mention the liquor weight units of g/oz instead of bottle units.  You’ll need a couple more tables:

              UNITOFMEASURE (or UOFM) to list your various units of measure and UOFMCONV to store the various conversions for your units of measure, for example 1 gallon = 128 ounces.


              You don’t mention anything about how you purchase the liquor but I suspect you’ll also need a PURCHASE table, PURCHASELINE, RECEIPT, RECEIPTLINE for purchasing (maybe your POS takes care of this?)


              I agree what others have said about creating a data model.  You can download a general PDF data model at FileMaker Inventory Resources.  The site also has a number of articles and a few demo files dedicated to building Inventory Management Systems in FileMaker Pro.