Determine number of absences elapsed between two dates

[Alex117]

Hi everyone! Glad to have found the forum, the tips here are amazing.

So I was wondering if maybe one of you could help me tackle this conundrum. I've searched several forums but I can't find a clear enough explanation.

I'm working on a database for a friend, based on school attendance. I have a Student table and an Attendance table. One Student can have many records in the Attendance table. So the relationship is a one to many from Student ---< Attendance.

The two tables have the following fields:

Student table

Name

Start date

End date

Attendance table

Date

Present

Each student enrolls in an educational program and begins classes on the date specified in the Start date field, the End date field is then automatically calculated to determine their graduation date.

Each day a student comes to class they personally clock in at the front desk and a record in the Attendance table is created, set to present status in the Present field. If a student does not show up to class that day and is Absent, by default a record will not be created in the Attendance table because they did not clock in at the front desk. (I have chosen to use this method because the professors tend to not do a good job at manually inputing attendance and mistakes are made more than often.)

How can I determine the number of Absences though a calculation field, using only the Present records that are normally created in the Attendance table when a student personally clocks in?

Using the example of a week, a student is supposed to attend classes 5 weekdays per se, but at the end of that week only 3 Present records exist in the Attendance table from personally clocking in.

5 weekdays - 3 weekdays (or 3 Present records) = 2 weekdays (or 2 Absences)

Now how can I perform this calculation accounting not just for a week, but for the period elapsed from the Start date field to the Current date? I want to be able to see how many "uncreated" or absent records a student has, from the Start date to the Current date, by comparing with the Present records that exist in the Attendance table.

Thanks for any help in the right direction, my left brain is exhausted.

Alex

Using FMP 12, OS X Mountain Lion, Novice/Beginner

[comment]

Obviously, you need to know the number of classes that occurred in the given period, then subtract the number of attendances. This should be relatively easy to do if classes are always following some fixed pattern, e.g. every Monday and Wednesday. However, in real life such regularity is rare on account of holidays, cancellations etc.

[Alex117]

Yes, thank you for your reply.

...you need to know the number of classes that occurred in the given period, then subtract the number of attendances.

I also have a field that contains all the weekday names (in a return-delimited list, like checkboxes) that a class will occur on.

This should be relatively easy to do if classes are always following some fixed pattern, e.g. every Monday and Wednesday.

The classes always follow the fixed pattern of weekdays that is set up in the return-delimited list mentioned above.

However, in real life such regularity is rare on account of holidays, cancellations etc.

Yes, that's why I also created a third related table containing Holidays/Cancellations as single records, with the dates they fall on.

I'm thinking I would have to count all the relevant weekdays (the weekdays specified on the return-delimited list) from the Start date to the Current date, and skip a day if it's a Holiday/Cancellation that is previously set up in the Holiday/Cancellation table.

I just can't figure out how to piece it all together, by using a calculation or script. I know this is a complex calculation, that's why I posted under the complex calculations and advanced scripts sub forum. I think I have all the data variables, just need help executing it. Maybe a smart Filemaker guru can help me take a crack at this. There's not much clear help on the web regarding this for a beginner.

[comment]

It's too bad you don't have the advanced version (or do you?), because then you could install a recursive custom function such as:

http://www.briandunning.com/cf/452

Perhaps something like this could work for you instead:

http://fmforums.com/forum/topic/32882-working-days/page__st__-20#entry152903

There's no need to skip holidays and cancellations - you simply subtract their count from the above.