4 Replies Latest reply on Feb 28, 2014 9:23 AM by philmodjunk

    Tracking incoming inventory parts from multiple item receipts

    CraigFink

      Title

      Tracking incoming inventory parts from multiple item receipts

      Post

           I am a contractor who inventories most of there products to be sold.  I am attempting to to track from PO(stock ordered ) to item receipt (Stock received).  I have a diagram that goes from PO Table to PO Line item table to track each individual part ordered. Then, I have a table Item Received that is related to the PO To Track the different shipments received to fulfill  the PO.  Here is where I start stumbling.   Since all line items or all the quantities of each line item are not necessarily fulfilled on one item receipt is the reason I have an additional table for item receipts.  What I do not see is how , since each PO and each PO line item can have multiple item receipts, how I can Track Each line item from the PO to see if it has been fulfilled.   I have seen other examples where they do not track the individual item receipts but I would like to because these item receipts are generally turned into bills when they are received.

           Any input would be great as I also want to use the same methods for outgoing to the different jobs

           Thanks, Craig

        • 1. Re: Tracking incoming inventory parts from multiple item receipts
          philmodjunk

               Best guess at your relationships:

               ItemsReceived>------PO----<POLineITems

               PO::__pkPOID = POLineItems::_fkPOID
               PO::__pkPOID = ItemsReceived::_fkPOID

               Seems like you would really need this set of tables/relationships, however:

               ItemsReceived>----ShipmentsReceived>-----PO----<POLineItems

               And this could need even one more table if a single received shipment fulfills at least part of more than one order....

               This doesn't answer your question, it just seems like you would want to track the date and items included of each received shipment as part of your overall system here unless each received shipment only consists of a single item--which seems highly unlikely.

               After I click Post, I'm going to try a forum search for a thread where I helped someone else with the same issue. It's a long thread that goes into a lot of detail so it would be a good example of one way to manage this process if I can find it to include a link here in my next post.

          • 2. Re: Tracking incoming inventory parts from multiple item receipts
            philmodjunk

                 Well my memory was definitely playing tricks on me. The thread that I was searching for works on the opposite end of the shipping process. It dealt with shipping out shipments that had back orders--necessitating multiple shipments to fulfill a customer's order. That would be a bit of a stretch to apply here though there are some definite similarities.

                 Whether you add a table for shipments received, what you need is to add a second table occurrence of POLineITems to your relationships:

                 POLineItems|Received>-----ItemsReceived

                 ItemsReceived::_fkPOID = POLineItems|received::_fkPOID AND
                 ItemsReceived::_fkItemID = POLineItems|Received::_fkItemID

                 You can format ItemsReceived::_fkItemID with a drop down list or popup menu of ItemID's and descriptions from a conditional value list of just the Items for a specific POID in order to link the Item received to a specific POLineItem record.

            • 3. Re: Tracking incoming inventory parts from multiple item receipts
              CraigFink

                   PhilModJunk

                   I am new at this so it takes me some time to visualize things.  I will tell you, your first response is how I had the relationships drawn out on my diagram.  I couldn't quite visualize how I could relate the line items and if I can't visualize, I sure haven't been able to make it happen in the database.  I am leaving the office right now and the second response, I have not had time to absorb but I plan to look at it later today or tomorrow.   I will be responding back after I have more time to look at it.   Thank you for your quick response .  It is appreciated

              • 4. Re: Tracking incoming inventory parts from multiple item receipts
                philmodjunk

                     Here's something that I forgot to include that may help you understand what I posted:

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