14 Replies Latest reply on Jul 1, 2015 8:27 AM by ekrj

    Computation of Inventory?

    ekrj

      Hi Professionals,


      I would like to seek the advice on the solution for the beneath scenario:

       

      Vendor: Is a florist.

       

      He would like the system to be able to calculate the quantity in the hamper then deduct it automatically all the types of items in the hamper.

       

      Example:

      Inventory Contains: 20 Apples, 15 Pears, 5 Cokes,10 Oranges and 30 Roses.

       

      Hamper A Contains: 5 apple, 3 pear, 3 Cokes and 20 Oranges (these variables are changeable and he/she is able to add new items)

       

      When he add a transaction this quantity (5 apple, 3 pear, 3 Cokes and 20 Oranges) will automatically deduct from inventory.


      Please advice.


      Thank you.

      Eugene K.

        • 1. Re: Computation of Inventory?
          ibrahim_bittar

          Hi Eugene

           

          Basically what you want is a compound product.

           

          You can have a PRODUCTS table and a COMPONENTS table, related by the ProductID.

           

          Well, I decided to create a sample file, which is worth a gazillion words . Sorry if it's not too elegant. I just couldn't sleep LOL.

           

          Hope you find it useful.

          • 2. Re: Computation of Inventory?
            wimdecorte

            There was a long thread on how to do inventory a month or two ago, do a quick search on "inventory" to bring it up.

             

            From a principle point of view you don't want to deduct inventory at the time the order is placed, only when you ship.

            You could keep an "on hand" and a "ordered" value separate if you want.

             

            Very quickly you may find yourself in need of a good transactional approach (discussed in that other long thread)

            • 3. Re: Computation of Inventory?
              ekrj

              Hi,

               

              Thank you for your reply. How am i able to retrieve the sample file?

               

              Thank you.

              Eugene K.

              • 4. Re: Computation of Inventory?
                ibrahim_bittar

                Please send me an email to ibrahim_bittar@eikonsys.com and I'll send it directly to you. It seems that attached files are not making it through the portal.

                • 5. Re: Computation of Inventory?
                  ekrj

                  Hi,

                   

                  It went through! While i am studying

                   

                  I actually had the cost computed. Probably i am not clear in my question posted previously, my bad. The part where i am unsure is how to deduct the inventory automatically when a hamper is sold (etc 20 Apples, 15 Pears, 5 Cokes,10 Oranges and 30 Roses with be deducted from inventories) which i did see the process however when i try to import the invoice there are a lot of rows appearing and the computation is getting out of control and does not tally.

                   

                  Example:

                  When user selects 1 quantity Hamper A on the Movement out,  (20 Apples, 15 Pears, 5 Cokes,10 Oranges and 30 Roses with be deducted from inventories without adding them in the line of movement).


                  When user selects 2 quantity Hamper A on the Movement out,  (20x2 Apples, 15x2 Pears, 5x2 Cokes, 10x2 Oranges and 30x2 Roses with be deducted from inventories without adding them in the line of movement).

                   

                  Please advice.


                  Much Appreciated!

                  Eugene K.

                  • 6. Re: Computation of Inventory?
                    ibrahim_bittar

                    Hi Eugene

                     

                    Can you send screenshots?, it's much easier to understand.

                    • 7. Re: Computation of Inventory?
                      electon

                      Re: Updating Inventory

                       

                      This is the post that wim refers to.

                      Managing inventory can be such a complicated matter depending on many factors.

                      In some isolated cases it is enough to keep a transaction table where stuff going in has positive numbers and stuff going out negative. If you sum() it you'll get the current inventory. But pretty soon it will bog down doe to large amount of transactions.

                       

                      In your case I think if you don't buy the hampers ready made but put stuff in them yourself, you need to track the actual items that are in each hamper. If you let the client take the hamper too, register it also.

                      Good data to have because probably you want to know if you're low on hampers themselves / is the hamper business going good vs loose items ( altho this you can deduct from the transaction record ), etc.

                       

                       

                      So the data could look like this:

                       

                      compound product:

                      Hamper of the Year

                       

                      hamper        x1

                      apples         x2

                      oranges      x4

                       

                      When deducting from inventory you multiply the qty of each individual item in the compound hamper by the quantity of them sold,

                      then deduct this amount for each item from the inventory table, or add them to the transactions table each multiplied by -1.

                      Whichever suits best.

                       

                      You can't do everything just via data tables, sometimes scripting the process is the best because it gives you more possibilities.

                      Is it single or multi user? Single user is the easiest but I's a good idea to make everything multi user, in case your solution helps the business grow.

                       

                      The post goes quite in depth about these issues.

                      • 8. Re: Computation of Inventory?
                        ekrj

                        Beneath are the screenshots. When users are using the system, they would only want to key in the transaction(movement) of Hamper A. The quantity of the hamper will be derived base on the items availability.

                         

                        Example if there is 3 apples left and the hamper requires 5, then the system will prompt the user that there are not enough apples to have the hamper. (taking 1 item as illustration purpose)

                         

                        If there are 10 apples left and the hamper requires 5, then the system will allow user to transact "OUT" in the movement. The transact "IN" will be done only to the items alone instead of the Hamper A.


                        The quantity of hamper will be recorded under the "Out" for graph charting purpose.

                        Example.pngExample 2.jpg

                        • 9. Re: Computation of Inventory?
                          ekrj

                          Hi Elect,

                           

                          Thank you for the reference! I will take a good read on it.

                           

                          Regards,

                          Eugene K.

                          • 10. Re: Computation of Inventory?
                            ekrj

                            Hi Wim,

                             

                            Thank you for your help and tips!

                             

                            Regards,

                            Eugene K.

                            • 11. Re: Computation of Inventory?
                              ibrahim_bittar

                              OK, I see.

                               

                              In the products table you can calculate how many hampers you can make based on how much of each component you have in stock, using more or less the same reasoning you explained me here. Then on invoicing (or quoting, or packing slips, etc.) you can compare the requested amount with the calculated "available" amount for hampers.

                               

                              It's a bit of arithmetic but not so complicated. What you think?.

                              • 12. Re: Computation of Inventory?
                                ekrj

                                I have done the set up and relation however i am kinda stuck here on how to deduct the inventory quality of "X" number of apple and "X" number of pear when "Hamper A" is selected or of "X" number of apple and "X" number of pear when "Hamper B" is selected. Where should this script be written? I kinda tried a few ways but it did not turn out the way i wanted.

                                 

                                Please Advice.

                                 

                                Thank you.

                                Eugene K.

                                • 13. Re: Computation of Inventory?
                                  ibrahim_bittar

                                  Hi Eugene

                                   

                                  Check on the sample file I sent you. In the inventory movements layout there is a script to import an invoice. If you run it with the script debugger you'll see how it works. It does precisely what you want.

                                  • 14. Re: Computation of Inventory?
                                    ekrj

                                    Thank you!!

                                     

                                    I found it~

                                     

                                    Regards,

                                    Eugene K.