dealing with calculated fields

[Simon Bowler]

I

[The Bridge]

quote:

---

Originally posted by Simon Bowler:

As a calculated field, [transaction_amt] is not alterable or storable and will change if any of its determinig fields change. This gives some difficulties since the line item will change if in the future the price changes. In addition, from time to time the value needs to be altered in an ad hoc way at the time of entry - not possible with a calculated field.

---

It seems that what you need is a related database that stores "default" transaction_amt for your line items. For the sake of this post let's call it default_amt.fp5.

Set transaction_amt in your line items database to Lookup values based on a relationship, this relationship based on one of your "determining fields".

Whenever a value is entered (or modified) in the determining field in a record in your line items database, the transaction_amt value will change as well. You will also be able to change transaction_amt without affecting the "default" or other records with the same line item determining field.

However, otherwise the transaction_amt value will not change at all, even if you change its "default" value in default.amt.fp5. This way previously-created line item records will retain the information entered, and new ones will take on the new value.

I hope this gives you a good start!

[BobWeaver]

There is a way to do this with an auto-enter calculation if you set it up properly. You have to use a case statement to make sure that all of the referenced fields have valid values before calculating the result. For example, suppose you want to calculate an invoice charge as follows:

InvoiceAmount = Quantity * UnitPrice * DiscountRate

You would set it up like this:

Case( IsEmpty(Quantity) ,"",

IsEmpty(UnitPrice),"",

IsEmpty(DiscountRate),"",

Quantity * UnitPrice * DiscountRate)

[ January 09, 2002: Message edited by: BobWeaver ]

[Simon Bowler]

The Bridge and Bob - thanks for the replies. Bob - worked perfectly - onwardsd and upwards.

Cheers

Simon Bowler