3 Replies Latest reply on Oct 1, 2009 7:21 AM by ninja

    Inventory Summary

    Gburg

      Title

      Inventory Summary

      Post

      I have an issue. I have 2 entries in my warehouse summary (summarized by lot number) that have the identical information except number of UNITS.

       

      Example:

       

      WH ID Loc.   Lot No.   HYBRID    UNITS       NET SHIPPED   LEFT TO SHIP

           511         15541    4T985        450             500                  -50

        

           511         15541    4T985        250             500                 -250

       

       

      My issue is, when I enter 500 NET SHIPPED it automatically takes 500 units from both rows. Is there a way to distinguish between the two?

        • 1. Re: Inventory Summary
          ninja
            

          Howdy Gburg,

           

          The two are distinguished by the UniqueID field which is an autoenter serial#.  This should be your primary key for relationships, as opposed to the Lot#, for this very reason.

           

          I hope that you're just populating the Dbase now and this change will not be a massive undertaking, but I would consider it mandatory that a unique serialized ID# get tagged to the records so that this doesn't happen again (and it will...it always does eventually).

           

          Keep all of your existing fields, just add a "UniqueID" field and shift your relationships to use this as the linkage between tables.  It looks like this linkage is currently done via LotNo. (guess on my part).

           

          PLEASE: make thorough backups before you shift the structure!

          • 2. Re: Inventory Summary
            Gburg
               My concern is that I use the Lot# to summarize Lot totals (sometimes the same Lot # will be in two different warehouse locations). If I use a Unique ID serialized number, will that through my summary totals off?
            • 3. Re: Inventory Summary
              ninja
                

              Howdy,

               

              You seem to have the summary system worked out already (since it wasn't part of your original question).  So perhaps the easiest way would be to use the UniqueID# for your transactions, and have your current structure for your summaries.

               

              BTW, you could accomplish your summary by a self join using Lot# and creating a summary field of Qty through the link.  Perhaps this is the way you subtotal now...no problem.  But you've already seen the issue of using that same relationship for transactions.  The link for transactions and the link for summarizing should be two different links.  If you have the same lot in two locations, and pull from one...you'll want a record of which location you pulled from (not both) as your original post describes.  The UniqueID# would address that.  Two records, one for each location, each with a different UniqueID#.