MS CRM - Using Excel VLookup to Update Exported CRM Records

After successfully imported more than 20,000 records into CRM Contact, the client gave me 2000 records with updated phone numbers. That means I need to update the existing CRM Contact records with the new file.

There are a few options here - I can use a third party ETL tool, such as Scribe, to load the Excel file into CRM directly, but this can be an overkill. Or I can re-do the whole data import, but this may not be viable because data have been updated/deleted since the first import. So I decided to use the export-for-reimport approach.

The environment that I worked with is CRM 2016 Online. In earlier CRM versions, the Excel export record limit is 10,000. To export more than that, you will have to rely on registry key tweak or tools to increase the limit. But now the limit is increased to 100,000 - such a life saver. Also, the option to specify for a re-import is taken away. I take it in the new CRM, any export to Excel, by default record guid is included and hidden.

So I exported all data with Phone # columns that I wanted to update into Excel (let's name this Destination worksheet). In the Excel, I attached another Excel Worksheet with updated Phone #s (let's name this Source worksheet). From there, all I needed to do is retrieve and match the updated value  from the source into the destination.

This is the exported Excel worksheet (destination)

















And this is the source worksheet with updated phone #s (source)




















So the formula is VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
In the exported Excel example (destination), in the Main Phone cell, used formula: VLOOKUP($A2,Sheet1!$A$2:$C$780,2,0), where

$A2 = the field that I wanted to look up against, in this case, Full name
Sheet1!$A$2:$C$2000 = the range to search (in the source)
2 = the returned column index, 2 indicated the second column, which is Column B
0 = always set to 0 for an exact match, if skipped or set to 1, you will get weird result.

After populating your exported Excel, re-import it, and you will have your updated info.

MS CRM SSRS - Fetchxml Party List Not Supported

I was building a custom SSRS report for an MS CRM Online instance, as usual, I use Advanced Find to build my query, This looks like a straightforward query:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="phonecall">
    <attribute name="subject" />
    <attribute name="createdon" />
    <attribute name="ownerid" />
    <attribute name="actualend" />
    <attribute name="from" />
    <attribute name="regardingobjectid" />
    <order attribute="subject" descending="false" />
  </entity>
</fetch>

When I pasted it into the working area, an error popped up:

The attribute type "PartyList" is not supported. Remove attribute "from" from the query, and try again.

This started to make sense because a Party List field can represent a person or group associated, and SSRS can not simply translate it into a text field. So the solution will be retrieving it from the Activity Party. So I changed the query to include the additional link-entity:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="phonecall">
    <attribute name="subject" />
    <attribute name="createdon" />
    <attribute name="ownerid" />
    <attribute name="actualend" />
    <attribute name="regardingobjectid" />
    <order attribute="subject" descending="false" />
   <link-entity name="activityparty" to="activityid" from="activityid" link-type="outer" >
     <attribute name="participationtypemask" />
     <attribute name="partyid"  />
     <attribute name="partyidname" />
   </link-entity>
  </entity>
</fetch>

More often than not, since the activity party may return multiple results, if you really want to display all results in a single field, you will have to perform additional tweaking to do so. A couple of solutions:
1. Capture and concatenate the activity party field into a custom single text field in CRM, and refer to this field in your report
2. Use custom code to concatenate the value within SSRS. Custom code may not be allowed with the RDL Sandboxing.

But how to do so is beyond the scope of this blog.

MS Dynamic CRM Plugin - Assign Record on Retrieve Message

I have worked on a couple MS Dynamics CRM projects for Call Center clients, one of the common requirements is record lock - when an agent opens a record, be it a lead or case, this record should be made exclusively available to the agent. Typically, these records are commonly accessible by all agents, until an agent marks one record, usually by opening it. An agent may choose not to work on this record, but until the record is released back to the "pool", this agent has the exclusive access.

Out of box, MS CRM comes with Queue and Queue item. They have never been good enough for such a scenario, because Queue Item is manually picked, and is just an intersect table between Queue and the actual record. A user/agent can bypass the Queue and work on the actual record if the user wants to. More often than not, in a call center environment where agents are measured by individual accomplishment, Queue and Queue Item do not meet the business requirement.

I started with assigning the record using JavaScript, i.e. when a record is loaded, an Onload JavaScript function it will assign the record to the executing user. The following users will be alerted if they try to open the same record. But this assignment occurs in client side, there are always possible lags between the local client and the server. It is simply not a reliable solution.

