5 Replies Latest reply on Apr 9, 2013 4:22 PM by philmodjunk

    Inventory in Multiple Locations

    K.Feete

      Title

      Inventory in Multiple Locations

      Post

           Hi all,

           I'm building an inventory system for my workplace that includes (I hope) the ability to see how much product is in each of our locations. I'm working from a table that lists each location (the Move Table) and then a related ledger table which records Move From ID, Move To ID, Item ID, and then either Quantity Moved or Quantity Removed (depending on whether the item was just shifted from one location to another or sold).

           I've got the system up and running in most areas but how exactly to track how much of a given item is in each location is stumping me. I've messed around with portals and summary fields and calculations but I can't seem to get even a start, which is doubly frustrating given that I feel like all the correct data is sitting there in the ledger table waiting for me, if only I could figure out which angle I needed to sink my teeth into it.

           Does anyone have any suggestions or insights that might get me going?

        • 1. Re: Inventory in Multiple Locations
          philmodjunk

               I'm not sure I follow the purpose of your Move table.

               I've been able to help others set up the ledger table and not have a "move" table. Each record in the ledger table included a location field. Moving product from location A to Location B would be logged with two records in the Ledger table. One that reomved it from Location A and one that added it to Location B.

               From such a ledger, you can sort your records to group them by Product, then by location in order for summary fields to show the total on hand for each product at each location. There are several variations of this report you can set up depending on whether you want to see just the figures for one product and whether you want to see the individual changes in inventory or just the current totals.

          • 2. Re: Inventory in Multiple Locations
            K.Feete

                 Thanks so much for the prompt reply!

                 Well, now you've said it, I'm not sure why I have  the move table either. :) At the moment I mostly use it to print barcodes and provide human-readable names for the locations on some layouts, but I suspect it's mostly there because it seemed logical at the time. I can probably get rid of it if necessary.

                 The setup of the ledger I am somewhat more cautious about. The workflow of my business is extremely untidy: though we have only a few employees, I'd estimate we handle 100-200 units of product a day, all of which has to kept track of via both type and batch (we have to log each day's production separately), and it's unfortunately common for a batch to be split between two or three locations and then sold out of any of them -- or all of them. The good folks who are moving and selling all this stuff have quite enough to deal with, so I'm trying to keep the number of times I want them to stop and mess around with a barcode scanner very minimal and the scanning itself very simple.

                 I think I see how the setup you're describing would be able to manage a batch split between two or three locations, which is a primary concern, but I am worried that it would clog up the workflow. Would my guys need to scan "out" product and then do a separate scan "in" for the identical product and amount? (I can probably talk them into it, but I was promising a single scan, so... I may need to go armed with chocolate....) Also, is there a way in your setup to differentiate between product being "removed" to a different location and being permanently removed from inventory (sold)?

                 Don't get me wrong -- I see what you're saying and this may well be the solution I go with. But if I can appeal to your patience while I try to wrap my head around the details.

            • 3. Re: Inventory in Multiple Locations
              philmodjunk

                   The starting point is the structure of the ledger table. Once you have that, you can set up layouts customized to the needs of your business. It would certainly be possible to scan an item once and then input the needed info to log a move from one location to another that generates two records in the ledger off of that single scan.

              • 4. Re: Inventory in Multiple Locations
                SamuelPeret

                     This is similiar to what I'm looking to create. Would you be able to send me your template?

                • 5. Re: Inventory in Multiple Locations
                  philmodjunk

                       I may have a demo file buried somewhere but I am not finding it and lack the time to really search through older files looking for it. I did pull up this thread that describes the method I recommend in some detail: Managing Inventory using a Transactions Ledger