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

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

brianhunot1 · Answer

Hey Mark, thank you, I will try this tonight. I've been wanting to experiment with relating a table with itself. I didn't mention, if it matters, that we only have 1 record for each 15 day period, not daily records; and really only modify the record at the end of the 15 day period, not daily. Additionally, the Invoice Period Table and the associated related field don't use a date, it's just a text string. I.E. '1-1 to 1-15'. But maybe setting the key field will still work. There is a possibility of one of our locations missing a period and breaking the chain by virtue of closing and then opening again a month later. But I suppose I could make non-change records to bridge the gaps. The other complication, of course it's not cut and dried haha, is that each of our 40 locations, each of which has a distinct inventory, have different lengths of service each year and open and close at different times. For instance one location is open from 4-15 to 7-31 and another is open from 6-1 to 10-31.

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

markshnier__you · Answer

For the stores only open part if the year, no problem. Have a formula to calculate the normal previous period. Get that working. Then have an override field to override the previous period. Then adjust the formula to use the override if not blank, else use the regular formula.

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

Forum Discussion

Carryover or running total inventory help

3 Replies