ramanjjilunaidu
Forms Fledgling

Exclude weekends and public holidays and send notification

Jump to solution

Hi Team,

I want to send alerts to manager every day for three days after submitting a new item in list. But here i have a requirement that i have to exclude weekends(Friday & Saturday) and public holidays some i have given below.

New Year's Day Fri, 1 Jan 2021 
Eid al-Fitr Eve 2 May 2021
Commemoration Day 30 Nov 2021
National Day Thur, 2 Dec 2021 (here i have exclude thursday, friday & Saturday), if reminder date is falling on 2nd Dec, email should trigger on 5th Dec.)

 

Any suggestions would be highly appreciated.

Regards,

0 Kudos
Reply
13 Replies
kunalpatel
Automation Master
Automation Master

Re: Exclude weekends and public holidays and send notification

Jump to solution

@ramanjjilunaidu can you try below approach?

 

  1. Create a list for Holidays, you will have to keep this updated
  2. Query the list and filter date column of the list with your date and store it in collection
  3. Using collection operations, get the count of that collection
  4. Using Format Date function, get the day of the your date
    1. Add Run If and condition will be day in #1 is not Fri or day in #1 is not Sat or num_HolidayCollecCount = 0
      1. Send email alert to your manager
Reply
ramanjjilunaidu
Forms Fledgling

Re: Exclude weekends and public holidays and send notification

Jump to solution

Dear Kunal,
Thanks for your solution, since i am a newbie, second point is not clear for me, i would request you to explain in brief please....................

My Holiday list is as follows.

ramanjjilunaidu_0-1626156430291.png

And my filter is as follows and i don't know whether i have to take start date or end date column in filtering. LMFReminder is a calculated column(Created+1) in main list(not holidays list)

ramanjjilunaidu_1-1626157466925.png

 


thanks in advance.

0 Kudos
Reply
kunalpatel
Automation Master
Automation Master

Re: Exclude weekends and public holidays and send notification

Jump to solution

@ramanjjilunaidu in your holiday list why you have start and end date? If holiday is on a certain day then all you need is the date when that holiday falls.

 

Create 2 columns i.e. Date of Holiday - Date Type and Holiday as SLT

 

e.g. 12/24/2021 - Christmas Eve

       12/25/2021 - Christmas

       12/31/2021 - New Year Eve

       01/01/2022 - New Year

 

Now when querying holiday list get ID collection and apply filter Alert_Date = Date of Holiday? If that is not same then collection will be empty.

 

Now for 2nd part i.e. for weekends...

 

  1. Create a SLT variable i.e. slt_Alert_Day
  2. Using build string action use below formula and store it in above variable
    formatDate(Alert_Date, ddd) - this formula will give the day of Alert_Date in 3 letters, you can use dddd to get the full spelling
  3. Add Run If action and configure by adding below 3 filter conditions using OR operations
    1. slt_Alert_Day not equal Fri
    2. slt_Alert Day not equal Sat
    3. num_Holiday_Count = 0
  4. Inside this run if action add your email notification
Reply
ramanjjilunaidu
Forms Fledgling

Re: Exclude weekends and public holidays and send notification

Jump to solution

@kunalpatel ,

1. I am getting Holiday Count(num_Holiday_Count) blank as below.

ramanjjilunaidu_0-1626258286085.png List query is as follows

ramanjjilunaidu_1-1626258371822.png

2. Even though my holiday on 15 July 2021 and alert date is 15 July 2021, email getting generated.

ramanjjilunaidu_2-1626258801329.png

ramanjjilunaidu_3-1626258839106.pngi have a holiday on 15 which is falling on thursady still email is triggering

My Run If condition is as follows.

ramanjjilunaidu_4-1626258954496.png

 

Regards,

 

 

0 Kudos
Reply
ramanjjilunaidu
Forms Fledgling

Re: Exclude weekends and public holidays and send notification

Jump to solution

Dear All,

No one is there to help me out, can anyone guide me please..........

Regards,

0 Kudos
Reply
ramanjjilunaidu
Forms Fledgling

Re: Exclude weekends and public holidays and send notification

Jump to solution

@cassymfreeman can you please help me as i am getting date collection blank and query list is as above.

Thanks in advance.

0 Kudos
Reply
kunalpatel
Automation Master
Automation Master

Re: Exclude weekends and public holidays and send notification

Jump to solution

@ramanjjilunaidu after using Query List, did you use the Collection Operations to count the collections and store it in the number variable?

0 Kudos
Reply
ramanjjilunaidu
Forms Fledgling

Re: Exclude weekends and public holidays and send notification

Jump to solution

@kunalpatel 

Dear Kunal, i would request if you can create NWF(i hope it won't take more than 10 mns) and provide me the solution(attach it here). Appreciate if you can do this please........................................................

Thanks in advance.

Regards,

0 Kudos
Reply
kunalpatel
Automation Master
Automation Master

Re: Exclude weekends and public holidays and send notification

Jump to solution

@ramanjjilunaidu  PFA...my 2 cents...you won't learn unless you won't try to troubleshoot.

0 Kudos
Reply