6 Replies Latest reply on May 20, 2016 8:26 AM by Magnus Fransson

    Another question about Inventory


      A standard structures between customers and a company should be:


      Inventory --< LineItem >-- Order >--- customers


      But how about from vender side? As a company I need to order goods from vender as well. So supposedly, it should be appeared as:


      Vender --< PO --< LineItem >-- Inventory


      However, I am concern about my on hand quantity.


      Say I order 100 pieces of candies, but I cannot enter it directly to the PO LineItem because it will immediately reflect my on hand qty. Whereas, I am still waiting the candies to be delivered to me. Besides, what if my vender cannot deliver 100 pieces of candies at once? What if they only deliver 50 pieces of candies first and deliver another 50 a week later?


      What should I do to solve this problem?


      I am thinking that I should create another table called "Bill" somewhere between the PO and Inventory, but I do not know what exactly I need to do.


      Can anyone give me some advices?

        • 1. Re: Another question about Inventory

          You need to have a field for ordered and delivered in your purchase order line table. Its only the delivered stock you need to count against you on hand inventory,

          1 of 1 people found this helpful
          • 2. Re: Another question about Inventory

            That might solve the problem but I usually don't pay my vender in advanced. Instead, I pay them based on what they delivered.


            If they deliver 50 pc of candies along with a bill showing 50 candies delivered from certain PO. I will use that bill to pay my vender. Thus, it would be better if I can have a bill table created to help me keep tracking my POs.


            Any idea how to do so?

            • 3. Re: Another question about Inventory

              There are a couple of options, another field for quantity paid for in the POLine, this is the simplest way.


              If you go down your bill table route you will get better information on the history of both deliveries and payments to suppliers. It depends on how much you need to see.

              • 4. Re: Another question about Inventory

                This is easier if you have a generic Transaction table – Order, Delivery, and even Sales – each with a factor that determines how a certain line item is calculated into stock: Order: 0, Delivery: 1, Sales: -1


                So the figures for an Order transaction will not be reflected in the stock calculation; once the goods arrive for a certain PO, create a Delivery transaction tied to that PO (and as required, duplicate and adapt that Order's line items) to reflect the actual delivery (e.g. 50 delivered where 100 were ordered). ow those line items will be considered for your stock calculations.


                A Bill table could simply be a correspondence table that records any correspondence created from a transaction, but hasn't its own line items – it is simply a container for communication events that pertain to to transactions, where Transaction --< Correspondence.


                Angelus81 wrote:

                I usually don't pay my vender in advanced.

                “vender” = vendor

                • 5. Re: Another question about Inventory

                  Yes and no. Personally I would lean towards your suggestion and have in out internal solution however transactional models are far harder to code well. For example all transactions against a PO will need a payment, shipping transactions will require an invoice, you will need to chop the data from this table into these reports for payment/invoice required. You will need to filter portals, tweak list views and all sorts of other stuff.


                  Also I would not calculate stock live (i.e. all the ins minus the outs) unless the system was simple.


                  I am thinking the OP was leaning towards a simpler system so was aiming my suggestions there. There is no benefit in complexity beyond the minimum required.

                  • 6. Re: Another question about Inventory
                    Magnus Fransson

                    Hi Angelus,

                    To avoid making it over complicated, I spare "Transactions" till later.


                    Your "Order_Linitem" and "PO_Lineitem" tables should both have two stored fields storing "Ordered quantity" and "Delivered quantity" (you could even calculate "left to deliver"). And Your "Inventory" table should have a field for storing "quantity in stock".


                    Then you need two scripts (identical for "order" and "PO") that when a "delivery" (in or out) happens, the delivered quantity is correctly stored in appropriate "LineItem" and either add to or subtract from "quantity in stock".


                    With best regards Magnus Fransson.