Import Case, Account, and Contact (Multiple Entities–3 Entities ) at Once in Single File in CRM Import Wizard

In my previous post, I was talking about how to solve the unresolved lookup reference between primary entity and child entity which I Put Customer and Case as the entities.

Now, what if I want to import them at once.

A Case reference to a Contact that Parent Company is an Account, which is Primary Contact is the Contact itself.

So it is a chicken and egg circuit, circle round and always in the loop.

Now, prepare your data map

*You can get your simple sample data map by importing to CRM and make sure you map all fields correctly and then save and download your newly create data map

#0 Prepare the Source File

You can prepare the source file first then you can try to import to CRM to help you to create the Data Map and do mapping from your source to the CRM Target Entity and Attribute.

#1 Create New Custom Data Map or Use the Existing One and Download It

*As mentioned before you can use the existing data map and modify it.
If you noticed, you can learn how to create new data map from the ‘For Generic Contact and Account Data’

http://blogs.msdn.com/b/crm/archive/2010/11/04/importing-two-or-more-entities-from-a-single-file.aspx

And here our objective is to import 3 entities so that we need to create new Data Map.

#2 Edit the Data Map XML

Here is the XML of the data map:

<Map Name="Case Single File with Account and Contact Reference" Source="Import">
  <Description>Data map created automatically using the map settings specified during an import.</Description>
  <EntitiesPerFile>Multiple</EntitiesPerFile>
  <EntityMaps>
    <EntityMap TargetEntityName="incident" SourceEntityName="Case" Dedupe="Ignore" ProcessCode="Process">
      <AttributeMaps>
        <AttributeMap>
          <SourceAttributeName>Case Title</SourceAttributeName>
          <TargetAttributeName>title</TargetAttributeName>
          <ProcessCode>Process</ProcessCode>
        </AttributeMap>
        <AttributeMap>
          <SourceAttributeName>Customer</SourceAttributeName>
          <TargetAttributeName>customerid</TargetAttributeName>
          <ProcessCode>Process</ProcessCode>
          <LookupMaps>
            <LookupMap>
              <LookupType>System</LookupType>
              <LookupEntityName>account</LookupEntityName>
              <LookupAttributeName>name</LookupAttributeName>
              <ProcessCode>Process</ProcessCode>
            </LookupMap>
            <LookupMap>
              <LookupType>System</LookupType>
              <LookupEntityName>account</LookupEntityName>
              <LookupAttributeName>accountid</LookupAttributeName>
              <ProcessCode>Process</ProcessCode>
            </LookupMap>
            <LookupMap>
              <LookupType>System</LookupType>
              <LookupEntityName>contact</LookupEntityName>
              <LookupAttributeName>contactid</LookupAttributeName>
              <ProcessCode>Process</ProcessCode>
            </LookupMap>
            <LookupMap>
              <LookupType>System</LookupType>
              <LookupEntityName>contact</LookupEntityName>
              <LookupAttributeName>fullname</LookupAttributeName>
              <ProcessCode>Process</ProcessCode>
            </LookupMap>
          </LookupMaps>
        </AttributeMap>
      </AttributeMaps>
    </EntityMap>
    <EntityMap TargetEntityName="account" SourceEntityName="Account" Dedupe="Eliminate" ProcessCode="Process">
      <AttributeMaps>
        <AttributeMap>
          <SourceAttributeName>Account</SourceAttributeName>
          <TargetAttributeName>name</TargetAttributeName>
          <ProcessCode>Process</ProcessCode>
        </AttributeMap>
        <AttributeMap>
          <SourceAttributeName>Contact</SourceAttributeName>
          <TargetAttributeName>primarycontactid</TargetAttributeName>
          <ProcessCode>Process</ProcessCode>
          <LookupMaps>
            <LookupMap>
              <LookupType>System</LookupType>
              <LookupEntityName>contact</LookupEntityName>
              <LookupAttributeName>fullname</LookupAttributeName>
              <ProcessCode>Process</ProcessCode>
            </LookupMap>
            <LookupMap>
              <LookupType>Source</LookupType>
              <LookupEntityName>Contact</LookupEntityName>
              <LookupAttributeName>Contact</LookupAttributeName>
              <ProcessCode>Process</ProcessCode>
            </LookupMap>
          </LookupMaps>
        </AttributeMap>
      </AttributeMaps>
    </EntityMap>
    <EntityMap TargetEntityName="contact" SourceEntityName="Contact" Dedupe="Eliminate" ProcessCode="Process">
      <AttributeMaps>
        <AttributeMap>
          <SourceAttributeName>Contact</SourceAttributeName>
          <TargetAttributeName>lastname</TargetAttributeName>
          <ProcessCode>Process</ProcessCode>
        </AttributeMap>
        <AttributeMap>
          <SourceAttributeName>Account</SourceAttributeName>
          <TargetAttributeName>parentcustomerid</TargetAttributeName>
          <ProcessCode>Process</ProcessCode>
          <LookupMaps>
            <LookupMap>
              <LookupType>System</LookupType>
              <LookupEntityName>account</LookupEntityName>
              <LookupAttributeName>name</LookupAttributeName>
              <ProcessCode>Process</ProcessCode>
            </LookupMap>
            <LookupMap>
              <LookupType>Source</LookupType>
              <LookupEntityName>Account</LookupEntityName>
              <LookupAttributeName>Account</LookupAttributeName>
              <ProcessCode>Process</ProcessCode>
            </LookupMap>
          </LookupMaps>
        </AttributeMap>
      </AttributeMaps>
    </EntityMap>
  </EntityMaps>
