I have added an additional TO to sum the withdrawals in a set of financial records by year, Withdrawal YTD column. I would like to show the yearly Withdrawal YTD sum in the last record of the year only and be blank in all of the other months in the given year, it may be Dec 31 or may not. All of the records would still be there, only some the values in the Withdrawal YTD column would be blanked. In this example only these values would show in the Withdrawal YTD column.

Dec 31, 2008   $8,000

Dec 31, 2009   $9,000

Dec 31, 2010   blank

Dec 31, 2011   $10,000

Dec 31, 2012   $11,000

Jul 31, 2013  $12,000

Before data below.

Edited January 4, 201610 yr by laguna92651

27 minutes ago, laguna92651 said:

I would like to show the yearly Withdrawal YTD sum in the last record of the year only

I believe the sensible thing to do would be to summarize the records by year and show a summary field (Total of Withdrawals) in a trailing sub-summary by year part - i.e. after the last record of the year.

 

27 minutes ago, laguna92651 said:

I have added an additional TO to sum the withdrawals

I am afraid that makes no sense to me at all.

 

Edited January 4, 201610 yr by comment

I thought of that, but I just want to keep a continuous list of records with out any breaks. There are alot of columns, and maybe down the road when I get a good handle on what data is really important to me.

"I am afraid that makes no sense to me at all."

What didn't make sense is there a better way to summarize the years withdrawals without using a summary field?

4 minutes ago, laguna92651 said:

but I just want to keep a continuous list of records with out any breaks.

Well, it is possible, but it will cost a bit in terms of performance. Just hide the summary field when the next record's year is the same as the current record's.

 

6 minutes ago, laguna92651 said:

What didn't make sense is there a better way to summarize the years withdrawals without using a summary field?

No, of course not. But it makes no sense to add a TO for this purpose.

That is what I am struggling with to set up, don't I need a TO for that? If not how would I do that?

3 hours ago, laguna92651 said:

don't I need a TO for that?

No. At most, you need a layout for the report and maybe a script to go that layout, find the desired records and sort them.

 

3 hours ago, laguna92651 said:

If not how would I do that?

See if the attached example works for you.

 

YearlyTotal.fp7

Edited January 4, 201610 yr by comment

Very elegant approach, looks great. My first pass on my app didn't work but I did it in a hurry, I'll have to slow down and be sure I set it up correctly. thanks for the help, will let you know.

Worked great, thanks.

Is there a way to modify the approach so I don't have to sort on cJan? Ideally, I would like to keep records sorted by ID, in case a date is miss-entered, wrong year entered. If an incorrect date is entered it can easily be seen as being out of order, if it is sorted by cJan the incorrect date will just fall into the sort date order and may not be noticed.

Filemaker will provide sub-summary values only for sorted groups. Ostensibly, you could copy the sub-summary values into a regular field, but I would advise against that, esp. in a multi-user scenario.

3 hours ago, laguna92651 said:

Worked great, thanks.

Is there a way to modify the approach so I don't have to sort on cJan? Ideally, I would like to keep records sorted by ID, in case a date is miss-entered, wrong year entered. If an incorrect date is entered it can easily be seen as being out of order, if it is sorted by cJan the incorrect date will just fall into the sort date order and may not be noticed.

I would see that as a data entry issue, rather than a reporting issue.

I have a similar issue I deal with in people's time sheets, where they need to make sure their previous entry ends at the time for the next entry's start time - and I use some conditional formatting to highlight the entries that may be incorrect. And I apply it in the standard data entry mode, not on the end of week reports...

 

Edited January 4, 201610 yr by webko

Worked great, thanks.

Is there a way to modify the approach so I don't have to sort on cJan? Ideally, I would like to keep records sorted by ID, in case a date is miss-entered, wrong year entered. If an incorrect date is entered it can easily be seen as being out of order, if it is sorted by cJan the incorrect date will just fall into the sort date order and may not be noticed.

Using conditional formatting is a great input to deal with the data entry task, thanks.