Monday, August 13, 2012

Check if the date is a business day in CRM2011

In CRM solutions, some times we need to know that  a date is working business day. For example a company policy can be a complaint must be closed within 5 working days. In this scenario, we need to update the working days on the case entity at the end of every business day. One of the solution to this scenario can be to write a console application and schedule it to run every night and update the working days.
I have written this code to check if current date is a business day. The code is checking if the date is a weekend or a “Business Closure” day defined in CRM. Here is the code. Make the changes according to your requirements.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;

namespace dateCheck
    class dateCheck
        static void Main(string[] args)
            //Date to be checked 
            DateTime dateBeingTested = DateTime.Now;

             if (IsItWeekend(dateBeingTested) || CheckHolidays(dateBeingTested))
                 Console.WriteLine(dateBeingTested.ToString("d") + " is not working day.");
                 Console.WriteLine(dateBeingTested.ToString("d") + " is a working day.");

        static bool IsItWeekend(DateTime date)
            if ((date.DayOfWeek== DayOfWeek.Saturday) || (date.DayOfWeek==DayOfWeek.Sunday))
                return true;
            return false;          

        static bool CheckHolidays(DateTime date)
            var connection = new CrmConnection("Crm");
            var service = new OrganizationService(connection);

            QueryExpression query = new QueryExpression("calendar");
            query.ColumnSet = new ColumnSet(true);
            ConditionExpression condition = new ConditionExpression();
            condition.AttributeName = "name";
            condition.Operator = ConditionOperator.Equal;
            condition.Values.Add("Business Closure Calendar");
            EntityCollection calendars = service.RetrieveMultiple(query);
            EntityCollection calendarrule = calendars[0].GetAttributeValue<EntityCollection>("calendarrules");
            return calendarrule.Entities
            .Where(e => ((DateTime)e["starttime"]).Date == date.Date).Any();


To make this code works, we need to define a connection string “Crm” in app.config file. Change the server name and credentials to match your deployment.
<?xml version="1.0"?>
    <!--<add name="Crm" connectionString="ServiceUri=http://servername/orgname; Domain=domainname; Username=username; Password=password"/>-->
    <add name="Crm" connectionString="ServiceUri=; Username=username; Password=password; DeviceID=deviceid; DevicePassword=password"/>

Happy Programming…Thanks Russel for the code.


  1. While you're there, use some linq, rather than the loop at the bottom.

    return calendarrule.Entities
    .Where(e => ((DateTime) e["starttime"]).Date == date.Date

  2. Thanks for the feedback buddy. The code is written by a Ist year uni student. I believe that he did a good job for a student.

    Anyway you missed a bracket after date.Date.

    I do appreciate the feedback and updated the code.

    Thanks again.

    1. I thought the author (Amreek) was trying to convey a point rather than giving us a lesson about LINQ. I guess Amreek you have managed to successfully explain how to retrieve the holiday list from CRM and it is up to us to follow any convention we like. I'd still stick to the old lovely For Loop as I don't see that much harm/difference anyway.

      On the other hand and if you (coder) really want to use LINQ anyway, then you may need to consider a more readable LINQ query so it can be easily understood and interpreted (as well as less conversion and shorter syntax). Your code should be:

      return calendarrule.Entities
      .Any(e => ((DateTime)e["starttime"]).Date == date.Date);

      In your version of the code... you are saying for a list of calendar rule entities, retrieve me a list of entities that their startdate == date.Date and then return true if this list has any value.

      My version of the code... I'm saying for a list of calendar rule entities, return true if any of the records has a startdate == date.Date

      Thanks Amreek.... your post is really great as usual.

  3. Hello Mr. Singh

    Thanks for the nice blog. I tried the code and get 'access denied' error while stepping through the below line
    EntityCollection calendars = service.RetrieveMultiple(query);

    App.config has following defiiation and username/password is valid windows login. Don't know what should be the value for DeviceId/DevicePassword.

    Below are the exception details.

    Server stack trace:
    at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)
    at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
    at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs)
    at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
    at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]:
    at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
    at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
    at Microsoft.Xrm.Sdk.IOrganizationService.RetrieveMultiple(QueryBase query)
    at Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy.RetrieveMultipleCore(QueryBase query)
    at Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy.RetrieveMultiple(QueryBase query)
    at Microsoft.Xrm.Client.Services.OrganizationService.<>c__DisplayClass22.b__21(IOrganizationService s)
    at Microsoft.Xrm.Client.Services.OrganizationService.InnerOrganizationService.UsingService[TResult](Func`2 action)
    at Microsoft.Xrm.Client.Services.OrganizationService.RetrieveMultiple(QueryBase query)
    at TESTCRM.Program.CheckHolidays(DateTime date) in c:\Projects\CRM\TESTCRM\TESTCRM\Program.cs:line 57
    at TESTCRM.Program.Main(String[] args) in c:\Projects\CRM\TESTCRM\TESTCRM\Program.cs:line 26
    at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
    at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
    at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
    at System.Threading.ThreadHelper.ThreadStart()

    1. This comment has been removed by the author.

    2. I am trying to paste app.config values but it is not publishing. I have used testcrmserver\aniJoshi as userID and testpwd is the password (they are correct windows logins). DeviceID and Devicepassword is the same as from your code.

    3. Check the serverURI in app.config is exactly same as defined in the deployment manager.