Simple Sum not working (idiot user error I'm sure)

[Rob 7 Collins]

I have an accounting database set up ... I need totals per designer per quarter. Somehow I'm messing this up, I could use some help. Here's the relevant info:

Relations:

RoyaltiesbyFont (foreignkey = Quarterlies::key)

Quarterlies

RoyaltiesTotal (quarterlieskey = Quarterlies::key, designerskey = DesignersRT::key)

DesignersRT

FontsRT (designerskey = DesignersRT::key)

PurchasesRT (fontkey = FontsRT::key)

In Royalties total is the field:

calcTotalPrice (unstored, from RoyaltiesTotal)

"$" &

If ( Sum ( PurchasesRT::calcPrice ) = 0 ; "0" ;

Sum ( PurchasesRT::calcPrice ) &

If ( Middle ( GetAsText ( Sum ( PurchasesRT::calcPrice ) ) ; ( Length ( GetAsText ( Sum ( PurchasesRT::calcPrice ) ) ) - 1 ) ; 1 ) = "." ; "0" ;

If ( Middle ( GetAsText ( Sum ( PurchasesRT::calcPrice ) ) ; ( Length ( GetAsText ( Sum ( PurchasesRT::calcPrice ) ) ) - 2 ) ; 1 ) = "." ; "" ;

".00" ) ) )

Now I set everything to the same quarter: Go to RoyaltiesbyFont and find quarterkey = 4. Then go to RoyaltiesTotal and find quarterkey = 4. Go to quarterlies, find key = 4. Go to PurchasesRT, find quarterkey = 4.

On my report layout, the "show records from" is the table RoyaltiesbyFont. The RoyaltiesTotal::calcTotalPrice is in a Sub-Summary by designerkey.

The Sum I get is not correct unless there were no purchases in any quarter. The problem is that the Sum includes purchases from all quarters.

...

I would think that since the subsummary is by designers, I would also have to use Purchases through a relation with Quarterlies in the calculation in order for the Purchases to only include the proper quarter's data. This is not working however. :

Edited January 19, 2006 by Guest
(more details)

[Ender]

Ho Rob!

There you go, asking the tough questions again.

I'm afraid, I can't quite visualize the relationships from the description. If you can post a screenshot of the table occurence graph or a simple sample file with the relevant TOs and fields, it might illuminate any problems with the relationships.

Otherwise, the only thing that stands out is that your calcTotalPrice seems to be processing the numbers as text instead of as numbers, and it's not clear why this is necessary.

[Jim Lah]

Rob, Ender

I'm having what looks like a similar problem - again it is probably user error. I am trying to get the sum or count of values in a field in a table. The application is a simple issues tracker within a business meeting - I want to count the open (or red etc) issues both by the person that owns then, and by the forum within which the issue was raised. I use some related tables as in the attached diagrams. The forum is set as a global field as one variable connection into the portal. Another connection is provided by the ID of the person owning the issue. The portals display the correct data but the sum and count functions seem to count all the issues, rather than those within the portal.

The attached show the relationships and the resultant output.

The calculations are of the form:

Count Reds = unstored, from Count from here, = sum (Issues to count::Red no)

Sum Reds = unstored, from Count from here, = Count (Issues to count::Red no)

I would be grateful for any help or pointers.

Best regards