5 Replies Latest reply on Jan 4, 2017 8:40 AM by jofftan

    Inventory by lots



      Inventory by lots


           Hey guys!  I'm working on an inventory DB  and I've learned quite a lot so far.  Hopefully you can help me out on this.

           My database is set up with purchase order table linked to line-items table which in turn is linked to the SALES table. The products table is also linked to the line items table. I'm keeping all the calculations (QTY SOLD, Summaries etc.) within the Line-Items table

           My issue is that FM is not recognizing or differentiating between lot-product-qty (sold or purchased).  If I buy a product (purchase order layout by lot number) it adds this amount to the product and does not create an entry for this item by lot number. i.e

           Purchase order:

           Lot# 3455M

      Product                 QTY purchased         Total

           Yellow Tail                       25                       100

           I created a list report by lot number and it creates it nicely stating the above, but my problem comes if I create a second purchase order for the same product with different  LOT #.  FM just  adds the amount to the 25cs  bought in lot 3455M.

           Same thing happens when I enter an invoice it just subtracts the amount from the product total and it is not subtracting from the lot/product line.  It is not taking into consideration that they are two different things even though they have the same description.

           MY report looks something like this:


           LOT                     Product               In_stock

           3545M            Yellow Tail               25

           1254M            Yellow Tail               25


           Hope I'm explaining myself clearly and that you guys can help me out.  I'm about to give up on this, although I can see the light at the end of the tunnel. 

           Thanks in advance


        • 1. Re: Inventory by lots

               Hi Julio,

               I have an Inventory video on my youtube channel. You might want to check that one out.

               I'm not sure what you are doing with your Lot Nr. but ideally what you should have is:

               A Products table where your Yellow Tail only shows up once with it's own unique ID number.

               A Line items table that has a ProductIdFk field.
               Also a PurchasesIdFk and Sales IfDk to relate every line item to either a purchase or a sale.
               An "Amount Bought" and an "Amount Sold" field that you fill in properly according to whether an item has been bought or sold. 
               A "Total Bought" calc field that takes the Amount bought and multiplies that by a Price field also in the Line items table.
               A "Total Sold" calc field that takes the Amount sold and multiplies that by a Price field also in the Line items table.

               Then, in your Line Items you nees two summary fields that total the amount bought and the amount sold. (s_AmountSold and s_AmountBought )

               And then a calc field (let's call it c_Balance) that calculates the difference between the amount bought and the amount sold:
               s_AmountBought - s_AmountSold 

               That field should always give you the correct amount in stock if you display it on the related products table.

               Hope this helps. 

               Guy Stevens

          • 2. Re: Inventory by lots

                 Hey Guy, Thanks for your fast response.  You don't know how many times I've watch and how much I've learned from your videos.  I was super happy when I saw that it was you responding to my post, since my DB is based on your sample inventory structure.  I have basically followed step by step your video and  I already have everything that you suggested in your post.   

                 Let me give you a quick background on what we do:  we are a fish house and we keep product for other people (our Clients).  We track everything based on LOT #s.  Today we might receive 10cs of yellow Tail Snapper under lot 5566M and tomorrow we might get another 15cs under LOT # 1055M.  That is why I need FM to separate these by lot.

                 Like i said, your video was extremely helpful, with me just changing names and adding some stuff.  I've tried to put the "LOT" field everywhere and restructure everything every time, but for some reason or another I'm not getting the right results.  I'm Attaching my file to make it easier for you to see. If you be so kind to let me know what I've done wrong.



            • 3. Re: Inventory by lots

                   guy, I guess I cant attach FM files here.  Is there a way for me to send you my file?

              • 4. Re: Inventory by lots

                     Hi Julio,

                     If you use something like Dropbox or Google drive you can put the file in there and share it with me. Or paste the public link in here.

                     I'll PM you my email.

                     But where I think it's going wrong is the fact that you are using the lot numbers. If you have two different lots of the same fish you need to have that noted somewhere. 

                     That's why I said that your "Yellow Tail Snapper" needs it's own ID.

                     And then you can have as many lots as you want. The lots are irrelevant to your stock balance. 

                     The only thing that matters is that your Yellow Tail snappers have their own ID and that every line item record of the Yellow tail snappers contains that Yellow Tail snapper ID.

                     Otherwise you will never be able to calculate how many there still are in stock.



                • 5. Re: Inventory by lots

                  Maybe the lot become relevant when you have to deal with food, to isolate what lot are getting too old to be sold and then you need to treat it as a lost or something like that.