Jump to content

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


Rob 7 Collins
 Share

This topic is 5711 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Guest
(more details)
Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

  • 2 weeks later...

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

Picture_2.png

Picture_1.png

Link to comment
Share on other sites

This topic is 5711 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.