Determine whether current day is public holiday

[Jan Cernocky]

Hi guys,

I am trying to find a way how to determine whether current day is within any holiday schedule.

Example:
- We have a schedule called "Holidays"
- We have schedule entries, a mixture of "does not repeat" and "repeat" occurences, e.g.
a) New Year's Day - repeat every year on Jan 1
b) Good Friday 2020 - Does not repeat, Apr 1,
etc.

What I would like to achive is to run a script that will determine whether today is a public holiday and throw some kind of a message (to agent processing ticket - e.g. "there is US public holiday today, please transfer ticket to another queue than usual...")

I assume I would query schedule entry table where schedule = "Holidays"

But how can I compare each record with today?
E.g. I have defined in the past that January 1 is New Year's Day but the Start date is 2020-01-01 00:00:00 and end date 2020-01-01 23:59:59 + repeat on every year.
I would assume this automatically switches to 2021 after the holiday has passed but it does not.
So I don't know where to find out that the next occurence is 2021-01-01.

The only solution I can think of is to
- grab current time and date
- search for schedule entries where current time/date is between start date time AND end date time (to get all data for current year)
- search for schedule entries where current time+month+day is between start time+month+day AND end time+month+day and have repeat set to "Every year" (to get all data from the past years but are repeating)

I believe there must be something easier to the solution drafted above.

Any thoughts?

[Allen Andreas]

Hi,

You could accomplish this with a display business rule perhaps?

You would just need to query the cmn_schedule_span table for active records (or records associated to a specific schedule you're using -- so you know they're active and good to use), then just get the month and day from the start date time field. I don't think there's holidays that are only 6 hours long, etc.? So you can ignore any time usage.

And just do a simple comparison if current month and current day equals the query current day and time, then do 'x' (provide a message?) assigning it to business rule scratchpad.

Then on the record form, create onLoad client script to then check if scratchpad contains a value or not, if so, show g_form infomessage with business rule scratchpad verbiage.

I guess things get a bit more complicated when it's something like: every 3rd Friday of March...so you'd have to do a bit more there, but it doesn't seem like out of box there's anything that can quickly do this for you. So there'd definitely be some scripting going on here.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Jan Cernocky]

Thanks Allen,

yeah I think there won't be any issue to display the message if I find a proper way to get the result from the query.

The biggest issue I see that data are not "consistent" from my point of view. Example in the schedule entry [cmn_schedule_span]:

Entry	Date	Repeat
New Year's Day	2020-01-01	every 1 year
Good Friday 2020	2020-04-10	no repeat
Good Friday 2021	2021-04-02	no repeat

So I would not only have to check the month and day but also the year and whether it repeats.

Basically I can think about 2 scenarious in my case - fixed dates that repeat every year (single record with repeat yearly) and flexible dates (pretty much only Easter in our country) - and these can be scheduled in advance for several years upfront.

For the repeating records - I cannot read from anywhere that there is public holiday on 2021-01-01 unless I really calculate the future date based on the record values (repeat is enabled AND repeat until >= current date). That is my concern.

If there was a simple table record anywhere saying "next occurence is 2021-01-01" that would make things much easier.

[Allen Andreas]

Yeah, you may need to build that out (add new column to schedule table and do the calculation there), but I can definitely see what you're saying.

I looked around the community too and there doesn't appear any obvious threads previously talking about this.

[Allen Andreas]

Hi,

I just wanted to check in on this. If my reply helped guide you correctly, please mark it as Helpful & Correct.

Thank you!