7 Replies Latest reply on Nov 17, 2014 1:20 PM by illumine

# Sort Birth Dates in List View Starting from Current Month

### Title

Sort Birth Dates in List View Starting from Current Month

### Post

In list view, I need to sort records for a class of students so they:

1. Are grouped together by their respective birth months and sorted ascending by birth day.
2.
3. Remain together as a group even if their birthday for the current year has passed.
4.
5. Dynamically start the top of the sorted list with the current month, not necessarily January.

I've already parsed out student birth dates into birthMonthNumber = month ( DOB ), and birthDayNumber = day ( DOB ) and have placed these in the Sort Dialog, both in ascending order.  The sort works fine except (of course) it starts the resulting sorted list beginning with January and that's not quite what I need.

Example: As I write this post the current month is November and I want the sorted list to display students with a November birth month to be at the head of the list and then shift the same November grouping to the bottom of the list on the first day of the next month (December, in this case).  Students with December birthdays would then take their place at the top of the list, and so on.

The birth year is not relevant to the desired outcome, as far as I can tell.

• ###### 1. Re: Sort Birth Dates in List View Starting from Current Month

I wonder if a new field, with calculation of Month(Get(CurrentDate)) or MonthName(get(currentDate)) could be used as a sorter field

• ###### 2. Re: Sort Birth Dates in List View Starting from Current Month

Define an Unstored calculation field:

Let  ( [ M = Month ( DOB )  ;
Mt =  Month ( Get ( CurrentDate ) )
] ;
If ( M > Mt ; M - Mt ; 12 + Mt - M )
) // Let

Sort in ascending order on this new calculation field.

• ###### 4. Re: Sort Birth Dates in List View Starting from Current Month

Wow Phil,

Did you write that calculation before or after your first cup of coffee this morning?  Your response was so rapid and your calculation is so simple and elegant.  Thanks for that!

I set it all up in the classroom management solution I'm developing, using your calc, and it seems to be on the right track but I did get some unexpected results.

As written, the results were:

 MONTH SORT CALC RESULT December 1 November 12 October 13 September 14 August 15 July 16 June 17 May 18 April 19 March 20 February 21 January 22

I fiddled with your excellent calc and wound up with this:

Let  ( [
M = Month ( DOB )  ;
Mt =  Month ( Get ( CurrentDate ) )
] ;
Case ( M = Mt ; 0 ;
M > Mt ; M - Mt ; 12 + M - Mt )
)     // Let

…and the records sorted thusly:

 MONTH SORT CALC RESULT November 0 December 1 January 2 February 3 March 4 April 5 May 6 June 7 July 8 August 9 September 10 October 11

This sequence seems to yield the result I was hoping for (i.e. the current month - November - at the top, followed by the natural progression of calendar months).  The thing is, I don't yet know if it will dynamically move November to the bottom of the sort pile and shift everything else up when December 1st arrives.  I'll create a test DB to try it out.  What do you think?  Will your calc with the reflected mods do the trick?

Thanks for your tireless support and encouragement!

• ###### 5. Re: Sort Birth Dates in List View Starting from Current Month

Thanks for the reply, Steve Martino.  I hear you.  I've been dabbling with FMP over the past couple of years but didn't really dig in until a few months ago when I started a teacher-focused project for my teacher wife.  FileMaker is such an impressive and powerful application and even with all my efforts (and headaches) and working late into the night for weeks I realize that I'm just a 'newbie' who's barely scratched the surface.  Sigh… I keep telling myself to stick with it and work on the DB project every single day and at some point I may actually understand what I'm doing.