I'm modifying an Expenses DB template that came with my FM12 app (the only app I have) and it does a monthly summary based on Year ( Due Date ) & " " & Month ( Due Date ) The output is Text - which is sorted in the Sub Summary field.

The summary view based on sub-summary sort works for 80% of the year but for some reason it goes Jan - Oct - Nov - Dec - Feb then the rest is correct month order. I'm not sure what the glitch is. Is there a better workaround that sorts by date and year, so all of 2016 in correct month order then all of 2017 in correct month order etc?

The glitch is that it is sorting as text.   

01, 10, 11, 12, 02 is how it would sort as text.  You need it to sort by date.

i would change the sub summary report to sort by date.  You don't have to show the date in the sub-summary part

 

Understand, what’s the best way to sort by month year (summarised by month) for the sub-summary to sort and display correctly. I often get muddled with this. 

1.  Create a  date field, auto-enter calc (uncheck "Do not replace..."), using your field names, call it zzDateMonthYear:

Due Date - Day (Due Date ) + 1  //Make sure it's a date field.

2.  Make this your field (zzDateMonthYear) for the sub-summary report  (Sub-summary when sorted by), and just put your Year ( Due Date ) & " " & Month ( Due Date ) calculation field in that section.  Make sure your calculation is of type 'Number'.

3.  Sort by the zzDateMonthYear month field.

On 11/28/2017 at 7:11 AM, Steve Martino said:

1.  Create a  date field, auto-enter calc (uncheck "Do not replace..."), using your field names, call it zzDateMonthYear:

 

Hi Steve,

There would be no reason to make this stored data unless the User needs the ability to change that date.  Making it a stored calculation is better option. :-)

Edited November 29, 20178 yr by LaRetta

Thanks for the solution. It worked once I spent an hour working out what it did and that I needed to refresh the existing entries Due Date field! I don't use FMP often enough it seems.