3 Replies Latest reply on Jul 11, 2014 2:28 PM by philmodjunk

    Inventory Table?



      Inventory Table?


           I need to set up a parts inventory table. My confusion is that most parts go in to many different finished products, and come from many different companies and I am having problems figuring out how to take the information I have in an excel sheet, and put it in a table so that each part is separate, but some how still connected to the finished product it goes in to. Each part is listed with its own item number, the problem is that finished products are listed in the excel sheet along side the different parts. I'm not sure if I need to remove these from the table before I import it to filemaker, so that the parts table in filemaker will only have the parts numbers, and not the finished product numbers as well. I'm also confused as to what to use as the primary key and foreign key between the parts inventory table and the finished product table, when the item numbers will not match. I'm thinking the finished products primary key would be the name of the finished product and then in the parts inventory, the foreign key would be the product name, but what do I do when one part may go into different finished products? Then I need to have the contacts table connect to the parts table so that when a part needs to be ordered, it will show what contact we order the part from.

        • 1. Re: Inventory Table?

               Warning! the "Data Dump Truck" is now backing up to dump a huge load into your brain! wink

               Remember the tables and relationships that I recommended for invoicing? You'll see some similarities to it in what I am about to post, but the tables and the purpose for which they are used are different:

               Start with these relationships:


               Products::__pkProductID = BOM::_fkProductID
               Parts::__pkPartID = BOM::_fkPartID

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

               You can place a portal to BOM on the Products layout to list and select  Parts records for each given Products record. Fields from Parts can be included in the Portal to show additional info about each selected Parts record and the _fkPartID field can be set up with a value list for selecting Parts records by their ID field.

               A number field, Qty, can be put in the BOM table to document the number of a particular part needed to produce one product. You can even set a field in the BOM to look up a "cost" field from Parts so that you can compute a "materials cost" for a given unit of product. A calculation field in Products can be defined as Sum ( BOM::PartCost ) to compute such a total.

               Note that this documents how the product should be assembled from it's parts. Additional tables can be linked in at your option to fully document the manufacturing specs--such as a table of PDF's with mechanical drawings documenting the correct assembly of these parts.

               In a "full up" manufacturing system, two additional groups of table occurrences that link to the ProductsParts table are typically set up--one for planning your manufacturing processes. (We'll make 20 units of Product A on Shift X on 7/25/2014...) and one for documenting the actual production with the resulting inventory changes: (We made 19 units of Product A on Shift X, 7/25/2014 with X amount of part wastage...)

               Whether you need that ambitious a setup depends on the size of your operation and the needs of your business. You also do not have to design an implement all of these features at once. You can add a module--a set of tables, layouts and scripts to support a particular task, then move forward to add the next one only after testing your design and seeing it put successfully into regular use.

               And here's something to challenge your thinking: Products and Parts can be two Tutorial: What are Table Occurrences? with the same data source table. A record in this single table can be the record for a part or a product. This is often done so that inventory reports can be produced that list all parts and products currently in inventory.

               And it's possible for one table, related to an occurrence of this PartsProducts table to log each and every change in inventory to both parts and products--whether it be a reduction in Product level due to selling product, an increase in parts due to a delivery from a vendor, shrinkage due to theft/damage/failure to meet QA standards... etc.

               See this thread for such an "inventory ledger" method: Managing Inventory using a Transactions Ledger

               Other threads that may be useful to the new database developer:

               Setting up regular automatic backups during the development process:  Saving Sequential Back Ups During Development

               Setting up a data separation model in order to make deploying database updates easier to manage: Convert to Seperation Model

          • 2. Re: Inventory Table?

                 What does BOM stand for?

            • 3. Re: Inventory Table?

                   Sorry, BOM is a standard industry term for "Bill of Materials". If this were a recipe, this table would list the ingredients and amounts needed to make the item described in the recipe. In your case, this will be the list of parts (and possibly manufactured components) that make up a particular product that you offer for sale.