Inventory sum by Day

[Reid]

I'm trying to track inventory count on a daily basis.

What I want is a table where I put the date in one field and the next field calculates the sum of total inventory count from that day.

Formula wise, I've come up with this:

Inventory for date X = quantities shipped after date X + quantities with no ship dates (still in inventory)

I have a summary field in the inventory that totals all the quantities together. Now I just have to have it show me only the quantities based on the formula I have above. Date X would be entered into a date field on a new table and the SumForDateX would be a calculation of the total sum based on that formula.

[wireshop]

Wouldn't it be easier to have a "ship" script that updates the inventory quantity when an order is shipped. You can still keep track of ship dates and quantites for reporting later. That is the way I do it.

Plus the way you are doing it will get very slow later on once you get a lot of data in the system.

[wireshop]

What I want is a table where I put the date in one field and the next field calculates the sum of total inventory count from that day.

Formula wise, I've come up with this:

Inventory for date X = quantities shipped after date X + quantities with no ship dates (still in inventory)

I have a summary field in the inventory that totals all the quantities together. Now I just have to have it show me only the quantities based on the formula I have above. Date X would be entered into a date field on a new table and the SumForDateX would be a calculation of the total sum based on that formula.

Ok I re-read your post now I get it better.

What you need is a separate table occurrence for your "shipping history" table that is keyed off of the date you input in your new table using a greater than relationship. Your calc will read something like:

sum(ShippingHistoryLookup::Quantity)+CurrentInventory

Now what happens if you have more inventory come in after the date you are looking up? You can add another TO to your receiving history with the same relationship and your calc will look like:

sum(ShippingHistoryLookup::Quantity)+CurrentInventory - sum(ReceivingHistoryLookup::Quantity)

Again I think this will get very slow in the long run. To make your approach work you will have to use an unstored calc. An alternate way of doing this is to have a separate "inventory log" table that has three fields "Date", "ItemID", and "Quantity". Whenever an item is shipped/received you just add another record to the log. This will be much faster in searches in the long run.