3 Replies Latest reply on Jan 6, 2014 9:40 AM by philmodjunk

    Inserting multiple items into record



      Inserting multiple items into record


           I was looking at the Inventory starter solution and ended up creating my own.  The related tables for this question are:

           1. Products - This is singular products.  Such as one specific tv model would be stored here among other specific products.

           2. Inventory - This is products that are assigned to locations.  For instance warehouse1, warehouse2.

           3. Locations - This is locations such as warehouse1, warehouse2.


           What I want to do, is on the Inventory Form Layout, I want it to show every product that is in the Product Table, and put a spinner type field next to each product.   Then it would have a location popup box on that page also.  When a button is clicked, it creates (number of products from Products table) records in the inventory table with their location and timestamp.

           Is this possible?  There obviously will be products added and deleted over time also in case that matters.


        • 1. Re: Inserting multiple items into record

               There are two basic approaches that can be used to manage inventory:

               1) Each time something changes inventory, (Product is sold, shipments are received, product is manufactured, shrinkage, etc) a new record is added to a transactions table logging the increase or decrease in inventory at the specified location. This is more sophisticated and more work to set up, but allows you to track how your inventory levels change over time--which can help set tighter re-order levels for each item.

               2) There is exactly one record for each item at each location. Changes in inventory increment or decrement a number field in that table to update the count for that item at that location.

               I think that you are setting up a version of option 2. Is that correct?

          • 2. Re: Inserting multiple items into record

                 I don't know really.  My friend has a electric company that does new houses.   He has about 30 electricians that have their own vehicles and each electrician does their own job (house).  What happens is, on any given day, a third party drop ships equipment to his electricians or to one of his three warehouses.  His electricians and the warehouses all keep a stock level on their truck or in the building.

                 So, there might be on any given day, 100 different products going out to 30 different electricians in different amounts.   If he can only update 1 product at a time per electrician, then that would take forever.  So, it made more sense to me - to have a list of all products with a quantity spinner next to each that he could quickly update, choose who these products were going to and that electrician (location) would be updated.  Then he could do the next location.

                 Then, when the electrician gets the shipment, he would have to check in what he got.  Then he would have to check out equipment that he uses for jobs and that equipment would be assigned to a customer that he creates.   Does all this make sense?

                 He's been having problems with theft.  So I told him I'd help him create a basic inventory system to help him keep track of it.  

            • 3. Re: Inserting multiple items into record

                        don't know really.

                   Well it's your database, so you'll need to decide on how you want to set this up. What you describe could be set up for either method, but the details of how you set it up will vary.

                   The key difference is that the more complex Transaction based system allows you to track usage statistics over time.


                        He's been having problems with theft.

                   Then I'd definitely use a transaction based approach. That would allow you to look for patterns such as one electrician that is consuming inventory at a rate that does not match the number or jobs when compared to other electricians.

                   To start, take a look at the Inventory Starter solution that comes with FileMaker 12 or 13. You'll find such a transactions table set up in very simple form so you can look it over to see how it works.

                   Then, for a more sophisticated discussion of the same, you might see this thread: Managing Inventory using a Transactions Ledger

                   For what you are requesting, it will come down to creating Transaction Records to log each change in inventory and each change in location, such as moving inventory from a warehouse to a particular electrician's vehicle. This can be streamlined with scripting to produce a list of Inventory Transaction records like you want. Buttons can be added to that layout that increment or decrement a field to update the "In" or "out" fields to log a change in inventory--I think that's the closest you can come to a "spinner" for FileMaker Pro layouts, though you might be able to use a drop down list on an iPhone or iPad layout and flick through values to select a quantity.