The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Creating Custom Sample Data for CRM 2011 - Advanced

Creating Custom Sample Data for CRM 2011 - Advanced

  • Comments 2

We learned how to create a simple sample data for Microsoft Dynamics CRM 2011 in the blog post titled Creating Custom Sample Data for CRM 2011. In this post, we will take a look at some of the more advanced sample data building techniques.

Building a Sample Data Set with Multiple Entities and Related Records

You may want to create multiple related sample data files and then import them into CRM 2011 together. For example, let’s say you create sample data for Account and Contact. You can then create a compressed folder (.zip), drop these sample data files into this folder, and then import the .zip file into CRM from Workplace > Imports. To do so, click on Import Data on the ribbon, and select the .zip file in this dialog below:

image

Then go through the Import Data Wizard flow. At the end of this process, you would have installed multiple sample data files into your CRM system in one go.

You can create very rich and complex data sets using this method since it allows you to create sample data records that are related to each other. For example, you can set the parent customer field of your sample contacts to sample accounts. The import infrastructure will make multiple passes through your data set and ensure that the records are related properly. The account and contact sample data I created contain the following records:

Contact.xml:

First Name: Yvonne

Last Name: McKay (sample)

Parent Customer: Variety Store (sample)

Account.xml:

Account Name: Variety Store (sample)

Primary Contact: Yvonne McKay

Upon importing the Account.xml and the Contact.xml sample data files, the system makes sure that the contact Yvonne McKay (sample) and the account Variety Store (sample) are linked.

Please keep in mind that for the linking to work, the contacts First Name concatenated to its Last Name must be the exact same string as the account’s Primary Contact field. Similarly, the Account Name needs to be the exact same string as the contact’s Parent Customer.

Relative Dates:

If you create sample data with date fields (such as Actual Close Date for Opportunities), your sample data will look stale if you install it much later compared to when it was created. To make sure your data always looks fresh, no matter when it is installed, you can use dates relative to the time of install. For example, if you want the Actual Close Date of your sample emails to be relative to time of install, you need to do the following:

1. Download the opportunity.xml template as described in the Creating Custom Sample Data for CRM 2011 blog post (follow steps 1 through 4). Then add the following 6 columns to the end of the opportunity.xml file (I prefer using Microsoft Excel for editing sample data files):

Delta

Actual

Close

Year

Delta

Actual

Close

Month

Delta

Actual

Close

Day

Delta

Actual

Close

Hour

Delta

Actual

Close

Minute

Delta

Actual

Close

Second

2. Now you need to create data that has relative start dates. For each opportunity record, enter appropriate values in the Delta Actual Close columns so that the records are relative. For example, the following opportunity’s close date will always be one year in future from the time when the sample data is installed.

image

3. You now need to create a map that will allow you to use these columns to make the actual close date of your opportunities relative. To do so, import the opportunity.xml file into your CRM system - navigate to Workplace > Imports and click on Import Data on the ribbon, select the opportunity.xml file and click Next. You will see the following screen, in which, change the CRM Fields dropdown to Show Unmapped:

image

4. Change each field’s drop down to Ignore and click Next:

image

5. Click Next on this screen:

image

6. Type in the Data Map Name. In this example, I will call my map RelativeOpportunityMap, and click Submit:

image

You have just created a basic map for importing opportunities that you will now modify to take relative actual close dates into account.

7. Download the RelativeOpportunityMap by clicking Settings > Data Management > Data Maps, selecting it, and clicking Export (after exporting, delete it from CRM):

image

8. Save the RelativeOpportunityMap on your desktop. You now need to modify this map so that it adds the values from the 6 Delta Actual Close Date columns to the system time to calculate actual close dates for your sample opportunities. The import infrastructure allows you to add the delta values to the system date by using the Microsoft.Crm.Transformations.AddToCurrentDate transformation in your map. When given the 6 Delta Actual Close Date columns as inputs, and actualclosedate (the actual close date field of an opportunity) as the output, this transformation will add the delta values to the current system date and set that as the actualclosedate for the sample opportunity. To add this transformation to the RelativeOpportunityMap, open it using an XML editor (even though I use Microsoft Excel for editing the sample data files, I prefer using Microsoft Visual Studio for editing the map files). Add the following node as a child node of the EntityMap node:

