Sunday, 7 September 2014

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’.

image

Default ‘Import Template’ from Fresh Custom Entity


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

image

And this is the result…

image

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.

image

Field 2, a Two Options field.

image

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.

image

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).

image

Download the template…

image

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.


image

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

I add the field 1 and field 2.

image

And Wow…This is the result..

Yes, we got the field 1 and field 2

image

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’


image

And here is the result..

image

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’


image

And this is the result..

image

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.


image

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

image

And..download the template..

image

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

image

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

Form 2

image

It has Name, Owner, and Field 3.

Now, I login as System Administrator..

I have two forms enable for me to choose.

image

And this is the result…

image

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.

image

And this is the result…

image

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?’


image

image

Next, I login as the Salesperson.

image

See the result..

image

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

What if I increase the access to ‘can Read’

image


Result:

image

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

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

image


Result:

image

No ‘Field 1’

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

image


Result:

image

Yes, we get the ‘Field 1’

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

image


Result:

image

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’?

image



Result:

image

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


image

I show the field on the form.

image

I assign Salesperson role:

image

I login as the Salesperson.

I cannot select any Account.

image

I can still download the template and modify it.

image

I import the .XML file

image

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

And this is the result.

image


image.

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:


image

I can still download the template

image

image

But..

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

image

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

image

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 Smile. 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.

image

Result:

image

Additional Information

1. Field Requirement Setting


If you notice the Template:

image

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:

image

Result:

image

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

2. The Power of Description

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

image

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.

image

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

You can actually add the Description to the field 2

image

And it will show into your template as well..

image

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:

image

Result:

image

3. It has the expected Validation

image

image

image

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!

8 comments:

  1. Great article Aileen, informative and thorough! Thanks for sharing.

    ProgCRM

    ReplyDelete
  2. Hi ProgCRM, thanks for your positive feedback :)

    ReplyDelete
  3. Working with CRM for 5 years but never experiment this much with Import template.
    Many thanks for bringing this up. Great Job !

    John B

    ReplyDelete
  4. Hi John, thanks for your positive feedback!

    ReplyDelete
  5. Very good article. Keep it up!

    Jef

    ReplyDelete
  6. Very information article and really comprehensive stuff. Great work!

    ReplyDelete
  7. Very Informative article! however I am facing issue with import template. When I try to import new records for Product entity, the text fields(custom) are ignored. i.e even if I map them correctly, the fields shows up blank after the import completes successfully

    ReplyDelete
  8. Hi!

    I am trying to add the Status Reason field to the template from Opportunity. I need to change the Status reason for Lost Opps and it seems I cannot find a way to change. I thought on export/importing to change the option but the field is not appearing in the template...could you help?

    Please and thank you!
    -Natalie

    ReplyDelete

My Name is..