SubSummary of portal records

[ron G]

I am running FM 11 adv

There are 2 tables: Members and Dues. There are of course related 1 to many.

The dues are in a portal on the Members layout. Each row in the portal shows the dateYear and amount of the dues payment. The portal sorts on the dateYear descending.

I want to create a SubSummary report that will sort on the dateYear field but ONLY PICK UP THE TOP (ie, highest ranking) dateYear and it's associated dues information.

The way it is now, if a member makes payment in 2009, 2010 and 2011, each entry shows up under the appropriate year. One under 2009. One under 2010 and one under 2011. I just want to show the 2011 payment information. (Other members may have a history of 2006, 2007 and 2009 for example)

Is there some kind of filter for this perhaps?

Thanks

[comment]

Is there only payment per year (for one member) or can there be several?

[ron G]

Is there only payment per year (for one member) or can there be several?

It is possible, but not usual, to have more than one payment per year. What is your thinking?

[comment]

My thinking is that this may not be as simple as you perhaps imagine. But I am still not sure I understand exactly what your report should look like.

Suppose member Adam made one payment of $100 in 2009, and member Betty has two payments, $50 and $25, both in 2010. Is it enough to show:

Adam  2009  $100

Betty 2010   $75

...

or do you also need to show the individual payments?

[ron G]

My thinking is that this may not be as simple as you perhaps imagine. But I am still not sure I understand exactly what your report should look like.

Suppose member Adam made one payment of $100 in 2009, and member Betty has two payments, $50 and $25, both in 2010. Is it enough to show:

Adam  2009  $100

Betty 2010   $75

...

or do you also need to show the individual payments?

Hmmmm. I thought I answered this but it is not showing?

Anyway, it would be nice to see the detail but not necessary. To affect the detail, I can add an arrow button with GTRR to the layout that shows the detail.

So, it at a minimum I just need to see Betty and the total she paid for a given year.

I realize this isn't as easy as I thought. I started out thinking I would use a Summary field called MAXYEAR to grab the "biggest" (most current) year for any member. But, I can't add MAXYEAR into the SubSummary break in the report. So, I guess that idea is out... I am looking forward to hearing what your thoughts are.

[comment]

Well, you could try it this way: in the Dues table, define a summary field sTotalAmount as Total of Amount. Also define a calculation field =

GetSummary ( sTotalAmount ; Year )

Base your report layout on the Dues table. Use only a sub-summary part when sorted by MemberID. In this part, place the Year field and the calculation field along the name/s field from the Members table. To produce the report, show all records and sort by MemberID and by Year descending.



I'm writing this without testing, so I hope I got it right.





The other option is to define a calculation field in the Members table =

Max ( Dues::Year )

then create another relationship to the Dues table based on matching this field to the Year field, along with MemberID. You can either sum the related amounts using this relationship in the Members table, or use GTRR [Match found set] to create a found set in the Dues table containing only last year payments of each member.