DISTINGUISH WORKING VS NON-WORKING DAYS/HOURS: PERFORM CUSTOM ACTIONS WITHIN CRM BASED ON THE RESULT

INTRODUCTION

It is highly probable to encounter this business requirement atleast once in any Microsoft Dynamics CRM developer’s life time. If you are an upcoming developer, you will face this requirement for sure. Heads up! The main intention of this post is to enlighten you of how to achieve this business requirement using only out of box features & workflows instead of writing a plugin code. Though it requires some deep customization skills & writing structured workflows, in the end you are going to achieve this business requirement without writing a single piece of code.

WORKING VS NON-WORKING DAYS BUSINESS REQUIREMENT

The common business requirement we receive from our business owners is to perform one set of activity if today is a working day (weekday) and within office hours. In opposite to that, we need to perform other set of activities if today is non-working day (weekend or public holidays) or outside of office hours. What ever may be the activity, the final goal is to find if today is a working day within office hours or non-working day. Our business requirement is once a case is created, first calculate if today is working day or not and use a working day email template to send email to case customer. Suppose today is not a working day, then we need to use non-working day email template to send email to case customer.

DESIGN ARCHITECTURE

The whole business requirement can be segregated into 3 segments:

1. Working Hours (8am to 5pm) vs Non-Working hours
2. Weekdays (Monday,……Friday) vs Weekend (Saturday, Sunday)
3. Public Holidays (Christmas, independence day) vs Normal days

We need to calculate to which group does current date & time belongs to in all the 3 three segments. Based on the results, we can decide if current moment is working or not and use respective email template to mail customers. The only permutation when present date & time is working: 1^st segment is working hours, 2^nd segment is weekdays, 3^rd segment is Normal days. For any other permutation & combination other than the before listed scenario, present date & time is non-working.

CUSTOM ENTITY TO CAPTURE HOLIDAY SCHEDULE

The first question that comes to every one’s mind is where do we capture holiday schedule or list of holidays during a calendar year? Well dynamics CRM have give an out of box entity called “Holiday Schedule” (Settings -> Service Management -> Holiday Schedule) where you can create a record in that entity for current calendar year and enter list of holidays for that year.

But the problem here is if you want to check the list of holidays from that entity, the only supported way is through writing a plugin or custom workflow to retrieve the list of holidays data. You can follow below link for code reference:

https://salimadamoncrm.com/2013/05/21/retrieving-business-closure-dates-from-dynamics-crm/

But this post aims at achieving this requirement using only out of box features rather than writing plugin code. As a result, we have created a custom entity and named it “Duplicate holiday schedule”. Inside that entity, we created 10 date time fields to capture regular US holidays like Memorial Day, Independence Day, thanks giving day Christmas etc. and 10 miscellaneous custom date time fields to capture sudden business closure days or variable holidays in a year. Remember to create only date time fields and while entering time set it to 12 am and date as required. One important point to keep in mind is every year starting you need to visit this entity and change the date keeping time as 12 am.

We also created 2 more custom fields:

1. “Current Date” field is of type Date type
2. “Is Holiday BR” is a text field

The significance of both these fields will be discussed soon.

DISTINGUISHING WEEKDAY VS WEEKEND 

The next task is to distinguish whether current date is a weekday or weekend. For this we made following customizations in case entity:

1. Create a field “Reference Date” which is of datatype Date Time and behavior of field is Date only in case entity. This field is always constant, and we populate this field with a fixed reference date say for example 01/01/2018. This reference date is fixed for ever and is not meant to change. Based on this reference date only we will calculate the difference between current date & reference date and come to a conclusion on whether today is weekday or weekend.
2. “Present Date” custom field is also of datatype date time and behavior of this field is date only. This field is populated from our custom workflow which we will discuss soon. It contains the today’s date.
3. “Week Number” is a calculated field and datatype is whole number. The calculation we are performing in this field:

Week Number = (DiffInDays(Reference Date, Present Date) + 1)/7

1. “Week Day” is a calculated field and datatype is whole number. The calculation we are performing in this field:

Week Day = (DiffInDays(Reference Date, Present Date) – (Week Number * 7))+ 1

The result is stored in week day field. Based on the reference date I have chosen 01/01/2018, if the week day calculated field is -1 then today is Saturday, 0 is Sunday. 1 is Monday, 2 is Tuesday, 3 is Wednesday, -3 is Thursday and -2 is Friday.

The above values are calibrated for reference date of 01/01/2018. If you set reference date to 2017 or 2016, above integer values for week days will obviously change.

BUSINESS RULE TO FIND PUBLIC DAY VS NORMAL DAY

In the above step we have already found out whether today is weekday or weekend. The next segment we need to find is if today is a public holiday or not. To achieve this, we have already created a custom entity “Duplicate Holiday Schedule” where we will enter the list of holidays during a calendar year. Now the business rule is designed to compare todays date with the existing set of dates in the entity. If there is a match, then today is public holiday. Or else today is normal day.

I have developed a business rule on Duplicate holiday schedule entity whose scope is set to entity (meaning business rule is run on server side). In this business rule we will be using before mentioned 2 custom fields: “Current Date” & “Is Holiday BR”.