So I decided to use a Retrieve plugin to achieve this. The idea is when a record is retrieved, the plugin will assign the record to the user. This is not a complete solution but the code snippet. You definitely need other mechanism/processes in place to meet your own business requirement. 

Anyway, in my example, the entity is PhoneCall. The step is registered on post-operation of Retrieve.

       public void Execute(IServiceProvider serviceProvider) {
          try {
            IPluginExecutionContext context = (IPluginExecutionContext) serviceProvider.GetService(typeof(IPluginExecutionContext));
            if (context.Depth > 1) { return; }
            if (context.OutputParameters.Contains("BusinessEntity") && context.OutputParameters["BusinessEntity"] is Entity)
            {
                Entity entity = (Entity)context.OutputParameters["BusinessEntity"];

                if (entity.LogicalName == "phonecall" && entity.GetAttributeValue<OptionSetValue>("statecode").Value == 0) //if the phone is open
                {
                    IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                    IOrganizationService service = factory.CreateOrganizationService(context.UserId);
             
                   
                        Guid PhoneCallId = context.PrimaryEntityId;
                        Guid userId = context.UserId;

                        //assignment
                        AssignRequest assign = new AssignRequest
                        {
                            Assignee = new EntityReference("systemuser", userId),
                            Target = new EntityReference("phonecall", PhoneCallId)                        
                        };
                        //   Execute the Request
                        service.Execute(assign);
                }
           
            }
          } catch (Exception ex) {
            throw new InvalidPluginExecutionException(ex.Message.ToString());
          }
        }

MS CRM - Alert and Notification JavaScript Methods

How do you create alerts – that seems to be a simple question.

So often in CRM, users need to be alerted of their actions. For example, a typical alert might be a message that warns you when a user attempts to enter a text string into a numeric field.

Using the alert() method, a message is usually displayed in a pop-up window. This type of alert method does not require any in-depth response from users, who will simply acknowledge it by clicking the OK button.

With Microsoft Dynamics CRM 2013 and 2015, Microsoft Dynamics CRM has continued to improve the user experience by introducing a flattened interface so to speak.

This approach focuses on removing excessive clicks and flattening out pop-up windowsfound in the previous version of Dynamics CRM.

With that in mind, using an alert method that requires a response from users seems to contradict the flattening approach.

At the same time, Dynamics CRM 2013 and 2015 introduced new alert methods:setFormNotification and setNotification.

These two methods display messages on the form instead of on a pop-up window, and require no clicking from users.

The Classic CRM Alert Method Versus the New Improved CRM Alert

In this article, I am going to demonstrate how the new alert methods are different from the classic one, and how they can be used.

The example here is a credit card form where credit card numbers and a CVV codes were to be validated. In the previous version of Dynamics CRM, using the classic alert() method, users are typically notified of errors one by one and users have to acknowledge them by clicking OK.

Of course this alert method can be slightly improved by displaying the error messages in one single alert, thus reducing the number of pop-ups and clicks to one. But still users have to make the effort clicking the OK button.

Eliminating the Mouse Clicks on the Notifications in Dynamics CRM

In Dynamics CRM 2013 or 2015, mouse click(s) can be completely eliminated by using setFormNotification that displays message on the form header area. And depending on the nature of messages, they can be categorized as information, error and warning.

• Xrm.Page.ui.setFormNotification(‘Please fix the following error(s):’, ‘INFORMATION’,);
• Xrm.Page.ui.setFormNotification(‘Invalid Credit Card Number.’, ‘ERROR’);
• Xrm.Page.ui.setFormNotification(‘Invalid CVV Code.’, ‘WARNING’);

There is an extra line of code to clear the message from the form, simply use the following method:

• Xrm.Page.ui.clearFormNotification()

When there are many fields on one form, users may find it difficult to locate the fields that require correction (of course, additional methods can be developed to help locate such fields, for example, setFocus or setRequiredLevel).

In such a case, I would tend to use another alert, setNotification, that displays messages next to the fields. This way, fields can be visibly located and corrected.

• Xrm.Page.getControl(“new_creditcardnumber”).setNotification(‘Invalid Credit Card Number.’);
• Xrm.Page.getControl(“new_cvvcode”).setNotification(‘Invalid CVV Code.’);

Bear in mind that these two methods do not intercept the saving event, meaning that you can still save the records with invalid data.

So depending on the requirements, additional scripts and conditions may be required to prevent record saving if errors are not resolved.

Whichever methods you use, there are no more mouse clicks and the form looks cleaner with messages embedded on it.

At the end of the day, the new alert methods complements the flattening interface andimproves user navigation experience and efficiency.

Happy alerting!