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.
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.
guy, I guess I cant attach FM files here. Is there a way for me to send you my file?
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.
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.