3 Replies Latest reply on Oct 23, 2013 11:46 AM by philmodjunk

    Inventory Automation



      Inventory Automation


           Hello! I have just started with Filemaker Pro and I'm so impressed with it, but a little bit lost.

           I'm guessing that there is a way to do what I'd like to do, but I don't even know where to start looking for how to set it up. So if you can answer me directly, that's great, but even if you can just point me at which part of the help files to read, that would be most appreciated as well.

           So I have an inventory database. It has the parts in it to make widgets, which I make myself - let's say it's got widget bottoms, widget tops, and widget middles. So in my database, I have entries for the number of tops, bottoms, middles, and completed widgets which I have on hand.

           So say I make three widgets today. Is there an automated way (a script??) to "press a button" (literally or figuratively) and have it increase the widget count by 3 and ALSO decrease the tops, bottoms and middles accordingly?

           It seems like there is probably some straightforward way to do this. Or do I need a different setup? Should my finished goods be in their own separate inventory file?

           Any help is most appreciated here - I am complete beginner, and totally not afraid to "read the manual" - if only I knew where to start reading! Thank you!


        • 1. Re: Inventory Automation

               You are describing the classic manufacturing inventory control database. You'll need several tables linked in relationships first before you tackle the layout design and any needed scripting to automate it.

               Here are some tables you'll need:

               Inventory: One record for each type of item you make and each material you use to make items. So you'll have a record for a widgit in here but also a record for a widgit top, widgit middle and widgit bottom if these are three distinctly different objects.

               BOM (Bill of materials). This table functions like the list of ingredients in a cooking recipe. You create one record for each material used to build your widgit and a field in that record records the quantity of material needed to construct one widgit.



               Item|Inventory::__pkInventoryID = BOM::_fkItemID
               Material|Inventory::__pkInventoryID =BOM::_fkMaterialID

               Item|Inventory and Material|Inventory would be two Tutorial: What are Table Occurrences? that have the same data source table.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               This is just the "recipe" part of a manufacturing database. Other tables would be used to schedule and document the actual production run and the data generated from it would then be used to produce a continually updated inventory showing that each production run increases the number of widgits on hand (until they are shipped) and decreases each material consumed by the production run (until more material is received).

          • 2. Re: Inventory Automation

                 Thank you @PhilModJunk !

                 So what you've said there sounds like it should help me set up the structure of the database table(s). I am a craftsperson - I make, say, no more than a dozen widgets on any given day, and probably far less than that. And my widgets are made up of 3-6 components each. Does that change your answer at all?

                 And then where do I go after that to find a way to automate the process? Is it a scripting thing? Or a button with an "action" or something that I attach to it?

                 Thanks again for your help.


            • 3. Re: Inventory Automation

                   The basic tables, relationships should serve your needs as a starting point.

                   To this, you'll need to add a table where you can log the number of widgets produced each time. You can enter one record in that table for each king of item created. A script can then use the information in the BOM to log the consumption of materials and the production of items.

                   But there are two basic and different approaches to how you manage those inventory counts:

                   1) A number field in the Inventory table is simply updated each time inventory changes. Your script would simply add to and subtract from this field in different records in order to update your inventory.

                   2) A transactions table can be set up where each inventory change is logged much like bank account deposits and withdrawals. The sum of all transactions that increase inventory minus the sum of all transactions that reduce inventory for a given item then computes the new inventory count.

                   The first option is the easiest to set up. The second option allows you to see how a history of how your inventory counts change over time and that can be useful in determining how much of each material should be maintained without tying up too much revenue in yet to be used inventory.

                   The basic set up is the same in both cases:


                   So if you Make 5 "widget number 1's" today, you create a record in Production, select the ID for "Widget #1" from a value list and enter 5 as the quantity produced. Your script can then be performed to increase inventory for Widget #1 by 5 and then it pulls up the set of BOM records for that item, multiplying the quantities in the BOM records by 5 to compute the materials used and then reducing inventory for each item accordingly.

                   The exact details of how that script does it will vary depending on which method for managing inventory you want to use.