Summing Multiple Records

[HunterBoss]

All

I am in need of what I thought would be a simple solution. No luck.

I have 1 table that is a summary list of my clients. In it there is a unique 6 digit client id number.

The second table has multiple records per client. There also exists the same 6 digit client id number in each record. As the number of times a client could pay is different, there can be anywhere from 1 to 4 (or more) different records per client in table 2.

I have used the sum(child table::amount) calculation. It works. Kind of. I'm only getting a result returned for those clients that have 1 payment record. Those clients with more than one are not showing up.

I know I'm supposed to do something with adding a portal and I know it's supposed to be easy, but I'm all out of options.

So, basically, in Table 1 I need the summary total of client payment records from Table 2.

Thanks in advance.

here's the file

Billable.zip

Edited June 15, 2006 by Guest
didn't add the file right the first time.

[Søren Dyhr]

Kind of. I'm only getting a result returned for those clients that have 1 payment record. Those clients with more than one are not showing up.

Well for what I can see in your attached file, have you made the summing over the wrong relation, the field Summary of Fees is actually showing the sum of the related records in the Services tabel!

--sd

[HunterBoss]

Correct

Services has the various individual payment records. So, it's summing the correct fields and records in Services, I just can't get the total amount to display when related to a record in Clients.

I've been trying to do this in an incremental approach, but after thinking about it I realized I left a key additional step out.

I'm hoping to use the "sum" function (or something similar) because I'm going to need rolling totals. In other words this month I want the totals from June 05 through May 06. Next month I'm going to want the totals from July 05 through June 06, etc.

So, I guess I need some kind of "get" or "found set" in there somewhere?

The way I understand the "summary" function is it's going to give me a sum of all the records. Or, is there a way to limit that as well?

Thanks for your help

Christopher

[HunterBoss]

Ah, what a learning experience. I don't know if I should be elated or depressed.

The positive part is I got my first Script to work. That's how new I am. It's doing what I want, with the exception of not being able to use a variable date. But, that I can live with for now. So, now I can look at just the payments I want to.

The next positive part is I got a portal to work.

Problems -- the portal is not using the found set I created with the Script. It's looking at all records from the Services table. Even the ones that are not in the found set. Not sure how important this is.

Next problem -- the SummaryTotal from the Services table is still not coming over. I created the portal to make sure I was relating to the correct records in Services. I am.

This has got to be simple. What the heck am I missing? There's got to be a multitude of ways to display the sum of multiple records.

Billable.zip

[HunterBoss]

ok, SD

I think I finally understand what you were saying. If the numbers I want to total and sum are in the table "Services," then I have to base my summary layout on Services.

Is that what you meant?

So far, so good. Now, what do I do when I need the values from different tables to all appear together?

Christopher

[Søren Dyhr]

Sorry for not responding earlier on... I have toyed with what it seems you're telling me... so please investigate the template.

--sd

BillableMod.zip

[HunterBoss]

sd

thank you so much! I've only had a few minutes to go through what you sent me, but I think I got the general idea. I think you even put a way in there for it only to look at the last x number of months.

This little episode of mine shows that we need a samples area in here for us new guys who don't know the difference between a table and a file.

thanks again sd

Christopher

[Søren Dyhr]

I think you even put a way in there for it only to look at the last x number of months.

Yes, but only indirectly by making the thresholds from the 6th in a month to the 5th in the next, such ranges could be made as you would wish them to be!

--sd