8 Replies Latest reply on Jul 31, 2015 2:44 AM by greatgrey

    Skipping Weekends and School Holidays


      I apologize if I ask too many questions. I hope there's no required ratio of questions to contributions on this board.


      My school uses our database, called the KIS (KIPPster Information System) to enter behavior consequences, aka detention. The administrators sometimes assign a kid multiple days of detention after school. I have a script that simply takes the number of instances they want the kid to be on (how many days) and creates a record in the behavior table that number of times, looping until the looped variable reaches the number of days the admin puts in.

      IF an adminstrator chooses 5 days, it will loop through 5 times, writing a record on each loop. This script skips Saturday and Sunday but checking to see if the DayName() of the newly-created record is a saturday and then re sets the date adding 2 days. It skips sunday by adding 1 day to the date. I feel this is clumsy, but i know of no other way.


      One of the problems is dealing with holidays, no school days. How do I get the database to skip those days? I assume a 'no school days' table is involved, but i'm not sure Is there a relationship involved as well? How do i get the script to look at the day it just created and check to see if it is a no-school day, and reset the field as the next day?


      Is it a matter of looking up the date just set in the Detention::Date field to see if it matches a no school day?


      Can the same thing be done for the weekends, or is my method okay?




        • 1. Re: Skipping Weekends and School Holidays

          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 =




          returns true.

          • 2. Re: Skipping Weekends and School Holidays


            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.

            Thanks again.


            \\\\ EDIT:

            Okay it was a lack of brain function. The script and that step was working fine


            Thanks Michael.

            Your advice really gave me an 'window' into how to user FM more.




            • 3. Re: Skipping Weekends and School Holidays

              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


              BusinessDays(startDate;endDate)  http://www.briandunning.com/cf/1364 

              BusinessHolidays(year)  http://www.briandunning.com/cf/1363


              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.





              • 4. Re: Skipping Weekends and School Holidays

                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...

                • 5. Re: Skipping Weekends and School Holidays

                  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.

                  • 6. Re: Skipping Weekends and School Holidays

                    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.

                    • 7. Re: Skipping Weekends and School Holidays

                      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.

                      • 8. Re: Skipping Weekends and School Holidays

                        Well it's still a good point for others who may face the same type of problems.