Totaling Records in Portals

[EasyEdE]

Simplifying my situation as much as possible here is my dilemma:

Two databases A and B.

A is linked to B with 3 different links:

Link Z relates to a subset of all the records in db B, Link X relates to a subset of the records related to Link Z and Link Y relates to another subset of the records related to Link Z. Together the records related to links X and Y equal the total records related to Z.

The layout in db A has 5 portals:

Portal 1 is related to db B via Link X and shows 8 rows with a scroll bar.

Portal 2 is related to db B via Link X and contains the total of the records displayed in Portal 1.

Portal 3 is related to db B via Link Y and shows 8 rows with a scroll bar.

Portal 4 is related to db B via Link Y and contains the total of the records displayed in Portal 3.

Portal 5 is related to db B via Link Z and contains the sum of Portals 2 and 4 (or indirectly Portals 1 and 3).

The dilemma is that the totals "bounce all over the place" - click on a field in Portal 1 and the total for that field in Portal 2 changes, sometimes it's correct, sometimes it equals only one of the several records in Portal 1, sometimes it's 0. Ditto for Portals 3 and 4. Portal 5 never contains the proper totals - sometimes it's 0, sometimes it's the total for Portal 2 and sometimes it's the total for Portal 4 but never the sum of Portals 2 and 4. Click on various portals in the layout and the numbers change!?!?!?!?

One thing that's really driving me nuts (yes - a very short drive) is that I have a very similar setup elsewhere in the 19 different db's related to on another and it works fine (albeit a minor refresh problem once in a while - meaning a portal needs to be clicked on in order for the correct total to appear, but once it is there it is correct and it does not "bounce around"). I've managed to fix some of the refresh problems by adding "Refresh window" in the GoToLayout script step going to the errant layout. A couple of layouts are proving to be stubborn and won't refresh. This is very minor compared to the problem outlined above.

I'm using FM Unlimited 6.0 on a G4 Mac running OS 10.2.

Any help will be greatly appreciated, including prayers, incantations, a hardy toast at the local pub - whatever it takes to get the gremlins (a nice name for MY mistakes) out of the db.

[Kurt Knippel]

Portal 1 is related to db B via Link X and shows 8 rows with a scroll bar.

Portal 2 is related to db B via Link X and contains the total of the records displayed in Portal 1.

Portal 3 is related to db B via Link Y and shows 8 rows with a scroll bar.

Portal 4 is related to db B via Link Y and contains the total of the records displayed in Portal 3.

Portal 5 is related to db B via Link Z and contains the sum of Portals 2 and 4 (or indirectly Portals 1 and 3).

What I think that your problem is, is that you are a little off in your setup. What portal 2, 4 and 5 will likely show is NOT "the total of...", but simply one of the totals from the records in that relationship.

What you want to do is to setup fields in your parent file which will total the records via the various relationships.

cTotalX = Sum (LinkX::TotalField)

cTotalY = Sum (LinkY::TotalField)

cTotalZ = cTotalX + cTotalY

[EasyEdE]

Capt Kurt you saved the day. Seriously I'd been hammering away at the problem for most of yesterday with no success. I'd gotten very close by putting the calculation in the parent BUT I DID NOT use the Sum function, I was using Total Of.

An added benefit is that your solution lets me clean up my layouts a bit - ie Portals 2, 4 and 5 are no longer needed. There are 6 instances of this concept in the overall database so with your help I am able to clear out a number of "gremlins"

While it has been a few years since I've been in the Santa Clara area you are in line for a good meal with appropriate beverages should my travels take to your area. THANK YOU - I can now get on with cleaning up the db and start exploring the implementation of a web interface.

Ed DiTomas