Friday, May 27, 2011

CRM 2011 Metadata Browser

I am not sure how come it has been so long that I haven’t noticed there is such a cool tool out there, I just came across Rhett Clinton’s blog and saw a link to a few CodePlex projects he has shared. Among them, there is the Metadata Browser for CRM 2011. One of the coolest things about this tool is that it is implemented as a CRM solution that you install in your CRM organization and it integrates with the CRM application!

I’ve developed the CRM 2011 Metadata Browser as a Silverlight 4 application that is packaged as a Managed CRM 2011 Solution. This tool allows you to view metadata within CRM including Entities, Attributes and Relationships. The tool uses the 2011 endpoint Organization.svc/web WCF service and is accessible from the Settings area under Customizations in CRM once imported into CRM.

So what does it do?
It gives you a matrix of all the CRM entities with all the metadata. Then I was pleased to see that you can even dig deeper into the attributes and the relationships.

Why is that good?
It is true this information is already accessible OOB by other means, but this tool gives you everything in one page without having to build SQL queries or navigating to multiple entities in CRM to gather the same information. Many times we want to document the metadata for multiple entities in the CRM organization and this tool provides a comprehensive view of all the information you need in one page. I would love to see an “export to excel” feature or maybe the ability to produce a report from it.


He has also published two other tools to CodePlex for converting CRM4 to CRM2011 JavaScript and for building OData queries for CRM, great stuff, check it out here.

Tuesday, May 24, 2011

Scheduling recurring workflows in CRM

Quite often we have a business process that needs to execute recurrently every month, week, etc; but there is no Out-of-the-Box scheduling engine in CRM, so that leaves us with a few options:
  1. Host a service that implements the scheduling functionality and then have this service call into the CRM web service to perform actions regularly.
  2. Leverage the CRM workflow “wait” step.
  3. Alter the recurrence patterns of CRM asynchronous operations (unsupported).
I will explain the second option in this post. As an example scenario I have the following requirements: “The first day of the month and email should be received with a summary of all the accounts created last month”.

I will start with the assumption that I have already created a custom workflow activity that retrieves the accounts created last month and outputs the text necessary for my email. Now I need to follow these steps to make the workflow recurring:


1.  Define triggers:

Define the workflow as On-Demand and Child workflow (not automatic triggers)
  



2.  Define process:
 
Define the body of the workflow to represent the set of actions that must occur every month:



3.  Add the Wait:

Now you need a way to tell the workflow engine to repeat the process every month. To do so, I will first insert a wait step that will tell the workflow engine to wait for 1 month:


After the step, I need a way to “restart” the workflow. Since there are no while/for loops in the workflow designer, we must find another solution. I will simply create a recurrence pattern by having an “Execute child workflow” step which executes itself. Because the workflow is defined as a child workflow, it will call itself every month. After I activate the workflow it looks like this:



4.  Trigger the workflow once
 
The first time that the workflow executes, it must be triggered manually, after that it will schedule itself monthly from the time that it was executed manually. Since it is defined as an on-demand workflow, you can simply navigate to your primary entity and trigger the workflow manually.



5.  Verify

Once your workflow has been triggered for the first time, it should appear in the system jobs grid, waiting for the next month to resume execution:
Note that “month” is not the same as 30 days, it actually uses the calendar month definition, since CRM workflow uses the .NET DateTime class. Therefore, if it was first triggered the 1st of the month, it will always trigger the 1st of the month regardless of the number of days in a month.


