Summary field updating

[VFXdbGuy]

I have two tables: parent and child.

Parent fields include

Price

ExchangeRate

Child fields include

Quantity

ChildExchangeRate

ExchangeRateUsed (conditional: Parent::ExchangeRate unless ChildExchangeRate contains value)

Cost (Price * Quantity)

USCost (Price * Quantity * ExchangeRateUsed)

This arrangement is needed as quotes are often spread out in time. Most of the items are culculated using the Parent ExchangeRate but the ones done later may require a different rate. Global fields are out as I need the Parent::ExchangeRate to stick whle the file is hosted.

This all works well at the Child record level, until I create a Summary field in the Child table that adds up the USCost for all the Child records. This value refuses to update until a Child record changes. I need this calculated for the Child records as I need the total to reflect the values of the Child records in the current found set.

Am I hooped?

[transpower]

Why not put the summary field for the children into the parent table?

[VFXdbGuy]

That would calculate the total for all the child records, right? I need a summary of the child records in the current found set.

I am starting to think this is because the ExchangeRateUsed field is conditional and references a field in another table. Do you know if it is due to one or the other?

[transpower]

You could use the Relationships Graph to display whatever summary you wanted.

[VFXdbGuy]

I don't quite understand what you mean.

As far as I can tell, unless I somehow mark each record as being in the current found set, the relationship will return the totals for all the records. And they require the ability to change the curent found set arbitrarily and as often as they like...

Can you elaborate?

[VFXdbGuy]

That would calculate the total for all the child records, right? I need a summary of the child records in the current found set.

I am starting to think this is because the ExchangeRateUsed field is conditional and references a field in another table. Do you know if it is due to one or the other?

Well, now I'm even more stumped... I have eliminated the exchange rate override (Child::ExchangeRateUsed). The Canadian values update (Child::Qty * Parent::Price) and they always have. But the US cost won't (Child::Qty * Parent::Price * Parent::ExchangeRate)

Even when I move part of the calculation to the Parent table (Parent::USPrice = ParentPrice * Parent::ExchangeRate) so that the Child equation becomes Child::Qty * Parent::USPrice, it still won't update when the Parent::Price changes!

[VFXdbGuy]

...just in case this hasn't been clear, the calculations are all displayed on a Child-based layout, as far as I know, that's the only way to see summary values for the current found set of Child records

[VFXdbGuy]

Case closed...

The real problem here was a summary used in a summary. To see this problem in action, have a look at the attached. It was onl by making this very siple database that I figured this out.

Go to the Child layout. You'll see two USTotalCost fields. If you change the Price you will see that the Summary-based US Total Cost value won't update (unless you change the Child::Qty) but the claculation-based value does.

I hope this helps someone else avoid my pain...

SummaryUpdate.zip