Find Multiple Holiday Dates

[crazybake]

I am sure this is a structural issue on my part, so if that is where the changes need to be, I'll start with that.

 

My solution involves checking out equipment to students. I created a holidays table with all global fields. To make it easier for the operator, I created a separate field for each known national holiday (approx 7) and a popup calendar, so they can set the values. I also created fields (8) for the start and end of each term and last, there are (5) custom dates fields, again each with popup calendars- Is there a better way?

 

I was able to create calculations fields and scripted finds during the checkout process to set due dates, so they don't conflict with these dates and weekends.

 

However, part of the solution involves reserving items in advance, so I need to be able to check against all of the dates for when the student wants to reserve the item (not the due date). My only thought is to create a script which will find against (20) fields and make sure the reserve date doesn't conflict? Help here?

 

My other idea was when a student tries to reserve it sets the due date first, then subtracts - but could not wrap my head around it.

[eos]

My solution involves checking out equipment to students. I created a holidays table with all global fields. To make it easier for the operator, I created a separate field for each known national holiday (approx 7) and a popup calendar, so they can set the values. I also created fields (8) for the start and end of each term and last, there are (5) custom dates fields, again each with popup calendars- Is there a better way?

 

Create a holiday table that has a date field, and maybe a description (what kind of holiday) – that's it. Every holiday in every year now is represented by its own record. Also I'd recommend designing all those transaction records (reserving, lending etc.) as – well, transactions, within their own table.

 

Now your script just needs to check one transaction date against the holiday table, instead of 20 fields.  

 

As a rule of thumb: Whenever you find yourself designing tables with only few records and many (similar) fields, it's almost always the wrong way to do it.

[crazybake]

Makes sense. -

To have them all on one page would I then create a self join that has all of the descriptions in a portal with the date filed next to it as a calendar dropdown? Or?

[eos]

No need for it to appear in the UI. Actually, you update your Holidays table once a year, then you forget about it for the next 52 weeks and simply use it as a lookup resource in calculations.

btw, Holidays is just a catch-all-term; there's nothing stopping you from putting other relevant dates in there, like your term start and end dates.

 

Let's assume you have your date (however it got into the system) and want to check if it's a holiday or a weekend. Why not create a nice CF putting the Holidays table to work?

(I guess there's a gazillion such CFs already out there, but it never hurts to write one yourself at least once.)

 

dateIsHolidayOrWekeend ( myDate ) =

 

Let

 

    ( [

   myDayOfWeek = DayOfWeek ( myDate ) ;

   dateIsWeekend = myDayOfWeek = 7 or myDayOfWeek = 1 ;

   holidayList = List ( myHolidayTable::date ) ;

   dateIsHoliday = not isempty ( FilterValues ( holidayList ; myDate ) ) // date appears as a value within the list of all holidays (but note below)

    ] ;

 

  dateIsWeekend or dateIsHoliday

 

  )

 

Obviously, this uses a relationship to the holidays table, which you could filter by current year; or use ExecuteSQL (with or w/o a filter like BETWEEN) to retrieve a list of the relevant holidays without setting up a relationship.

 

Simply feed the desired date to this custom function; if it evaluates to true, the date is not viable. Use it for Conditional Formatting, or in a script, etc. I guess you don't particularly care which holiday it is, or if it just a weekend. If you do, let me know.

 

btw, watch out for some pitfalls when using dates in comparisons; e.g. FM accepts a date with or without leading zeroes, and while both versions are the same date, they're not the same string, and FilterValues() wouldn't work.

Either make sure that all dates are entered consistently (a worthy goal in itself), or compare the numeric value of the desired date with a list of the numeric values of the dates.

 

In this here case, you'd need to create a GetAsNumber ( date ) calc field in the Holidays table, because FM doesn't let you retrieve a list and work at the individual values at the same time –

List ( GetAsNumber ( myHolidayTable::date ) ) just balks at you.

 

If anyone has a better idea around the date format / string conundrum, I'd like to hear about it.

[crazybake]

Hmm - I will have to break down the function into parts to understand the process. On a side note - 

 

No need for it to appear in the UI. Actually, you update your Holidays table once a year, then you forget about it for the next 52 weeks and simply use it as a lookup resource in calculations.

 

 

The idea was to make it really easy by having a user (a work-study student who is brand new, for example)  select the dates for all holidays, breaks, and the start/end of each term in one layout at the beginning of the academic year - I was hoping to do this by simply having a list of all of the major holidays, then have calendar dropdowns next to each item listed above with the ability to add other holidays/dates.

[eos]

It would be even easier to copy a list from the web, clean it up a bit, add your specific off- and special event dates, then create/update your table with these data; why enter it all manually? Maybe create a simple interface – button opens new window, goes to Holiday table, finds all holidays for current year, sorts – to allow your guy to check if a certain date is missing and if so, enter it manually.

 

As long as you don't create one holiday record per year with one field per holiday  , you really can't be that far off the mark.