<TransformationMaps> <TransformationMap> <TransformationTypeName>Microsoft.Crm.Transformations.AddToCurrentDate</TransformationTypeName> <ProcessCode>Process</ProcessCode> <InputParameterMaps> <SingletonInputParameterMaps> <SingletonInputParameterMap> <ParameterSequence>1</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Year</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>2</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Month</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>3</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Day</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>4</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Hour</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>5</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Minute</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>6</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Second</Data> </SingletonInputParameterMap> </SingletonInputParameterMaps> <ArrayInputParameterMaps /> </InputParameterMaps> <OutputParameterMaps> <OutputParameterMap> <ParameterSequence>1</ParameterSequence> <Data>actualclosedate</Data> </OutputParameterMap> </OutputParameterMaps> </TransformationMap> </TransformationMaps>

9. Now that you have modified the map to calculate actualclosedate by using a transformation, you need to remove the original mapping for it. Find the following node in the RelativeOpportunityMap and change its ProcessCode from Process to Ignore and delete actualclosedate from its TargetAttributeName:

Before:

<AttributeMap>

    <SourceAttributeName>Actual Close Date</SourceAttributeName>

    <TargetAttributeName> actualclosedate </TargetAttributeName>

    <ProcessCode>Process</ProcessCode>

</AttributeMap>

After:

<AttributeMap>

    <SourceAttributeName> Actual Close Date</SourceAttributeName>

    <TargetAttributeName></TargetAttributeName>

    <ProcessCode>Ignore</ProcessCode>

</AttributeMap>

10. Save and Close the RelativeOpportunityMap file

11. Import the RelativeOpportunityMap file into CRM from Settings > Data Management > Data Map

12. Select the RelativeOpportunityMap file and click ok:

image

13. Congratulations! You have successfully created and imported the map that will allow you to install opportunities with relative actual close dates.

14. You are now ready to import the opportunity.xml file to your CRM system. To do this, go to Workplace > Imports > Import Data. Select opportunity.xml as the data file name and click Next until you see the following screen:

image

Click OK

15. Click Back on three screens in a row, starting with this screen:

image

16. You will see the following screen. Select RelativeOpportunityMap and click Next:

image

Keep on clicking Next until you see Submit

17. Navigate to Sales > Opportunities to check that opportunities with relative dates have been imported into the system.

Things to keep in mind:

There are a few things you need to keep in mind as you are creating sample data. The following list is not exhaustive, but contains some of the issues you may face as you create and import sample data for your system:

  • You cannot import the lookup field for a N:N (Many to Many) relationship.
  • If records in a source file contain multiple values in a field that contains relationship data, edit the data so it contains one value, otherwise data import will fail.
  • Verify that required data in your source files exists. For example, Account Name is a required field for Account, and therefore must be included in Account sample data.
  • If your source file contains columns that do not map to existing fields, create a custom field as part of the import process using the Import Data Wizard.
  • To avoid import errors, verify that all date values are in the correct format before starting the Import Data Wizard. Each date must use the format YYYY-MM-DD.
  • Verify that data in the status and status reason columns matches valid values in Microsoft Dynamics CRM.

I hope that this blog helps you create a pretty rich set of sample data using the techniques described above to meet all your needs.

Cheers,

Rubaiyat Khan



  • hi,

    i just wanna know to to customize more the tables for the contacts and account information. What i mean is that how to add more COLUMNS and ROWS in the field.

  • I am intriqued with the use of a .zip folder containing Account and Contact files and have been trying to get this to work with 2 new Accounts and 2 new Contacts.  No such luck so far.  My assumption is that both Accounts and Contacts should be created and linked.  I am receiving the following error message on both Accounts: "The requested record was not found or you do not have sufficient permissions to view it."  The 2 Contacts are failing as well with the following message, "The reference lookup could not be resolved," but I am assuming this is because the Account creation is failing.  

    Any help on this?  Thanks.

Page 1 of 1 (2 items)
Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post