Understanding Import Template CRM 2011/2013: Discover Where The Fields are Coming From and Find the Missing Fields

Recently, I had been asked question by clients and by friends in Community about how to handle the missing fields in ‘Import Template’ when users want to import the data to CRM 2011/2013.

As we know, the Import Template has been proven to speed-up the import data since this template has a XML Validation that can help users to know what expected values to be inserted to the each column using that template.

But, sometimes, we found that the fields are so unstructured and we found that the expected fields were missing from the template.

This big question often appears in the Forum and also in my journey before, especially for Account and Contact that often need users to do bulk import for new Customers. Any functional consultant should be aware how to add fields to the template because customers often use it, rather than import a blank .csv file without any validation.

Okay, the first question is:

“I can’t find any place to customize the import template, but, I can see the fields after I download the Import Template, so where are they coming from?”

In this blog, I am explaining about ‘Import Template’ to let us getting more understanding about this feature.

To get started, I will use a new fresh custom entity to make us more understand the origin of those fields and I’ve created a custom entity: ‘Project’.

Default ‘Import Template’ from Fresh Custom Entity

Now, I Download the Template, it is without adding any new field.

And this is the result…

So, by default, it has:

1. Name

2. Owner

But, I didn’t do any configuration, how those fields can appear in the template?

Please follow the next statements it might answer your question.

Next, Add New Fields to the Custom Entity

I add new fields: field 1 and field 2, in the custom entity and let’s check whether the field will be automatically inserted in the template or not.

Field 1, a Single Line of text field.

Field 2, a Two Options field.

As usual without mentioning, I always do save and publish….

Now, I download the template… (I might be hoping that those new fields will be added to my template)..

Well, unfortunately, it didn’t show up.



So, what next?

I was thinking why the ‘Name’ and ‘Owner’ can come without I customize anything..
And if you try Account and Contact, the Templates also have pre-defined fields.

So, the next step is:

Add Those New Fields to the Custom Entity View

As we know that the columns shown in export to Excel and re-import features in CRM are based on the columns in certain view, depends the selected view you are using just before you click the ‘Export to Excel’ feature.

It can be System View, Personal View, or un-named view in Advanced Find (you can add more columns in Advanced Find).



Download the template…



So, we know that adding fields to the View didn’t give any effect, it is showing ‘Name’ and ‘Owner’, instead of showing ‘Name’ and ‘Created On’ by default.

So, need to do next experiment.

Add Those New Fields to the Custom Entity Form

When I go to Form Editor, I found that by default, ‘Name’ and ‘Owner’ are defined in the Form.



So..it boosts my curiosity to try adding new fields to the Form.

I add the field 1 and field 2.



And Wow…This is the result..

Yes, we got the field 1 and field 2



So, we know that to include the new Fields to the Template, you need to add them into the Form.

What if I Hide The Field in the Form?

Okay, let’s hide the ‘field 1’



And here is the result..



I can still see the ‘Field 1’, so, the Visibility set in the Form doesn’t give any effect.

What if I Make The Field as “read-only’ in the Form?

As we know, the Visibility didn’t give any effect. Okay, let’s disable the ‘field 1’ or make it ‘read-only’



And this is the result..



Conclusion, the ‘read-only’ setting didn’t give any impact.

What if I have Multiple Forms?

Let’s create a new field: field 3

Field 3, a Whole Number field.



Then, let’s create a new Form, and just include the field 3 to the form.



And..download the template..



Cool.. It has ‘Field 3’.

So, if you have multiple forms and those forms are enabled for the users (I am System Administrator), it will give you all of the combined columns in the Form.

What if I Deactivate the Forms?

Now, I try to deactivate all previous forms and create new new form with very minimum fields on it, only two fields I have and I published it (remember you need to publish forms before taking any effect)

Result:

There is no effect in the Template after you deactivate the forms.

What if I have Role-Based Multiple Forms?

Now, it goes to the next experiment, let’s assign the Form 1 to Everyone and Form 2 to System Administrator only.

Form 1



It has Name, Owner, Field 1, and field 2.

Form 2



It has Name, Owner, and Field 3.

Now, I login as System Administrator..

I have two forms enable for me to choose.



And this is the result…



Since I have been assigned to System Administrator and System Administrator was assigned to 2 role-based Forms, I can see all of the columns combination from Form 1 and Form 2, clearly, I can see Name, Owner, Field 1, field 2, and Field 3.

Next, I login as Salesperson…

I have only 1 Form.



And this is the result…



Wow..I can still see the ‘Field 3’ even though I am not having right to access the Form 2 which the ‘Field 3’ belongs to.