</Map>

*Which i will explain each node..

The most important is this XML Node:



This will indicate, eventhough it is in single file, you want to split them into different entities.

Now, for making it clearer, I try to hide the detail thing first and put the high level perspective here:



Basically, we have 4 important components

Map Source Node

-> This node is important because this will become the unique identifier for naming the Data Map

Description

-> Not important but you gonna need it to make it clearer

Entities Per File

-> Single or Multiple?

-> Very important to indicate whether you want to import to split or one entity only

Entity Maps

-> Very Important

-> You need this for mapping, the very fundamental thing

-> Can have multiple <EntityMap/> node that the number will determine how many multiple entities you want to import to

Entity Map

Now, here is your mapping for each entity:

Case

I have Case Title + Customer fields as Source and Target.

While for Case Title it is very straight-forward because it is a Text field, while for Customer field, little bit difficult because it is a lookup field and not only to single entity with single field as reference.

Account

I will have Source field: Account and Contact in my source files which i want to link them together.

I want to create new Account with Primary Contact  from my Contact field and vice versa for Contact below

Contact

I need to make sure that the Contact will be linked to the Account as Company.

*You need to make sure that the Source and Target are correct, but CRM would always check it once you try to import the Data Map

#3 Import the Data Map

Now just import it to the Data Map

Microsoft Dynamics CRM –> Settings –> Data Management –> Data Maps



And then click Import

Then the system will validate it.

#4 Back to the Source Files and Fill the Data

You can back to your file again to do re-checking before importing (even though you can delete it later)



So example here, I expect I will create new Case for row 5 and 6 with new Customer (Account/Contact that link each other).

I expect the system will create new Case with Title = Question about new Membership, with Customer = Aileen Gusni,

To make it happened, at first, the system need to create Aileen Gusni as Contact first, but Aileen Gusni also needs Jakarta Fluid Tech as her Company name.

So, CRM will also create Jakarta Fluid Tech as Account with Primary Contact = Aileen Gusni and Vice Versa, Jakarta Fluid Tech as Aileen Gusni’s Company name. While the newly created Case will be linked to Aileen Gusni as the customer.

And so on..Same for other Cases, Accounts, and Contacts.

#5 Import To CRM

Import to CRM as usual, but remember to Use our Data Map




You can see the Mapping, you would notice that the system will auto-map into 3 entities as same as you defined in the data Map, especially the <EntityMaps> node. you can make it ‘Ignore’ or just leave it and Next




And here is the Review Mapping Summary, you will realize that the CRM auto-split the field into 3 different actions of Entity import as pre-defined Entities in your Data Map.

Result

After the Import was completed, I go to the Case and I find this!

Case with Customer (Contact) Detail

As per our expectation.

Case Title and Customer are correct, while we know that Aileen Gusni is working for Jakarta Fluid Tech company.

Now, let’s go to the Company itself.

Company/Account Detail

Yes, as we can see the Primary Contact is beautifully set to the correct name.

Same as well with the other Account and Contact: Troy Co. and Adam Hings

Conclusion

Now, we understand that using the Data Map, you can do bulk import across multiple entities and multiple attributes, not only limited to one entity as well. Then you can also solve the big problem the ‘which one is the first’ question.

To make new data map, need to learn the XML Schema first, but never mind CRM will do checking for you.

