Sometimes you don't want to update a value but cannot avoid the automatic updating behavior

[Yalcin_Arsan]

I am trying to solve a weird problem: I am working on a sales / invoice table that pulls the ‘price’ column from the products table. The past sales (rows in sales table) are recorded accordingly.

All is fine, until you need to adjust the prices due to inflation: The old sales records also show the updated price, so obviously the past sales figures change to a wrong amount!..

Does anyone have an idea as to how to go about this?

[Breno_Nunes]

Hey, @Yalcin_Arsan .
Take a look at this post bellow by @Paul_Danyliuk

Is there a way to fix values once they have been calculated

You cannot fix a formula’s value, but you don’t have to make it a formula. You can have a regular input column (not a formula), and a button that you’d click to update it with a calculated value once. This is more logical than making a table of historical rates and depending on that in a formula throughout the whole history — that’s because you still risk messing up historical payment records by e.g. deleting old rates entries or editing them erroneously. Whereas by stamping the effective rate…

[Christiaan_Huizer]

hi @Yalcin_Arsan ,

I suggest you create an extra table for prices per product, timestamp them and take the most recent version. This is called the bill of material BOM approach.
cheers, Christiaan

[Xyzor_Max]

this shows the need for some deeper database design.

sometimes we need to record a history of changes.
as @Christiaan_Huizer, suggests, we need a history table to record past prices, and each order links to the row that was current at the time-of-order.

but a simpler method can also be used…

instead of getting the price of a product using a FORMULA, you could set the price in the order using a modifyRows() operation. this would copy the current product price into the order row. and it will not change in the future if the product price is changed. the modifyRows() operation needs to be done inside a BUTTON that gets pushed to create a new order.

max

[Xyzor_Max]

ERRATUM:

i had added an alternative method that used the “set value for new rows” option to set the price for the order based on the product.

but when i tested it, it does not work.

the “set value for new rows” formula is invoked BEFORE any values have been entered by the user, so the Product relation is not set and it does not set any price alas.

So my previous suggestion of using a BUTTON that executes the action

thisRow.ModifyRows(Price, Product.Price)

is still the best i can offer.

max

[Yalcin_Arsan]

This is a very interesting idea. I always wondered how to put “initial value” into good use and this may be a good example. Let’s try.

[Piet_Strydom]

Great and simple idea!

[Yalcin_Arsan]

I am working on this approach. Can you elaborate a bit more? ie: How do you timestamp a single entity? Do you mean add a column for every price change?

[Christiaan_Huizer]

good to read you took my suggestion serious, I wrote a blog about it.
in all my work this logic is applied (contact details, turn over, product prices, addresses, etc).
once you see how useful and important it is, it is hard to do it differently,

cheers, christiaan

Coda the prices of products prudently

[Yalcin_Arsan]

I think I’ve seen this article but found it too complicated at first glance. Will look into it now with more attention. I’ll get back to you, thank you for the suggestion

[Christiaan_Huizer]

hi @Yalcin_Arsan , this is maybe difficult at first glance, but the principles applied are simple
per row you have product and a price with a time stamp.
you compare time stamps via a filter, it results in a list of values and you take the last one
enjoy!