Calculating Future Dates

[snickwit]

Hi all,

I need help setting up calculations working with dates for a non-profit community school. I am developing a course listings database which lists courses and holds fields such as: start_date, end_date, number_of_weeks, days_of_the_week, number_of_class_sessions as well as others.

I need to generate calculations that have the ability to list all the possible dates for a course. For example, let's say there are two classes:

A mini-course which runs for 1 week based on 5 days meeting Mon, Tu, Wd, Th and Fri. Therefore, this class has 5 total class sessions.

A course which runs for 16 weeks based on twice a week which meets Mon and Wed. Therefore, this class has a total of 32 class sessions.

I have this formula to calculate the "number_of_weeks":

Int((end_date - start_date)/7)

"Day_of_the_week" = repeating field which holds all the days of the week the course can be hosted on.

"Calculate_day_of_the_week" = corresponding daily number based on "day_of_the_week" using a case formula:

Case(days_of_the_week= "Monday", 2,

etc. etc. etc. days_of_the_week= "Sunday", 1)

"Number of days" counts how frequently a course is taught within a week such as if a course is offered once a week or twice a week using this formula:

Count(days_of_the_week)

"number_of_sessions" = total number of sessions that class run such as 16 week class meeting twice a week equals 32 using:

Count(days_of_the_week) * number_of_weeks

However, I cannot seem to build a smart functionality to start with first day of class and increment by next class meeting date and continue to the end_date so I can automatically generate our class attendance roster sheets. My goal is to build layouts which can list attendance sheets based on number of weeks per course.

Is there a way to generate a formula based on number of weeks and add +7 to each date incrementing by number of weeks? Or perhaps, there is a better method? Such as if a class starts 1/5/2004 and meets for 2 weeks on Mondays and Wednesdays

[ESpringer]

I have struggled with this as well...

The best I could do (if I remember!) was to set up a script that loops along one date at a time, starting with the start date in a global counter field and advancing the date by one day at each step, and checking to see whether it has the correct day of the week to be included on the date list for the course, and IF SO, to append it and

[snickwit]

Hmmm, not sure I follow exactly what you mean by looping one date at a time. I'm still fairly new to developing databases so perhaps, I am missing something here? Do you have a sample of what it might look like or what the script should look like? I'm not sure how to run the append within Filemaker, is it similar to a dos command: >>?

I'm starting to revist and wonder if it would be easier to generate a script that would automatically import this data into an outlook calendar instead. The problem with that is all items would be in one calendar. If I could do a calendar for each room ...somehow get it to script to CSV and import to the appropriate room calendar ...that would eliminate all my needs to build any kind of "smart" calendar features within FileMaker. Are you familiar with anything like this? I saw a blurb on google groups recommending this solution:

http://www.datavations.com/dvpages/products/fmcomponent_outlook.cfm

Thanks in advance for your help,

S