I’d recommend to you to use the XML Editor or Visual Studio XML Editor, you can also use Notepad or Notepad ++ if you wish .

In this post, I explained about how powerful data map can be your helper to do bulk import without you need to make effort in splitting them into 3 different files and import them in correct sequence, because CRM has capability to know its sequences. I believe that this is not only limited to 3 entities.

Hope this helps!

Thanks.

Tips & Tricks: How To Solve the Common Bulk Import Problem: ‘The Lookup Reference Could Not Be Resolved” in CRM Import Wizard

Have you ever thought that importing data to CRM using OB Import feature is little bit troublesome and difficult?

Issue Introduction

For example, you are trying to import Cases (Bulk) to CRM with Case Title and Customers then you found that some of your records were failed because of this common issue:

That is “The lookup reference could not be resolved”

If yes.. Then we encounter the same challenge.

Yes, that happens and happens in most times during import.

Caused by you are trying to refer the lookup field to a record that is not existing record in CRM, so the system would confused and trying to find the correct Master Data reference.

Your are expecting to make sure that if I try to do bulk import, if the lookup reference could not be found or resolved, the system would auto-detect and auto-create the reference record, for example here is to create Customer record, either Account or Contact.

While the system would reject it if you just use the one file containing direct columns.

*While, Row No 5 and 6 are not referring to existing Lookup.

The system would not auto-create this if you keep importing the data with that manner since it is a lookup field and mentioned ‘must already exist…’

Workarounds

You can use the SSIS or any other third party tool, but, i am in budget constraint and not going to buy new tool. What should I do?

#Create Separated Files

1. Create another file to store the Customer record, Account and Contact

2. Zip them together

3. Now import the zipped file to CRM and do mapping

4. Waiting…

5. Result

Result

Here we go..

The result..

As you can see that new all Cases have been imported successfully without any error.

CRM can auto-create the un-existing records if you specifically split them.

Because CRM can detect from the source, which entity record should be created at first before creating other records.

Yes..Now, all my Cases records has been inserted into CRM, plus I have managed to achieve my objection to create the new customer record.

Without this way, the system can’t automatically create the reference record even though CRM knows that those lookup reference are not existing records and need to be created first, you need to provide more data to CRM in form of new separated files.

Despite it gives you possibility to achieve that, there is still an obstacle because you don’t know about the lookup existence until you tried it? Your objective should be doing similar to ‘Upsert’ operation which is system can auto-detect the records exist or not, if not then create new one, otherwise just refer it.

How I know that the records are already exist or not? Well, you need to massage your data, such as export all the Account/Contact then using VLookup function manually in CRM to find the data, you use SQL Query or ODataQuery, or you just try and error then just export out those which failed and then extract out the columns and create them in new separated files.

Hope this helps!

Thanks.

How to Show the Missing ‘Report Data’ in SSRS Development

A quick post, just in case you are creating SSRS or continue from anyone project and you found that you lost the ‘Report Data’ properties box, so here I am, facing the same issue.

Solution

A simple solution: Just press: CTRL + ALT + D

Result

Hope this can save your day.
Thanks!

Get Current User Setting Timezone CRM C#

Sometimes we need to get the current user timezone, so here is the code:

public int? RetrieveCurrentUsersTimeZoneSettings(IOrganizationService service)
{
     var currentUserSettings = service.RetrieveMultiple(
     new QueryExpression("usersettings")
     {
        ColumnSet = new ColumnSet("localeid", "timezonecode"),
        Criteria = new FilterExpression
        {
            Conditions =
        {
            new ConditionExpression("systemuserid", ConditionOperator.EqualUserId)
        }
        }
     }).Entities[0].ToEntity<Entity>();
     return (int?)currentUserSettings.Attributes["timezonecode"];
}

*You just need to pass the current CRM Service
Hope this helps.

Thanks.

Utilizing ‘ReplacePrivileges’ Plugin Message in CRM C#

In this post will talk about ‘ReplacePrivileges’ Message.

Introduction

This message will be triggered if you change the set of privilege collections from a security role.

In short word, when you change the ‘circle’ in the Security Role setting

When To Use?

Interfere this Plugin Message can be useful for some situations, example:

1. Prevent human error in security role modification for specific security role that already the best in the production.

Often, we have human error, this one cannot access, that one also cannot access, it can be caused by human error, someone accidently revoke or remove a privilege that is required to perform some action.

