Hi,

In one of my table, I have a calculation field (calcCost) and a number field (cost) which is an auto-enter calculation that simply copy the value from the calculation.

"calcCost" is working perfectly.

"cost" is not taking the value from calcCost

cost = calcCost (Auto-enter calculation replace existing data)

Can someone help me with this weird problem?

Thanks

Let me get straight to the REAL issue here: no, you cannot bypass the limitation of calculations that reference related fields (at least not this way).

Edited March 13, 200916 yr by Guest

What would be the right way? Then

What are you trying to accomplish?

In the cost field I want to have a default cost which is copied from calcCost.

The user might have to change the cost manually for a given reason.

When the calcCost field is recalculated, I want the cost field to be updated.

I though it was quite simple, but it doesn't seem to work the way I wanted.

When the calcCost field is recalculated, I want the cost field to be updated.

An unstored calculation is recalculated on every screen refresh. Obviously, you wouldn't want the cost field to be updated every time that happens.

You still haven't said what's the overall purpose.

I want the user to be able to modify the cost by hand.

The system will be use to calculate automatically the cost of an evaluation file on our company.

The calculation is based on the city, the type of evaluation, the options that are added. The sum of these gives a total cost, that can be eventually modified by the user.

I'm afraid cannot advise, because you keep providing generalities instead of specifics. All I know so far is that your calculation is unstored. Why is it unstored? I don't know. Perhaps it references related fields. You didn't say. What should happen if some referenced fields are modified AFTER user has specified the result manually? You didn't say.

Oh sorry, it's an unstored calculation because it references related fields.

It's the same principle as the invoices-products relationships.

invoices (pkInvoiceID, ..., calcCost [sum invoiceDetails[totalCost], cost [I want to have the calcCost value])

invoiceDetails (pkDetailID, fkInvoiceID, fkProductID, quantity, price[lookup from product], totalCost [quantity * cost])

products (pkProductID, name, price, ...)

The invoiceDetails::price is primely used for invoice history because if the price of the product change, I still want to have the price it was when the invoice was made.

So I just want the invoices::cost to copy the value from calcCost in case the user must modify the cost of the invoice by hand.

Thanks

The way it's usually done with invoices is to have a dedicated field for discount. The total price is then something like:

Sum ( LineItems::ExtendedPrice ) - Discount

If you like, you can have an Override field instead, and make the calculation:

Case ( Override ; Override ; )

Actually, there's more than discount and a single "LineItems" table.

totalPrice = sum (lineEvaluationTypes::price) + sum (lineSelectedOptions::price) + sum(otherFares::price) + cityDistanceCost

Actually, I prefere the price lookup in the LineItems method. This way the user can tweak the price for specific cases.