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

# How do I Automate the Collection of Reoccurring Dates?

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.

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

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?

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?

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?

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?

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?

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?

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?

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;

)

)