Summing Filtered Portal Rows

[Jon Crain]

I have a portal in my project which is displaying by quarter the amount of payment to a certain party. I want to add into the same portal the amount paid to that party total. (A summary of the amount of payment from all quarters for a given party) I can't seem to find the correct way of doing this. Any help would be appreciated.

jon

Edited May 24, 2006 by Guest

[John Mark Osborne]

Create a calculation in the parent table with the following formula:

Sum(ChildTable::Amount)

Make sure you refer to the Amount field using the same relationship as is attached to your portal.

Edited May 24, 2006 by Guest

[Jon Crain]

Still not quite fitting the solution to my circumstance.

Here's a little more context.

Take a look at the T.O. diagram that I've attached.

I have a portal on a "Project" layout referring to "Payments."

I'm assuming you mean to add in the calculation in the Royalties table, but that doesn't work for me. Are my relationships messed up, or does the RoyaltyPayments subtable necessitate a different approach? Thanks.

[John Mark Osborne]

I meant for you to add the calculation to the Projects table. The calculation might look like this:

Sum(Payments::Amount)

It's hard to tell you whether your relationships are setup correctly or not because I don't have enough information. It's hard to tell from your description what you really want to do. But, if you want a total of the values in a portal, use the Sum function.

[Jon Crain]

if you want a total of the values in a portal, use the Sum function.

Kind of...I want to sum the values of groups on a portal, like subtotals.

jon

[John Mark Osborne]

Can you post an example and direct me to where you want the totals. Thanks.

[Jon Crain]

I'll just post the whole thing to save a little time.

Just click View Project.

You will see Royalties and the place I want to view the Paid to Date. This should be totals from the payment tab.

jon

WPM.zip

[PeterHW]

Hi,

the problem is that since you are creating payment records from the projects table, and with the way you have the relationships set up, there is no way that the correct RoyaltyID can be set in the Join Table (RoyaltyPayments). Each time a new record is created in RoyaltyPayments, RoyaltyID is automatically set to the value of the first related record from Royalties to Projects. Therefore, records you create like this are not properly related to the Royalty record you think they are.

So, there should be a mechanism where you can choose the related RoyaltyID when you create the payment. The way to do is is to create a TO for payments, directly from Projects, set to allow creation of records. Then, using a value list (based on related values from Royalties) the user can select the correct Royalty.

I've modified your file, please take a look at it. Note that you do not need a join table for Royalties and Payments : this is a straightforward one-to-many scenario (one royalty can have multiple payments, but one payments only relates to one royalty).

Hope this clears things up, if not let me know.

Regards,

Peter

WPM_modified.zip

[Jon Crain]

Thank you Peter, your solution seems to be working great! I'm trying to tweak it to also allow the artist royalties to be displayed in a similar manner while at the same time cutting down on tables with using the royalties as a T.O. instead. Haven't had any luck yet, but it will have to wait till next week as this weather is too nice not to enjoy it. Thanks again!

jon

[PeterHW]

Glad I could help. Can't say we're having the same luck with the weather over here. Nothing but rain for the last week, and more to come... :

Ah well, at least I get to spend some time at FMForums.

Regards,

Peter

[Jon Crain]

I have everything restructured to how it should be. Now I'm beginning to think, there is a different solution to display the royalties for artists. Because the artist's are tied to groups as opposed to being tied to the project, the structure for displaying the royalties and payments per project changes correct?

I also seem to be having the same issue with the publishers as well.

WPM.zip