Carryover or running total inventory help
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, I...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendIf 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
------------------------------
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
------------------------------
- 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__YouFor 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
------------------------------
