Display Summary from another table in current layout

[netbooee]

I have a Layout based on Table A that has a Summary Total of Loans from that table. I placed that total in the header successfully. I would like to pull in a Summary of Payments from Table B into this Layout.

How can this be done?

[Matthew F]

First you will need to have a relationship between Table A and Table B based on an appropriate identifier (e.g. Client ID). Create a new calculated field for Table A, which I'll call 'PaymentSum'...

PaymentSum = Sum(Table B::Payments).

Now put PaymentSum in your header.

[Søren Dyhr]

The aggregate functions are not always up to the burden ...

http://www.kevinfrank.com/download/2009/shadow-reporting.zip

--sd

[netbooee]

Thought it could be that simple, but I am still not able to pull that summary from Table B.

I Checked table join is there

I created a CALC field in Table A:

PaymentSum = Sum(Table B::Payments)

I am able to sum all the payments from Table B on each indiviual record in this layout but I can't seem to total the Sum of all those payments for the records displayed.

Any other ideas?

[Matthew F]

You could take a look at this example.

example.fp7.zip

[netbooee]

Here is the example back modified with what i am trying to achieve.

Table A PaymentSum should = 70

This would achieve totaling all the payments for the Loans.

Thanks Again, maybe my initial explanation was poor.

example.fp7.zip

[Matthew F]

OK. There ares still two ways to do it. In either case you should make a second instance of Table B (e.g. Table B2) in the relationship graph. Relate Table B2 to Table A using the 'X' (any) connection. You can now either:

1) Place a related Summary Field from Table B2::PaymentTotal on the layout

2) Make a calculated field in Table A based on the new relationship PaymentSum= Sum(Table B2::Payment).

I prefer #2 since it recalculates instantly. For option #1, changes in table B don't always refresh in the Loans layout unless you click in the field

example2.fp7.zip

[netbooee]

Nice I also like your second opetion, it worked..

Now my challenge is to have the totals change based on a subset or find. For example if I bring in only Jacks Loans both totals in the header will adjust to only Jacks amounts.

Any ideas for that?

[Matthew F]

For example if I bring in only Jacks Loans both totals in the header will adjust to only Jacks amounts.

Yes, That's what I thought you intended to do originally in the first example. Just use the calculated field on Table A defined as:

= Sum(Table B::Payment). This is assumes that the original Table B is related by client IDs.

It will be more complicated if you want the user to be able to be able to perform ad hoc searches and total those subsets.

[netbooee]

Perfect thanks... This seems to be a nice solution to my layout woes.. :)

[Søren Dyhr]

Well since we are under fm10 why not use summary fields instead?

--sd

example3.zip

[Matthew F]

Now that I like! Summary fields come in handy since your script switches to a Payment layout. A script is also handy since you can use the 'Go to Related Record' step to quickly find the relevant records.

However, I think it doesn't answer the initial question of how to display sums from the Payment table on the Loans layout. Sometimes its nice to have aggregate data from two different tables in one layout.

[Søren Dyhr]

how to display sums from the Payment table on the Loans layout.

Perhaps, but isn't it a fixed idea of what table the data should be served from ... the endusers might feel nothing, the layout could be given an arbitrary name revealing nothing of it's origin. By and large should summaries be served form the most atomic of the related sets of data ... perhaps is it more the developer who fails wrapping his/her head around the concepts the tool is promoting.

--sd