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
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