In addition, we cannot also remove some basic privilege that must be there to login and access CRM Data, such as System Form, User Settings, etc. This can be prevented by give validation by doing intervention of this plugin message

2. Prevent user from ‘abuse of power’ action

In some organization which is security is very important, you cannot just grant common role person a privilege to delete important records, right? For example for Salesperson, do not delete Competitor record, Customer Service cannot delete existing Case, etc.

But, we cannot just let it go and blame who if that happened? We can prevent it by add some logic in this plugin message.

3. Logging Purpose

We can actually turn on Auditing in CRM, but for advance log, you can put your logging logic into this plugin message.

Sample Code

First Sample, Prevent Any Changes for ‘Salesperson’ Role

Many people are assigned to Salesperson role, so maintaining its security is very important.

You have set correctly a privilege in the development server, you want to make sure that no one, including the new System Administrator (if you resigned) to not be able to make any changes.

So, here is the sample code:

public void Execute(IServiceProvider serviceProvider)
{
     #region must to have

     IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

     IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

     // Create service with context of current user
     IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

     //create tracing service
     ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

     #endregion

     Guid guidRoleId = new Guid();

     if (context.InputParameters.Contains("RoleId"))
     {
         guidRoleId = (Guid)context.InputParameters["RoleId"];               
     }

     //To prevent any changes for Salesperson privilege
     if(GetRoleName(service, guidRoleId).ToString().ToLower() == "Salesperson".ToLower())
     {
         throw new InvalidPluginExecutionException("Please do not modify any changes for Salesperson Role!, do not dare to do it!");
     }
}

private string GetRoleName(IOrganizationService service, Guid guidRoleId)
{
     Entity enRole = null;
     string strRoleName = string.Empty;

     enRole = service.Retrieve("role", guidRoleId, new ColumnSet("name"));

     if (enRole != null)
     {
         strRoleName = enRole.GetAttributeValue<string>("name");
     }
     return strRoleName;          
}

Result:

Second Sample, Prevent Grant ‘Delete’ Privilege for ‘Competitor’

We don’t want Salesperson to delete the ‘Competitor’ record and we don’t want any human error to give the privilege (prvDeleteCompetitor).



Here is the code:

public void Execute(IServiceProvider serviceProvider)
{
      #region must to have

      IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

     IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

      // Create service with context of current user
     IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

     //create tracing service
     ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

      #endregion

      Guid guidRoleId = new Guid();

       if (context.InputParameters.Contains("RoleId"))
       {
            guidRoleId = (Guid)context.InputParameters["RoleId"];               
       }

      //To prevent changes for Salesperson privilege
       if (GetRoleName(service, guidRoleId).ToString().ToLower() == "Salesperson".ToLower())
       {
            if (context.InputParameters.Contains("Privileges"))
            {
               RolePrivilege[] privileges = (RolePrivilege[])context.InputParameters["Privileges"];
               foreach (RolePrivilege rolePrivilege in privileges)
               {
                    //To prevent granting 'Delete' Access for 'Competitor' Entity
                    if(GetPrivilegeName(service, rolePrivilege.PrivilegeId) == "prvDeleteCompetitor")
                    {
                        throw new InvalidPluginExecutionException("Please do not give this Delete Competitor Privilege for Salesperson");
                     }                           
                }
              }
       }                   
}

private string GetRoleName(IOrganizationService service, Guid guidRoleId)
{
     Entity enRole = null;
     string strRoleName = string.Empty;

     enRole = service.Retrieve("role", guidRoleId, new ColumnSet("name"));

     if (enRole != null)
     {
          strRoleName = enRole.GetAttributeValue<string>("name");
     }

          return strRoleName;          
}

private string GetPrivilegeName(IOrganizationService service, Guid guidPrivilegeId)
{
     Entity enPrivilege= null;
     string strPrivilegeName = string.Empty;

     enPrivilege = service.Retrieve("privilege", guidPrivilegeId, new ColumnSet("name"));

     if (enPrivilege != null)
     {
         strPrivilegeName = enPrivilege.GetAttributeValue<string>("name");
     }

     return strPrivilegeName;
}

Result:

Checking Privilege Depth

You can use rolePrivilege.Depth also to check, whether this is Basic, Deep, Local or Global Access.

https://msdn.microsoft.com/en-us/library/microsoft.crm.sdk.messages.privilegedepth.aspx

How to Register The Plugin

Message: ReplacePrivileges

Primary Entity: role

