8 Replies Latest reply on Apr 6, 2017 9:36 AM by joshuaw07

    How do I Automate the Collection of Reoccurring Dates?

    joshuaw07

      Our private academy enrolls students for a full year. They live on campus for the duration of their stay. We have monthly phone calls set up in which the students call their family members. This occurs on the 3rd Thursday of every month.

       

      When a student is enrolled their parents are given a schedule which establishes the dates in which phone calls occurs. Currently we look at a calendar and manually fill out this form.

       

      How can I automate this process?

       

      Once the 1st phone call date is established I need the next 10 month's phone call dates automatically collected.

       

      That would be the date of the 3rd Thursday for the next 10 months.

       

       

      Screen Shot 2017-04-06 at 9.11.52 AM.png

        • 1. Re: How do I Automate the Collection of Reoccurring Dates?
          Johan Hedman

          Create a Script that sets your dates. Are they in a related table or unique fields. If they are related table create a Loop. Else just set them one by one.

           

          Since you know 1st Call

           

          Set Variable ( $Date ; Your1stCallField )

          Loop

               New Record

               #Increase date with 3 weeks (21 DAYS)

               Set Variable ( $Date ; $Date + 21 )

               Set Field (YourDateField ; $Date )

          End Loop

           

          If you have unique fields

           

          Set Variable ( $Date ; Your1stCallField )

          Set Field ( Your2ndCallField ; $Date + 21 )

          Set Field ( Your3rdCallField ; $Date + 42 )

          Set Field ( Your4thCallField ; $Date + 63 )

          and so on

          • 2. Re: How do I Automate the Collection of Reoccurring Dates?
            joshuaw07

            Thanks for your help. I am unsure about the 21 days. the third Thursday of each month does not always equate to 21 days. I may be missing something though.

            • 3. Re: How do I Automate the Collection of Reoccurring Dates?
              Johan Hedman

              Sorry, then you need to make a longer calculation.

               

              You since you can check what day the first day of each month is

               

               

              Let (

              [

              currentdate = get(CurrentDate);

              firstDayOfMonth = date ( month ( currentdate ) ; 1 ; year ( currentdate ) );

              daynumber = DayOfWeek ( firstDayOfMonth )

              ]

              ;

              Case(

              //Monday + 3 days = Thursday + 14 days = 3rd Thursday of Month

              daynumber = 2 ; firstDayOfMonth + 3 + 14;

              //Tuesday + 2 days = Thursday + 14 days = 3rd Thursday of Month

              daynumber = 3 ; firstDayOfMonth + 2 + 14;

              //Wednesday + 1 days = Thursday + 14 days = 3rd Thursday of Month

              daynumber = 4 ; firstDayOfMonth + 1 + 14;

              //Thursday + 0 days = Thursday + 14 days = 3rd Thursday of Month

              daynumber = 5 ; firstDayOfMonth + 0 + 14;

              //Friday + 6 days = Thursday + 14 days = 3rd Thursday of Month

              daynumber = 6 ; firstDayOfMonth + 6 + 14;

              //Saturday + 5 days = Thursday + 14 days = 3rd Thursday of Month

              daynumber = 7 ; firstDayOfMonth + 5 + 14;

              //Sunday + 4 days = Thursday + 14 days = 3rd Thursday of Month

              daynumber = 1 ; firstDayOfMonth + 4 + 14;

              )

               

               

              )

              • 4. Re: How do I Automate the Collection of Reoccurring Dates?
                philmodjunk

                If you know the month and year as you would from a looping script:

                 

                let ( [ D = date ( $month ; 1 ; $year ) ;

                         Dw = DayOfWeek ( D )

                        ]

                        D - Dw + 5 + 14 + if ( Dw > 4 ; 7 )

                      )

                 

                will produce the third Thirsday of a given month provided your script assigns the correct values to the variables.

                • 5. Re: How do I Automate the Collection of Reoccurring Dates?
                  taylorsharpe

                  Just to confirm, are the phone calls in a related table or are they a bunch of fields in the same table?  This works best if they are in a related table and you see them through a portal. 

                  • 6. Re: How do I Automate the Collection of Reoccurring Dates?
                    joshuaw07

                    This seems to be on the right track, but i need the fields to be populated with the dates in advance. So when the first phone call is established,(as seen in the image above) the following fields need to be populated for the next 10 months.

                     

                    Also taylorsharpe I have all 10 fields in the same table.

                    • 7. Re: How do I Automate the Collection of Reoccurring Dates?
                      philmodjunk

                      I have all 10 fields in the same table.

                       

                      better to make that 10 different records in a related table.

                      • 8. Re: How do I Automate the Collection of Reoccurring Dates?
                        joshuaw07

                        johanhedman this was it. I kept the fields in the same table. Then I adjusted the calculation so each field would grab the date of the phone call the month prior.

                         

                        so currentdate = get(CurrentDate);

                        was changed to setdate = Phone2;

                         

                        So to collect the phone call date for phone call 3 the calculation would be:

                         

                        Let (

                        [

                        setdate = Phone2;

                        firstDayOfMonth = Date ( Month ( setdate ) +1 ; 1 ; Year ( setdate ) );

                        daynumber = DayOfWeek ( firstDayOfMonth )

                        ]

                        ;

                        Case(

                        //Monday + 3 days = Thursday + 14 days = 3rd Thursday of Month

                        daynumber = 2 ; firstDayOfMonth + 3 + 14;

                        //Tuesday + 2 days = Thursday + 14 days = 3rd Thursday of Month

                        daynumber = 3 ; firstDayOfMonth + 2 + 14;

                        //Wednesday + 1 days = Thursday + 14 days = 3rd Thursday of Month

                        daynumber = 4 ; firstDayOfMonth + 1 + 14;

                        //Thursday + 0 days = Thursday + 14 days = 3rd Thursday of Month

                        daynumber = 5 ; firstDayOfMonth + 0 + 14;

                        //Friday + 6 days = Thursday + 14 days = 3rd Thursday of Month

                        daynumber = 6 ; firstDayOfMonth + 6 + 14;

                        //Saturday + 5 days = Thursday + 14 days = 3rd Thursday of Month

                        daynumber = 7 ; firstDayOfMonth + 5 + 14;

                        //Sunday + 4 days = Thursday + 14 days = 3rd Thursday of Month

                        daynumber = 1 ; firstDayOfMonth + 4 + 14;

                        )

                         

                         

                        )