The custom out of box workflow which we are going to discuss soon will populate the “Current Date” field on “Duplicate Holiday Schedule” entity with todays date (time is set to 12 am because behavior of the field is date only). My business rule will check if the current date field matches any of the custom holidays fields date (screenshot of all custom holiday fields is attached above). If there is a match, then Is Holiday BR text field is set to Yes or else it is set to No. Based on the value in Is Holiday BR field, we can easily decide if today is a US Public holiday or a normal day.

FINAL WORKFLOW TO EXECUTE ABOVE STEPS

After performing all necessary customizations for case entity, we have certainly arrived at final step i.e developing an out of box workflow which joins all these pieces together to determine if today is working or non-working day. Screenshot of the workflow attached.

This workflow is the heart of whole business requirement because it does all required calculations, updates necessary fields, triggers certain processes and finally send email to case customer.

1. Attach current case to parent Duplicate holiday schedule record

This is the 1^st step in workflow. It is updating the case record. The following are the actions performed while updating case record

a. Populate reference date field on case record: It populates the reference date field with value 01/01/2018.

b.  Populate present date field: The present date field on case record is populated with today’s date.

c.  I already created a lookup field called “Holiday Schedule” on case entity. This will establish 1:N relationship between Duplicate Holiday Schedule entity and case entity. And if you remember, in that duplicate holiday schedule entity I have created a single record where we enter all holidays in a year. This record can also be called configuration record because the only purpose of this record is to support some other entities & workflows while storing only configuration related data. Now I am entering that single configuration record as a lookup on my case record. For every new case record created, it is associated with parent record (Duplicate holiday schedule record). This way I am establishing a connection between the newly created child case record and parent duplicate holiday schedule record. The main reason for establishing this connection is to find if the case created on date (today’s date) matches to any of the list of US public holidays (duplicate holiday schedule record contains this list) to find if today is a public holiday or normal day.

2.  Get the current date and clear Is Holiday flag

This is 2^nd step in workflow. It is Updating the duplicate holiday schedule entity. The following are the actions performed while updating it:

a. Clear the field “Is Holiday BR” and populate the “Current Date” field with today’s date.

One very important point to note here is that we already have a business rule on this entity with scope set to entity (i.e business rule will run on server side and any changes to the fields in that rule will trigger the rule). Since we are populating the current date field, it triggers the business rule. The rule will compare the current date field with list of holidays available in the record. If there is a match, “Is Holiday BR” field is set to yes or else it is set to no.

3. Calculate starting & ending hours of present day

This is 3^rd step in the workflow. It is again updating the case record. The following are the actions performed inside this step:

a. I have already created 2 more fields on case entity to capture the business starting time & business end time of current day. The 1^st field is Starting hours which is of data type date & time. And 2^nd field is Ending hours field which is of datatype date & time. Since my business working hours are from 8:30 am till 5:00 pm and present date field has todays date with 12:00 am, starting hours field is populated with present date + 8.5hrs, ending hours field equals present date + 17 hrs. Thus end result is starting hours field equals todays date with time 8:30 am, ending hours field equals todays date with time 5:00 pm.

4.  Check if case is created outside working hours or on weekends

This is the 4^th step in workflow. This step will check a condition and send email to case customer. The condition is if the case created on date is less than starting hours or case created on date is greater than ending hours or case week day calculated field equal -1 (Saturday) or case week day calculated field equals 0 (Sunday). If any of the above condition turns out to be true, it implies case is created on either during non-working business hours or on weekends. So we are using non-working hours email template to send email to case customer.

5.  Wait until business rule executes calculation and fills Is Holiday field

This is the 5^th step in workflow. It is a wait condition to check if the parent Duplicate holiday schedule record’s Is Holiday BR field is not empty. If you look at 2^nd step in the workflow we have cleared the Is Holiday BR field and populated the current date field. I also mentioned that changing the current date field will trigger business rule and it does necessary calculations to populate Is Holiday BR field with yes or no. Until then the field remains null. 5^th step in the workflow will wait for business rule to complete its execution and populate the Is Holiday BR field.

6.  Check if case is created on US Public holidays (as per Duplicate holiday list)

This is the 6^th step in workflow. After the parent Duplicate Holiday Schedule record’s Is Holiday BR field populated with a yes/no value, the condition is evaluated. If the Is Holiday BR field is yes, then it means today is a US public holiday and we use non-working email template to send email to case customer.

7.  Send automatic E-Mail regarding case creation using working day template

This is the 7^th step and final step in the workflow. If all the above conditions turn out to be false, it means case is created on weekdays, during working hours and on a non-US public holiday. Now we use working day email template to send email to case customer.

PRECAUTIONS TO BE TAKEN

Almost all the custom fields created of datatype date & time are of behavior date only. It is very important to set the behavior as date only because such fields will only der date and the time is automatically set to 12 am as per user’s time zone. The reason behind selecting date only fields is:

1. Date only fields can be easily compared with each other. Suppose you want to find if present date field’s value is similar to any of the US public holiday fields listed in Duplicate holiday schedule entity. In such scenarios, we must make all these fields of date only behavior so that time field is set to 12 am for all these fields and thus we compare only dates.
2. In order to capture todays date from the workflow, we are setting the present date field to Process execution time. Since present date field is of date only behavior, present date field gets populated with todays date and time remains 12 am.
3. Also once present date field is calculated, we can easily find starting hours field value by adding 8.5 hrs and ending hours field value by adding 17 hrs to present date field.