4 Replies Latest reply on Feb 8, 2016 2:42 PM by trustedworld

    Inventory Starter Solution - multiple locations

    trustedworld

      We are a non profit and we have a clothing donations center.  When we get too many items for the donation center we send them to the warehouse until we need them.

       

      I am using the "Inventory" starter solution.  Each bin has a bar code and I added a bar code scanner to the solution. I can scan the bar code and it will tell me all the info that I want to know about that item. (bin location, part number, item name, item category...) It will also allow me to add or subtract inventory.

       

      I would also like it to let move/assign inventory to our warehouse. Then when I scan a barcode it will tell me how many are in the bin that I am looking at and how many are sitting in our warehouse.

       

      Any suggestions?

        • 1. Re: Inventory Starter Solution - multiple locations
          electon

          You'll need to store inventory information separate from the item table, linked by item_id.

          Also would be best to have a table for locations.

           

          The inventory  table will have at least location_id, item_id, qty and all the extra info about the exact location of the item.

          • 2. Re: Inventory Starter Solution - multiple locations
            trustedworld

            Electon,

             

            Thanks for responding. I'm kinda new to this so please pardon my slowness.

             

            So I need to create an inventory table with item_id, location_id, qty and anything else. then link this table to the main table by creating a relationship between the two tables using the item_id.?

             

            You mentioned it was best to have a location table. What would I store in that one? and would I like the location_id to that?

            • 3. Re: Inventory Starter Solution - multiple locations
              electon

              Location table is what stores the ID ( primary key serial number ) and the name of the location.

              Location is what uniquely identifies the main group where inventory items are. This will be:

              Table: Location

              ID    Name

              1    Donations Center

              2    Warehouse

               

              Table: Inventory

              ID    LocationID    ItemID ( or Part Number )    Qty    Bin

              1    1                    1                                                5    The bin in Donations Center

              2    2                    1                                                2    The bin in Warehouse


              The Bin is kinda optional because maybe you don't bin stuff at the Center but you do in the Warehouse.

               

               

              Depending on where you use the app you can default your inventory context to a Location by it's ID.

              But that's a bit more involved and at the moment you can just concentrate on getting this right.

               

              So when this is set up, you transfer your inventory from location to location by some scripted method and some global fields for selections.

              You'll need begin location id ( this you can get from current / selected record in inventory ), end location id and the change qty.

              Decrease qty from begin location, increase in end location by the change qty.

              • 4. Re: Inventory Starter Solution - multiple locations
                trustedworld

                I followed your direction and created a separate table for locations and i was able to assign different locations to each of the items.  This worked great.

                 

                My next question is how do I get the total of each item based on location? I would like to see this on the same layout when I pull up the record for each item.  In my head I think the formula should be (Sum (" Units In" where location = Warehouse)  I tried many different variations of this with no luck.  Any suggestions?