So, the conclusion, the Role-Based Form didn’t give any impact to the ‘Import Template’ it will still show all of the columns as long as those fields are defined in the (Multiple) Forms.

What if I have Implemented Field Security Profiles?

Okay, the next experiment is ‘what if I have Field-Level Security Implemented in the Entity Project and Field 1?’





Next, I login as the Salesperson.



See the result..



I cannot see the ‘Field 1’ as per our expectation.

What if I increase the access to ‘can Read’



Result:



I am still not able to get the ‘Field 1’

What if I increase the access to ‘can Read’ and ‘can Update’



Result:



No ‘Field 1’

What if I increase the access to ‘can Read’ and ‘can Update’ and also ‘can Create’



Result:



Yes, we get the ‘Field 1’

What if I set the access to ‘can Create’ only without ‘can Read’ and ‘can Update’



Result:



So, the Field Security Profile will give effect, only user who has ‘Allow Create’ access in Field-Level Security Profile can see the field in the Import Template.

What if I Set the Field setting: ‘Searchable’ to ‘No’?




Result:



The’' ‘searchable’ didn’t give any effect.

What if I Create New Mandatory Field But I have Not Put It In Any Form?

I try to create a new field with Business Requirement I set to 'Business Required'

Result:

The field will be automatically added to the Template as long as it's set to 'Busines Required', not other Field Requirement Types.

What if Users don’t have ‘Append’ Privilege?

I add new field lookup to Account



I show the field on the form.



I assign Salesperson role:



I login as the Salesperson.

I cannot select any Account.



I can still download the template and modify it.



I import the .XML file



It is automatically mapped to the correct entity based on the .XML file name.

And this is the result.




.

So, even though user can download the template, it doesn’t mean that they can break the security role.

What if Users don’t have ‘Create’ Privilege to the entity record, but I can download the Template, so what will happen next?

I assign Salesperson role:



I can still download the template





But..

When I was trying to Import, I cannot find the Project.xml file was mapped automatically to entity Project as usual.



And I cannot find the entity: Project in the entity list.



So, if you don’t have access to create new record for certain entities, don’t wish that you can upload the data using Import Wizard . Once again, it works as it by design and based on the security purpose expectation.

How to Prevent Users Import Data and Download the Import Template?

To block the Import function, you need to remove access ‘Data Import’ Access in the Security Role.
I found that remove ‘the Read’ is good enough.



Result:



Additional Information

1. Field Requirement Setting

If you notice the Template:



Only ‘Name’ that has ‘Bold’ style, the rest are in Normal Font Style.

So, how to make it ‘Bold’

Change the ‘Field Requirement’ from Optional to Required:



Result:



‘field 2’ becomes the Mandatory Field and has Bold style.

2. The Power of Description

If you notice back the ‘Name’ in this template:



Beside it is written in Bold style, if you click the header in, you also have additional Information:

Name – The name of the custom entity.
Where does it come from??

And why the field 2 header, when I hover or click, it didn’t show me any additional information.



Please see my blog post for detail info: The Power of Description

You can actually add the Description to the field 2



And it will show into your template as well..



You want to send the template to the users and you don’t have time to access CRM?
You can add or modify the description through modifying the .XML file:



Result:



3. It has the expected Validation







Summary:

1. Import Template is very nice Template to help users to speed-up Importing Data to CRM, especially for new users to create new data without any experience. Because it has the Validation, Mandatory Fields, Description, and they don’t need to bother about any field other than defined in the Template.

2. If you have Non-Role-Based Multiple Forms and role-based Multiple Forms (no matter the status, Active or not, as long as it's been published), all of the Fields defined in the all of The Forms (doesn’t care about the Visibility setting) will appear in the Template.

3. Import Template supports the Field Level Security Profile. Thus, if the users don’t have privilege to create, it will not appear in the Template.

4. Even though the Users can Download the Template, but CRM stills block some features according to the respective privilege in the assigned Security Roles to the respective Users.

5. Remove the Data Import Read privilege if you want to block users download the template and do import data.

6. The validation covering Date Time, Numbering, Text, and Option Set, and also description for lookup, and it helps you to prevent user choose the Option Set value wrongly because it has validation and the lis tof available option, it prevents users create another new Option Set if you forgot to create as Global Option Set.

See this scenario here:

http://missdynamicscrm.blogspot.com/2014/03/optionsetvaluesuddenlyautoadded.html

7. Any mandatory field (set to 'Business Required') will be included in the Template no matter its position is in the Form or Not.

Have any additional conclusion? Just give us new comment.

Hope this can help you to master ‘Import Template’ feature.

Thank you!