Register as Pre-Operation or Post-Operation so far I don’t see any difference for this message, but better you register Pre-Operation stage for validation.

Hope this helps!

Thanks.

Alter Lookup Field/Column Displayed Value In the CRM View Using C#

When the users go to CRM and access the Entity View, the objective is they want to see the list of complete data, that they don’t want to click and enter each record, one by one.

Scenario

Often we can see a field showing same value, but in fact it should refer to different record.

Here is for example:

As we can see here, there are many Cases referring to same Customer Name (for example: Adventure Works), but actually, are they? Are they the same ‘Adventure Works’ or not?

Well, if we click one by one, we will know that they are different, because in fact we have  a lot of Adventure Works around the world (can be in Jakarta, Sydney, Singapore, Canada, etc.) or we can just add new columns to display, but it means consume another column space, and imagine every time you need to add the columns to the view, including your personal view.

Now, let’s tweak it little bit, I want to see the Case from which Customer, really, I want to know which the customer, exactly? Is that from Jakarta, Sydney, or any other branch.

Expected Result

I want to get like this:

Case 1        Adventure Works [Jakarta, Indonesia]
Case 2        Adventure Works [Sydney, Australia]

Not only showing ‘Adventure Works’

I want to concatenate the multiple fields into single lookup field column.

The Code

Here is the Sample C# Code to manipulate the lookup displayed value.

public void Execute(IServiceProvider serviceProvider)
 {
     #region must to have

     IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

     IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

     // Create service with context of current user
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

     //create tracing service
     ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

     #endregion

     if (context.OutputParameters.Contains("BusinessEntityCollection"))
     {
          var retrievedResult = (EntityCollection)context.OutputParameters["BusinessEntityCollection"];
          foreach (Entity entity in retrievedResult.Entities)
          {
              //retrieve the CustomerId Entity Reference
              if (entity.Contains("customerid"))
              {
                  EntityReference erCustomerId = (entity.Attributes["customerid"] as EntityReference);

                  if (erCustomerId != null)
                  {
                      //retrieve the customerid detail
                      Entity enCustomer = new Entity();
                      enCustomer = service.Retrieve(erCustomerId.LogicalName, erCustomerId.Id, new ColumnSet("address1_city", "address1_country"));

                      //retrieve the City and Country detail
                      string strCity = enCustomer.Contains("address1_city") ? enCustomer["address1_city"].ToString() : string.Empty;
                      string strCountry = enCustomer.Contains("address1_country") ? enCustomer["address1_country"].ToString() : string.Empty;

                      //alter the displayed value for column name
                      erCustomerId.Name = string.Format("{0} [{1}, {2}]", erCustomerId.Name, strCity, strCountry);
                  }
              }
          }
      }
}

How to Register Your Plugin

Please register your Plugin with the following config:



Message: RetrieveMultiple

Primary Entity: incident

Event of Execution: Post-Operation

And it is Synchronous Plugin.

Result

Now, see the result here:



*As we can see, you can see the additional information that is in separated fields to be displayed in single column.

This is also can be workaround for the CRM Limitation to only get the column from up to one level related entity.

So, let’s say you have Customer as the Lookup field, then you can only get the Columns from the Account/Contact, you cannot get the Column from the Lookup detail of the Account, for example: Originating Lead's Columns, Account Owner’s columns, etc.

It also does not consume to much space and you can put another detail, concatenate multiple columns into single column display.

Hope this helps!

Thanks.

Enable Modifying Unit Group in Existing Product Using Business Rules

As we know that we cannot modify Unit Group field in the CRM Existing Product record:



What if we want to Update the Unit Group?

Well, you can use Data Import feature.

What if we only want to update one record?

We can actually utilize the Business Rules as a trick

1. Create a Business Rule for Product



You can do any If, can be Product Name, Product ID or any dedicated field just to trigger the ‘Unlock’

And then for the Action, just make the Unit Group in ‘Unlock’ position.

2. Activate it

3. To Make it works, you need to trigger it (since the onLoad will be overridden by CRM Validation, it will lock again the Unit Group, so you need to change the field value to trigger it)

I just type a (anything actually) in the Description field



4. You can change the Unit Group



Note

1. This is a trick to update Unit Group just in case you want update in single record, it is useful to avoid hassle in importing effort just for single record
2. Remember to always memorize the field you changed to trigger and change the Default Unit accordingly by the new Unit Group
3. Since CRM blocks it, Do at your own risk.

Thank you!