Tracking historical prices and calculated fields

[Maclain]

Morning all,

I'm trying to get my head around something. I understand we should not store calculated fields.

So on our purchase orders we have a price list, where the totals are calculated on the report.

We have a form which shows historic purchase orders and the prices paid at the time.

We also have a regular incremental increase from the supplier, once we update the cost per item, all the calculated fields for historic purchase orders are incorrect.

how do we get around this?

 

[spikepl]

By storing the data so that your purchase orders are immutable.

The "don't store calculated fields" is a good rule aimed at preventing bloat, redundancy and errors, but in this instance it simply does not apply.

Update (and clarifying my mudded concepts): prices are data, so they do not need to be calculated but just stored. The sums are calculated, and here one has to choose whether to store or not. If you can calculate at little cost then no point in storing. If result of a calculation is used over and over again, and is heavy in CPU then storing can be justified.

Alternatively you could store the price history, i.e. make prices date-dependant so that proper rice is looked up at the relevant time. Depends on whether such history is relevant and of interest or not.

 

[gemma-the-husky]

sometimes you ought to store calculate figures.

you either need to
a) store the actual prices on the order
b) not strictly necessary, but store the calculated total
c) maintain a history of price changes, so you can establish the correct price

clearly if you do neither of these, then when you change the price you will lose the correct history.

I think most of us might well do both a) and c) - a for convenience, and c for usefulness.