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

    Sort Birth Dates in List View Starting from Current Month

    illumine

      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.

      Thanks for any advice.

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

          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
            philmodjunk

            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.

            • 3. Re: Sort Birth Dates in List View Starting from Current Month
              SteveMartino

              I wonder no more :)  Well I do about many things FileMaker, but there's your answer

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

                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:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                MONTHSORT CALC RESULT
                December1
                November12
                October13
                September14
                August15
                July16
                June17
                May18
                April19
                March20
                February21
                January22

                 

                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:

                 

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                MONTHSORT CALC RESULT
                November0
                December1
                January2
                February3
                March4
                April5
                May6
                June7
                July8
                August9
                September10
                October11

                 

                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
                  illumine

                  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. wink

                  Again, thanks for your reply!

                  • 6. Re: Sort Birth Dates in List View Starting from Current Month
                    philmodjunk

                    Try temporarily changing your system clock date to a different date and note how the calculation values change.

                    • 7. Re: Sort Birth Dates in List View Starting from Current Month
                      illumine

                      Okay Phil.  The tweaked version of your above original calc seems to shuffle things around as desired when the system clock is advanced one or more months.  It's sweet.  It would have taken me a lot longer to come up with the same result had you not stepped in, and the calc probably wouldn't have been as concise as yours, either.

                      I sincerely appreciate your help! yes