If you have a table of SchoolHolidays, and a relationship to this table using the x relational operator, you can test if a date is a school holiday by =
not IsEmpty ( FilterValues ( yourDate ; List ( SchoolHolidays::Date ) ) )
Alternatively, you could put the date in a global field and define the relationship as matching the global field to the Date field in SchoolHolidays; then the date is a holiday if =
Thanks for the advice. I tried this earlier and cant get it to work. I'm sure it was a lack of brain functioning that caused the issue.
I understand the x relationship - all records are related to all records in the other table. When I set up a field to do the List(), that worked, but i can't get the filter values to return the date that equals a given date in the behavior table.
Actually something just occured to me, Ill try something and get back to you.
Okay it was a lack of brain function. The script and that step was working fine
Your advice really gave me an 'window' into how to user FM more.
I need a similar calculation for some of the production programs I develop. In my case they are working days rather than school days, but no less arduous ;-)
Try the custom function shipArrivalDate ( startDate ; numberOfDays ; saturday ) http://www.briandunning.com/cf/721
If you pass this function the start date and the number of days, with the last parameter set to "Y", it will return a date in the future which has skipped weekends.
You might also want to look at two other functions
Business Days give you the number of working days between two dates. It depends on the BusinessHolidays function below which will provide a list of all the normal national holidays for a given year. If you need any help in adding your school holidays into the BusinessHolidays function, let me know.
I know this is an old thread so I am offering this to any belated reader:
Why not create a table of days/dates. One for each day of the year.
Each date would have a flag for use me or don't use me. You could create many flag fields for various purposes.
A field could have an auto enter calc such as if saturday or sunday, 1. Standard holidays, 1. Etc.
The you could manualy set a 1 for certain dates such as Teachers Day Off, Rocket Launching Day, etc.
Now you have a complete calendar of days to refer to.
Create a relationship between the two tables based on date.
Enter your date in the record and lookup the value in a specfici field.
Using a calendar lookup related table is far more flexible than calculations and it also lets you create separate fields to lookup so Fred, Sally, Tom, etc could have their own calendar lookups fields.
In fact, I just talked my self into adding this to my own project...
I see two possible problems with your basic logic.
1) What if a student is absent?
2) School being closed due weather or other unplanned event.
It just that you need a way to edit such records or do something that checks for them being in attendant at school on day being used.
I do have another way of checking dates. Part one checks that day of week is one school would normally be open or closed depending how you write the test. Part two you make a list of closed dates manually entered with popup Calenders (as many dates schools are closed have nothing to do with holidays) and checks that date in question is not in list. It can be combined with the results from dates that can be automatically found.
I was never a fan of the detention system and never showed up. I do not think it is actually mandatory, but may depend on where you live.
1) If the student is absent the system should note the absence and flag for rescheduling.
2) This could be handled in the same way as being absent. If the student is not marked as attending the incarceration for any reason it is flagged for reschedule and / or review. It is my experience that the numbers of these students is generally small and there will always be someone reviewing the data regularly.
Unfortunately the system of which I spoke is no longer being used. It was a great one, and I'm dang proud of it, but the schools chose to do something else. Their lose.
I ended up dealing with your questions (I had them as well). For an absent student, the student was flagged as absent, and the system would push them to the next planned school day. For an unplanned day off of school, the system just rolled all detentions to the next school day.
It ended up working very well.
Well it's still a good point for others who may face the same type of problems.