WARNINGS:
  • Every time you create a recurring workflow, you should carefully consider what resource consumption implications it will have. Recurring operations tend to utilize a large amount of resources which can cause lagging to the CRM Asynchronous processing service.
  • You must select a primary entity for your workflow which will never be deleted, otherwise the entire recurring workflow will be cancelled. For example, you can set the primary entity of your workflow to be systemuser and trigger it the first time regarding your own system user.
  • There is a loop detection mechanism in CRM which would cancel processes/plugins that create infinite loops as the one above. The maximum depth for a recurrence/loop is 8; however, the depth is reset after 10 minutes of inactivity. Therefore, the workflow above should work fine because it has 1 month of inactivity so each time it executes the depth will be reset and it will never be cancelled by the loop detection mechanism.

    Friday, May 20, 2011

    Processes/Workflow ownership FAQs

    I decided to post a blog article about this topic since I have been asked numerous times why solution import fails sometimes when trying to update an existing process. Before I get to the bottom of it, let me answer what are in my experience the most FAQ about process ownership:

    Q1: Under what user’s context does the workflow execute? (If the workflow creates a record, who will be the owner of that new record?)

    A1: It depends. Automatically triggered workflows (such as a workflow that triggers on account create) will execute in the context of the owner of the workflow. Therefore, if you have a send email step, the email will be by default sent from the e-mail account of the workflow owner. This is important to consider because the workflow owner might belong to a different business unit and have different privileges than the user who triggered the workflow (e.g. who created the account). Let’s say your workflow creates a task each time an account is created. Depending on the privileges of the user, the task might be in another business unit and not visible to the user, therefore you should consider adding an “assign step” that assigns the new task to the owner of the account. Now, if the workflow is executed on-demand, the workflow will then execute in the context of the user who requests the workflow execution. Because dialogs are always on-demand then they always execute in the context of the user who started the dialog.



    Q2: Why does the process execute under different users depending on how it was started?

    A2: This was a design decision based on security considerations. You don’t want to inadvertently be sending emails and executing actions without knowing it because some other user decided it. Therefore, by having this different behavior we can guarantee that the user under which the workflow executes is always aware that a workflow is performing some actions on his behalf. For the automatic workflow case, the owner of the workflow is also the person who activates it and who selects the trigger mechanism and the workflow steps so it is OK if the workflow executes under that user’s context. For the on-demand case, a user is specifically requesting some actions to be performed on his behalf by a workflow so the user is fully aware of the workflow definition and that it will execute; therefore it is safe to execute the workflow under that user’s context instead of the workflow owner (who might not be aware that a user requests an on-demand execution).



    Q3: Why can’t I activate/deactivate someone else’s workflow, even if I am the system administrator?

    A3: For the same security reason as explained above. You want the workflow owner to explicitly acknowledge that a workflow will be activated and will perform some actions on his behalf. You would not want to allow another user (even the system administrator) to decide that some process should be executed on another user’s behalf. If you want to activate/deactivate someone else’s process you must first assign it to yourself.



    Q4: If I assign an activated process to another user, why does the user have to re-activate it?

    A4: Active processes cannot be modified so the system automatically deactivates them before assigning it to the new user. As per Q3 above, only the new owner will be able to re-activate the process.



    Q5: I am importing a solution that contains processes and it fails with this error message “The workflow cannot be published or unpublished by someone who is not its owner”. What is wrong?

    A5: If your solution contains a process that already exists in the organization and is activated then solution import will attempt to update it. In order to do so, it must first deactivate it. However, if the owner of the activated process is not the same as the user who is importing the solution, then deactivating the process will fail (see Q3). Therefore you have a few options to fix this problem:

    1.       Import the solution using the user who owns the activated process. This can be tricky, especially if there are multiple processes owned by different users which need to be updated by the solution import.

    2.       Verify which processes are included in the solution, and then find them in the organization, if you can find them and they are not owned by you then you must assign them to yourself. You can reassign them to the original user after you import the solution; however, you will have to ask the process owners to activate it themselves.


    Thursday, May 19, 2011

    Improving Async performance in one click

    With CRM 4.0 it was often the case that the Asynchronous processing service would start lagging due to the rapid growth of the AsyncOperationBase table in SQL getting to millions of records in just a few months. This was often happening because of the amount of workflows triggered in the system. For example, if you create a workflow that triggers every time an activity is updated, you might end up flooding the database with system job records very quickly.

    Fortunately, in CRM 2011 there is a simple one-click solution that helps maintain the size of the AsyncOperationBasae table. If you open your workflow definition and go to the Administration tab, you will find the “Workflow Job Retention” option. If you click on the checkbox, it means that every time this workflow job is executed AND if it succeeds, the system job will be deleted. Selecting that option is perfect for processes that you don’t need to audit and you just need the logic to execute. However, if you need to keep a history of which workflows have executed on which records you should not select this option to delete the workflow job on completion (note that this option is disabled by default).



    The same option is now available for asynchronous plugin steps as well:



    This allows you to pick and choose which workflows and plugins are worth storing as system jobs every time they execute and which ones you want to delete upon completion to save space!

    However, in order for the async auto-delete feature to take effect, you must first make sure that the “auto-delete” feature is enabled for the organization. There is a master switch that must be turned on:

    select BitColumn from OrganizationProperties where ColumnName = 'AsyncRemoveCompletedJobs'

    Note that you can have a different value in that setting for each organization. In CRM Online, this value is always 1 (enabled) for all organizations.

    You will also notice there is an 'AsyncRemoveCompletedWorkflows' setting in OrganizationProperties. This setting has been deprecated in CRM 2011 so you can safely ignore it, since it has been replaced by the AsyncAutoDelete attribute in the workflow definition (Workflow Job Retention option).

    Tuesday, May 17, 2011

    MS Dynamics CRM 2011 Launch Event in Montreal



    I had the chance to assist as a sponsor (Avanade) to the Microsoft Dynamics CRM 2011 launch event in Montreal and I wanted to share one thing in particular that struck me by surprise: A speech session by Mitch Joel about marketing. Now, I’m the kind of guy that immediately rolls his eyes just when hearing the word “marketing”, but bear with me, Mitch has a delightful vision of marketing worth writing an article about!


    This slide probably best gives you an idea of his speech:




    "Your brand is not what you say it is...it's what Google says it is. It's also what Facebook, Twitter, LinkedIn say it is"


    Wow, wise words but let me take a step back to fill you in on where this all this comes from. He states we are standing right at the tipping point of a marketing revolution comparable in magnitude to the emergence of E-commerce. So what is this new marketing paradigm about? One word: Social.

    The most powerful marketing campaigns are no longer achieved by purchasing advertisement space or air time, but rather when you get your customers to advertise for you: Yelp, Facebook, etc. He even comes up with an interesting fact: A bad review is more likely to qualify a lead than a good review! That by the way reminds me of DecorMyEyes.com, the company that became famous because of its high ranking in Google, all thanks to its hundreds of bad reviews and scam reports. Mitch explains all this in his book called "Six Pixels of Separation" and as ironic as it sounds, here I am shouting to the world that I can't wait to read that book! OK now that you get the idea of social marketing, why is he talking about this at the launch event for Dynamics CRM? Perhaps a happy coincidence or a strategic topic, in any case, it aligns great with Microsoft’s recent announcement in their Statement of Direction that they will focus R&D efforts in providing solutions for a social CRM. Now it all makes sense, I was really delighted with his speech and the big lesson I took home is: Embrace the social marketing paradigm now or you might as well just reduce your marketing to the printed Yellow Pages.

    Monday, May 16, 2011

    Bulk updating user's preferences (UserSettings) in CRM: PART III

    I have split this post into 3 parts:
    · Part One: Retrieving a spreadsheet with the list of available time zones and languages
    · Part Two: Generating a spreadsheet for entering the user preferences for each user
    · Part Three: Uploading the updated spreadsheet to update user preferences


    Part Three: Uploading the updated spreadsheet to update user preferences

    When you have finished filling out the spreadsheet of part two, you are ready to upload the changes to CRM. If you have added data validation and saved the users.xml file as a spreadsheet, you must restore its original XML value (since this tool will not understand Excel format). You can do that by simply generating the users.xml file again and copy-paste into that file from your completed spreadsheet. Make sure you save the file as XML Data (not XML Spreadsheet) when you are done. If you open the usersCompleted.xml file with notepad/VS it must look something like this:

    <Users>
      <User>
        <Id>a9966ced-7c4e-e011-837a-080027b496abId>
        <Name>Jaques Joy<Name>
        <Language>French (France)<Language>
        <TimeZone>Pacific Standard Time<TimeZone>
      <User>
       ...


    Make sure you save your final user settings XML file with the same filename as specified by the usersCompletedFileName variable (see part one). Now you just need a method to upload the user preferences:


    private static void UploadUserSettingsSpreadsheet(IOrganizationService service, string fileName)
    {
        XmlDocument xDoc = new XmlDocument();
        xDoc.Load(fileName);
        foreach (XmlNode node in xDoc.DocumentElement.ChildNodes)
        {
            XmlNode nameNode = node.SelectSingleNode("./Name");
            XmlNode idNode = node.SelectSingleNode("./Id");
            XmlNode languageNode = node.SelectSingleNode("./Language");
            XmlNode timeZoneNode = node.SelectSingleNode("./TimeZone");
            Guid userId = new Guid(idNode.InnerText);
                
            UserSettings userSettings = RetrieveUserSettings(service, userId);

            string timeZoneName = timeZoneNode.InnerText;
            string languageName = languageNode.InnerText;
            if (string.IsNullOrEmpty(timeZoneName) || !TimeZoneNameToCode.ContainsKey(timeZoneName))
            {
                throw new ArgumentException("Invalid timezone");
            }
            if (string.IsNullOrEmpty(languageName) || !LanguageNameToCode.ContainsKey(languageName))
            {
                throw new ArgumentException("Invalid language");
            }
            userSettings.TimeZoneCode = TimeZoneNameToCode[timeZoneName];
            userSettings.UILanguageId = LanguageNameToCode[languageName];
            userSettings.HelpLanguageId = LanguageNameToCode[languageName];

            service.Update(userSettings);
        }
    }


    And voila, your user settings should be uploaded. If you want to verify that your changes have taken effect, you can re-generate the user settings XML from part two and it should have the updated user settings.

    Finally, you can download the VS Solution I used here.

    Bulk updating user's preferences (UserSettings) in CRM: PART II

      I have split this post into 3 parts:
    · Part One: Retrieving a spreadsheet with the list of available time zones and languages
    · Part Two: Generating a spreadsheet for entering the user preferences for each user
    · Part Three: Uploading the updated spreadsheet to update user preferences

    Part Two: Generating a spreadsheet for entering the user preferences for each user

    In Part One we gathered a list of available time zones and languages. Now we need to provide a spreadsheet were we can assign a time zone and language for each user. Note that this code requires part one to be executed first to populate the dictionaries in memory:
     
    private static void CreateUserSettingsSpreadsheet(IOrganizationService service, string fileName)
    {
        XmlDocument xDoc = new XmlDocument();
        xDoc.AppendChild(xDoc.CreateElement("Users"));

        QueryByAttribute usersQuery = new QueryByAttribute(SystemUser.EntityLogicalName);
        usersQuery.AddAttributeValue("isdisabled", false);
        usersQuery.ColumnSet = new ColumnSet("fullname");

        EntityCollection coll = service.RetrieveMultiple(usersQuery);
        foreach (Entity user in coll.Entities)
        {
            XmlNode userNode = xDoc.CreateElement("User");
            XmlNode userIdNode = xDoc.CreateElement("Id");
            XmlNode languageNode = xDoc.CreateElement("Language");
            XmlNode timeZoneNode = xDoc.CreateElement("TimeZone");
            XmlNode nameNode = xDoc.CreateElement("Name");

            userNode.AppendChild(userIdNode);
            userNode.AppendChild(nameNode);
            userNode.AppendChild(languageNode);
            userNode.AppendChild(timeZoneNode);
            xDoc.DocumentElement.AppendChild(userNode);

            userIdNode.InnerText = user.Id.ToString();
            nameNode.InnerText = user["fullname"] as string;

            UserSettings settings = RetrieveUserSettings(service, user.Id);
            languageNode.InnerText = settings.UILanguageId.HasValue ? LanguageCodeToName[settings.UILanguageId.Value] : string.Empty;
            timeZoneNode.InnerText = settings.TimeZoneCode.HasValue ? TimeZoneCodeToName[settings.TimeZoneCode.Value] : string.Empty;
        }
        xDoc.Save(fileName);
    }

    The code above should give you a nice XML (fileName) that when you open with Excel looks like this:
    Id
    Name
    Language
    TimeZone
    efe78790...
    Nelson Suddeth
    English (United States)
    US Eastern Standard Time
    d0d010ae...
    Maricela Speller
    French (France)
    US Eastern Standard Time
    36d231c0...
    Kathrine Minott
    English (United States)
    Pacific Standard Time
    c7b26b5a...
    Hugh Claybrook
    French (France)
    US Eastern Standard Time
    b1990973...
    Jessie Burgoon
    English (United States)
    Canada Central Standard Time
    4dcfc7fe...
    Cody Helfer
    English (United States)
    Pacific Standard Time
    d58455...
    Ted Tartt
    English (United States)
    Canada Central Standard Time
    3f7d84e...
    Saundra Willits
    French (France)
    Pacific Standard Time
    2e271a...
    Kelly Doster
    French (France)
    Canada Central Standard Time

    Now you can edit in Excel which language and time zone to assign to each user. You can even provide the template to the business to gather personal settings for each user. Note that the file is currently stored as XML so you cannot add data validation to the cells. It would be nice to provide a dropdown for the language and time zone columns. You can save the file as an Excel spreadsheet and then add data validation to those columns using a validation list and populating it with the values we retrieved from Part One:




    Note that providing the drop-down in Excel is an optional step and requires you to save the file in a different format (spreadsheet instead of XML). I’ve used the standard data validation feature in Excel to create the dropdown, and you can do the same for language too.



    Now you can provide the spreadsheet to your customer / business to fill in the time zones and languages for each user J.


    Monday, May 9, 2011

    Bulk updating user's preferences (UserSettings) in CRM: PART I


    I’ve realized it has been some time since my last post. I was quite busy with a CRM project, and I’d like to share some of the lessons I have learned. Sometimes we need to change the user settings for multiple users in CRM and we don’t want to ask each user to do it themselves. For example, for a Canadian organization we are often given a list of users with their corresponding time zone and preferred language. However, there is no functionality in the CRM application to modify someone else’s personal options, even if you log in as a system administrator. In this blog post I will share a tool that I have developed for easily managing user preferences and I will walk you through the design so you can extend the tool if necessary.

     I have split this post into 3 parts:
    ·         Part One: Retrieving a spreadsheet with the list of available time zones and languages
    ·         Part Two: Generating a spreadsheet for entering the user preferences for each user
    ·         Part Three: Uploading the updated spreadsheet to update user preferences




    Part One: Retrieving a spreadsheet with the list of available time zones and languages
    Once you have installed all the language packs you will require, it will be useful to have a list of all available time zones and languages available. Because we have to deal with time zone and language codes, we need to keep a mapping between codes and names (e.g. 1033 = English). The mappings will be useful later on in part two and three. Note that in my sample code I am using strong types so you need to generate the strong types using crmsvcutil.exe. For convenience you can download mine here and include it in your project. I will also post the final VS Solution that I used at the end of part three.
     
    1.       The program!

    Let’s start with the skeleton of the program we will use:
    class UpdateCRMSystemUserSettings
    {
        static string serverName = "192.168.1.68:5555";
        static string organizationName = "Avanade";
        static string userMame = "administrator";
        static string userDomain = "avanadedom";
        static string userpassword = "xxxxx";
        static string usersFileName = @"C:\users.xml";
        static string timeZonesFile = @"C:\TimeZones.xml";
        static string languagesFile = @"C:\Languages.xml";
        static string usersCompletedFileName = @"C:\usersCompleted.xml";
        static Dictionary<int, string> TimeZoneCodeToName = new Dictionary<int, string>();
        static Dictionary<string, int> TimeZoneNameToCode = new Dictionary<string, int>();
        static Dictionary<int, string> LanguageCodeToName = new Dictionary<int, string>();
        static Dictionary<string, int> LanguageNameToCode = new Dictionary<string, int>();

        static void Main(string[] args)
        {
            try
            {
                Organization org = new Organization();
                IOrganizationService service = org.Open(serverName, organizationName, userMame, userDomain, userpassword);

                CreateLanguageMappings(service, languagesFile);
                CreateTimeZoneMappings(service, timeZonesFile);
                   
                //CreateUserSettingsSpreadsheet(service, usersFileName);
                //UploadUserSettingsSpreadsheet(service, usersCompletedFileName);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.ReadLine();
            }
        }
    }

    You might notice we are keeping mappings between codes and names for languages and time zones in memory as well. You might also notice that I am using an Organization class that I have defined for connecting to an On-Premise deployment, but you can change those lines to get the IOrganizationService for your own organization.


    2.       Create the language mappings
    In this method I am retrieving the available languages in the organization and creating a two-way mapping between the language code and the language name. I am saving the mapping as an XML file (languagesFile) which is a spreadsheet that we can open in Excel. I am also keeping the mapping in memory using the dictionaries I defined (will be useful later):
    private static void CreateLanguageMappings(IOrganizationService service, string fileName)



    {
        XmlDocument xDoc = new XmlDocument();
        xDoc.AppendChild(xDoc.CreateElement("Languages"));
        RetrieveProvisionedLanguagesRequest req = new RetrieveProvisionedLanguagesRequest();
        RetrieveProvisionedLanguagesResponse resp = (RetrieveProvisionedLanguagesResponse)service.Execute(req);
        foreach (int lcid in resp.RetrieveProvisionedLanguages)
        {
            CultureInfo culture = CultureInfo.GetCultureInfo(lcid);

            XmlNode languageNode = xDoc.CreateElement("Language");
            XmlNode lcidNode = xDoc.CreateElement("LanguageID");
            XmlNode languageNameNode = xDoc.CreateElement("Name");
            xDoc.DocumentElement.AppendChild(languageNode);
            languageNode.AppendChild(lcidNode);
            languageNode.AppendChild(languageNameNode);

            lcidNode.InnerText = lcid.ToString();
            languageNameNode.InnerText = culture.EnglishName;

            LanguageCodeToName[lcid] = culture.EnglishName;
            LanguageNameToCode[culture.EnglishName] = lcid;
        }
        xDoc.Save(fileName);
    }



    3.       Create the time zone mappings


    Now I am doing the same as I did with the languages but for the time zones:
    private static void CreateTimeZoneMappings(IOrganizationService service, string fileName)
    {
        XmlDocument xDoc = new XmlDocument();
        xDoc.AppendChild(xDoc.CreateElement("TimeZones"));

        QueryExpression query = new QueryExpression(TimeZoneDefinition.EntityLogicalName);
        query.ColumnSet = new ColumnSet("timezonecode", "standardname");

        EntityCollection coll = service.RetrieveMultiple(query);
        foreach (Entity e in coll.Entities)
        {
            XmlNode timeZoneNode = xDoc.CreateElement("TimeZone");
            XmlNode timeZoneCodeNode = xDoc.CreateElement("TimeZoneCode");
            XmlNode timeZoneNameNode = xDoc.CreateElement("Name");
            xDoc.DocumentElement.AppendChild(timeZoneNode);
            timeZoneNode.AppendChild(timeZoneCodeNode);
            timeZoneNode.AppendChild(timeZoneNameNode);

            int timeZoneCode = (int)e["timezonecode"];
            string timeZoneName =  e["standardname"] as string;
            timeZoneCodeNode.InnerText = timeZoneCode.ToString();
            timeZoneNameNode.InnerText = timeZoneName;

            TimeZoneCodeToName[timeZoneCode] = timeZoneName;
            TimeZoneNameToCode[timeZoneName] = timeZoneCode;
        }
        xDoc.Save(fileName);
    }





    4.       Verify your spreadsheets
    By now you should have produced two XML files that when you open with Excel should look like this:


    LanguageID

    Name

    1033

    English (United States)

    1036

    French (France)



    TimeZoneCode

    Name

    256

    Canberra, Melbourne, Sydney (Commonwealth Games 2006)

    55

    SA Western Standard Time

    85

    GMT Standard Time

    180

    Ekaterinburg Standard Time

    158

    Arabic Standard Time

    196

    Bangladesh Standard Time

    29

    Central Standard Time (Mexico)

    165

    Arabian Standard Time

    …list continues…

    …list continues…