Storing calculated fields from form in yable

[mlozano]

I know calculated form control values should not be stored in tables. However, there are some situations where I cannot find an alternative

1.- The default shipping address for an order is not valid and I want to save an "one off" alternative shipping address


2.- Currency exchange rates vary in time. If I take the exchange rate into a query and use it for quotation, all my records' exchange rates will be updated.

These are just a couple of pretty frequent situations where calculated form fields would need to be stored - unless you guys can help me find an alternative!

Thanks

[pbaldy]

I wouldn't disagree with either of those. Product price is another common exception.

My wife and I enjoyed a nice visit to Madrid and Marbella last year. Only downside was people making fun of our lousy Spanish!

[mlozano]

Well your lousy Spanish was probably much better than most spaniards' non-existing english! I hope you enjoyed your stay and consider returning despite all the nonsense the media is pouring out on our country...

On topic, I was posting this just to get some reassurance, as all I read about is how storing calculated values does against all rules and means certain trouble ahead...

Cheers

[pbaldy]

Most people we talked to were very nice, and at hotels/restaurants their English was usually better than our Spanish. My wife and I are both in the same situation. We were fairly conversant in Spanish many years ago in school, but lost much of it in the intervening years.

The caution against storing calculated values is valid, but it could be said that the items you mentioned are attributes of the sale, not of the customer/product, and not calculations either (the product of quantity times price would be a calculated value).

The purely normalized approach would probably be to have a table with multiple addresses for the customer, and storing the key value of the appropriate address with the sale. Same with prices or exchange rates, or you'd store a price with a date range and look up the appropriate price/rate based on the sale date. From a practical standpoint, it makes more sense to simply store the value with the sale.

[Missinglinq]

As Paul pointed out, you're really not speaking of storing the Calculated Field, but rather storing a Component of the Calculation, in multiple places. And I agree with Paul that going with the 'purely normalized approach,' here, is really overkill, and storing the component in multiple places really makes more sense than having to, each time you move to a Record

• Look at the date of a quotation
• Access the Table of exchange rates
• Find the given currency
• Compare dates for that currency until you find the appropriate exchange rate
• Return to your Form and run the Calculation using that exchange rate
  
  Linq ;0)>