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

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?

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

3 Likes

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

2 Likes

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

2 Likes

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

2 Likes

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.

Great and simple idea!

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?

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

1 Like

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

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!

1 Like