2 Replies Latest reply on Jan 1, 2014 4:32 PM by dsimonson

    Sorting based upon day of week, Relationship based upon day of week?



      Sorting based upon day of week, Relationship based upon day of week?


           I need to be able to create a surgery schedule sorted by a surgeon’s block time on a particular day of the week.  So I need the sort order to vary depending upon what day of the week it is. 


           For the purpose of my example, consider that I have two tables: 


      appointments, which has surgery appointment data including patient_name, surgeon_id, appt_date, and appt_time


      surgeons, which has a surgeon_id and surgeon_name, etc. 


           The two tables are related via surgeon_id


           On Monday, Surgeon A operates first in the morning, so I want her sort order to be 1.  Surgeon B operates after Surgeon A on Mondays, so I want Surgeon B to be 2.


           On Tuesday, Surgeon B operates first, so I want him to be 1, and Surgeon A is second, so I want her to be 2.


           Currently, I am handling this in this fashion.  The surgeons table has fields called “dr_order_monday” and “dr_order_tuesday” and so on.  When I go to report on a day’s worth of appointments, I have created a report for each day of the week that has a sub summary part based upon that day of the week’s surgeon order field.


      This works pretty well, but I want to create a bunch more layouts based upon this sort order and it seems too complicated.  I don’t want to have to make 5 copies of each layout.  Thus I would like to create a field in the appointments table that looks at the surgeons table via the day of the week, and then puts a number in the appointments table field corresponding to the proper sort order for that surgeon on that day of the week.  Then after I have a found set of a day's worth of patients, I should only have to create one layout, which is sorted on that field.  


           My initial attempt was to create a calculated field called appointments.zz_c_sort_order that has a calculation like this:


      Case ( 

      DayName ( appt_date ) = “Monday" and surgeon = "Smith"; 1 ;

      DayName ( appt_date ) = “Tuesday” and surgeon = "Smith"; 2 ;


           I soon realized that I would need to do this for all 13 of my surgeons 5 times each (we work Mon-Fri mostly), and if they changed their order I would have to go back in and actually change the numbers in the calculation.  This was not going to work. 


           I’m thinking of some sort of relationship between my appointments table and my surgeon table based upon the appointment day of the week, but I am getting strung out on it.    Any good ideas?  TIA!


           Dan Simonson


        • 1. Re: Sorting based upon day of week, Relationship based upon day of week?

               Instead of 5 copies of the same layout, you can put 5 copies of the same sub summary part on your layout--each specifying a different break field. Since you will only sort on one of the 5, only one sub summary layout part will be visible at any given time.

               But that's still pretty complicated.

               You could define an unsorted calculation field to return the sort order value for the current day (or a specified day) of the week and use it as a single sort key.

               Choose ( DayOfWeek ( YourDateFIeldHere ) ; "" ; "" ; MondaySortKey ; TuesdaySortKey ; WednesdaySortKey ; ThursdaySortKey ; FridaySortKey )

               And you can then sort on this key as a single field. "YourDateFieldHere" can be an unstored calculation field with Get ( CurrentDate ). It can be a global field with a date that you specify or it can be a regular date field if you have a table where you have one record for each day that has surgery scheduled.

          • 2. Re: Sorting based upon day of week, Relationship based upon day of week?

                 You are a GENIUS!!! This worked perfectly!  


                 Here is what I put into the field calculation:

                 Choose ( DayOfWeek ( appt_date ) ; 

                 "" ;

                 and it returns the proper sort order.  Then I just went back to my layout and made zz_c_sort_order the break field.  Voilá!  

                 Thanks so much, this is going to simplify things immensely.  Happy New Year!!