Solved

Exclude weekends and public holidays and send notification


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,

icon

Best answer by kunalpatel 15 July 2021, 19:01

View original

13 replies

@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










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.





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)





 




thanks in advance.

@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


@kunalpatel ,



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



 List query is as follows





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





i have a holiday on 15 which is falling on thursady still email is triggering



My Run If condition is as follows.





 



Regards,



 



 

Dear All,



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



Regards,

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



Thanks in advance.

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

@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,

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

@kunalpatel 



Now i am getting Zero even though i have one item which equals to Date of Holiday = Alert Date







 



Regards

@kunalpatel 



Brother i am still getting zero count, please

@kunalpatel 



Please find the attachments for my two lists and workflow for your reference.





 



Regards,

@ramanjjilunaidu please see attached screenshots for configurations.



 



Query List Configuration





 



ReminderList is same as your main list. I've not ran the workflow on 2nd item.







Reply