Carryover or running total inventory help

Question

Hello everybody, I've searched the forums and can't seem to find an answer to what is probably a fairly common inventory problem. We track inventory by period. I have fields for Beginning Inventory, Inventory Received, Inventory Used and then Ending Inventory. What I'd like to do is have the Ending Inventory of one period automatically carryover to the next period's Beginning Inventory. In our company we use Bimonthly Invoice Periods, the 15th and the 31st. I may not have the terminology right here regarding carryover or running total.

In a perfect world that same Beginning Inventory would default to the prior period's Ending Inventory OR would allow manual entry to a selected value, for the first period of the year, or a non standard entry. But I realize that would add complexity. We are coming from FileMaker and we had this formula there that worked both ways like this. Thank you in advance.

------------------------------
Brian Hunot
------------------------------

MarkShnierYou · Answer

If you can set the key field of the table to be the date of the beginning of the period then you can relate the table to itself. The reference field which would be on the right side of the relationship would be a formula field that would calculate the first day of the previous period. Pst back if you get stuck.

When you make the relationship be sure you name the field for related period properly so you understand what it means and make sure to name the look up fields appropriately name so you don't get confused. When you look at the relationship it will look a bit funny but it does work very well then you can daisy chain as long as you'd like. You just need to make sure that you don't break the daisy chain by a missing Period.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community