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!

CRM 2013: Are You Missing ‘Imports’?

A brief post, are you feeling not comfortable to find where is your ‘Import Job Status’ after you do the Import function.

We know that after we submitted the data for import, we can check the status by clicking this ‘Imports’ hyperlink.




But.. I realize that the users often get missed this link which they will ask to you where the place to check their data status, imported successfully or not.

In CRM 2013, under the workplace sitemap, we have this link and easy to access:



But, in CRM 2013 (especially for organization not from upgraded CRM 2011) will not have this shortcut.

I know and I know we know that we can just go to the Settings –> Data Management –> Imports

But, it can be very troublesome, especially if you want to hide sitemap in CRM and want to prevent the common users to access the Settings area.

So, I have to say that my customers miss this ‘Import’ in sitemap.

Thus, we need to add this.

http://xrmtoolbox.codeplex.com/

1. Go to Sitemap Editor.



2. Connect to the Organization.

*Existing Connection



*New Connection



3. Load SiteMap



4.  Add new Sub Area in the expected sitemap location group/area



*I put in the Sales –> Tools

5. If you select Entity, you cannot find the Import entity, then just type: ‘Import File’ in the entity name






6. Save and Update SiteMap



*You can save sitemap.xml backup as well before you Update SiteMap






7. Test the result.



*To show the ‘Import’ in the security role, you need to have ‘Data Import’ Read access.



Hope this helps you all!

Thank you.

CRM 2013 Using addCustomFilter() to get Filtered Lookup Field based on Linked Entity

We all have known that in CRM 2013 we can filter the lookup field using easier method compared to CRM 2011, that is using addCustomFilter() combined with addPreSearch() function.

This is one of the example how to use it, to filter contact based on email that contains @example.com

function onLoad()
{
    addEventHandler();
}

function addEventHandler() {
    // add the event handler for PreSearch Event
    Xrm.Page.getControl("parentcontactid").addPreSearch(addFilter);
}

function addFilter() {
   //find contact contains this @example.com
   var email = "%@example.com%";

    //create a filter xml
   var filter = "<filter type='and'>" +
                "<condition attribute='emailaddress1' operator='like' value='" + email + "'/>" +
                 "</filter>";

    //add filter
    Xrm.Page.getControl("parentcontactid").addCustomFilter(filter);
}

Call the onLoad function during form onLoad event.

That code is looks good and it will work, yes, it will.

And if you notice, this is a very simple way, compared to the old code, using addCustomView(), which you need to build your own custom view and don’t forget to build layoutXML as well.

This is the addCustomView().

Xrm.Page.getControl(arg).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault)

However, there is a limitation, not only that is because those functions can only work for lookup field in the Refreshed UI Entity Form (it means cannot work for Price List Item, Quote Product, etc.), but this filter, if you notice, it can only work to filter using the attribute that is a part of that entity. So, it cannot be used to filter an entity based on the field from another entity.

The above example is to filter contact by email address (email address is one of the contact attribute), what if we are going to filter an entity record based on another related entity, for example in the Lead form, it has Existing Contact lookup field, then the users want to filter to show only Contact based on the Account Number or based on the Website/Email Address of Account or based on the Account Name that compared to the obtained Company Name in Lead form .

If you notice, the filter that is supported is currently just using the node that is started from <filter>..

//create a filter xml
    var filter = "<filter type='and'>" +
                 "<condition attribute='emailaddress1' operator='like' value='" + email + "'/>" +
                 "</filter>";

However, from that XML we get (see the example below), if we are using another related entity as the filter, it is using <linked-entity>, which is not allowed in the addCustomFilter() method.

This is the example of the generated XML with criteria based on the another related entity’s field:

*The XML to get Contact based on Account’s email address.

.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
 <entity name="contact">
   <attribute name="fullname" />
   <attribute name="parentcustomerid" />
   <attribute name="telephone1" />
   <attribute name="emailaddress1" />
   <attribute name="contactid" />
   <order attribute="telephone1" descending="false" />
   <link-entity name="account" from="primarycontactid" to="contactid" alias="ab">
     <filter type="and">
       <condition attribute="emailaddress1" operator="like" value="%example%" />
     </filter>
   </link-entity>
 </entity>
</fetch>

So…

We cannot use these lines as variable filter.

<link-entity name="account" from="primarycontactid" to="contactid" alias="ab">
     <filter type="and">
       <condition attribute="emailaddress1" operator="like" value="%example%" />
     </filter>
</link-entity>

As we know that the only way is still using the old-fashioned CRM 2011 Code, that is using addCustomView() with many parameters that you might not want to build anymore.

Xrm.Page.getControl(arg).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault)

So, we should build our own fetchXML + layoutXML as well, in fact you have built your own view and you just need to filter it.

Then, as the alternative, you can use another code to find that related entity id, see this example:

var filter = "<filter type='and'>" +
             "<condition attribute='parentcustomerid' operator='eq' uiname='Fourth Coffee (sample)' uitype='account' value='{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}' />" +
             "</filter>";

    //add filter
Xrm.Page.getControl("parentcontactid").addCustomFilter(filter);

You can use OData Query to find the Account Id.

The best part that you should do is by getting the AccountId and I believe the OData Query is will be your best friend.
To get used using odata, you can refer to my another blog post:

http://missdynamicscrm.blogspot.com/2014/10/tips-and-trick-odata-crm-2011-2013.html

Another limitation in addCustomFilter() is it only supports 'And'
(based on this link:http://msdn.microsoft.com/en-us/library/gg334266.aspx#BKMK_addCustomFilter)

If you need more than one Account Id, then you might need to add Operator IN, see this example:

<filter type="and">
        <condition attribute="accountid" operator="in">
          <value uiname="Fourth Coffee (sample)" uitype="account">{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>
          <value uiname="Litware, Inc. (sample)" uitype="account">{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>
          <value uiname="Adventure Works (sample)" uitype="account">{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>
        </condition>
</filter>

You might choose whether you want to use addCustomView() with those many required parameters or addCustomFilter() with that limitation and have to find the ID, but with no need to pass many parameters as the addCustomView() required.

Summary:

1. To use addCustomFilter() to filter based on the same entity field:

function addFilter() {
   //find contact contains this @example.com
   var email = "%@example.com%";

    //create a filter xml
    var filter = "<filter type='and'>" +
                 "<condition attribute='emailaddress1' operator='like' value='" + email + "'/>" +
                 "</filter>";

    //add filter
    Xrm.Page.getControl("parentcontactid").addCustomFilter(filter);
}

2. To filter based on the related entity (one record only):

 //create a filter xml
 var filter = 
"<filter type='and'>" +
     "<condition attribute='parentcustomerid' operator='eq' uiname='Fourth Coffee” (sample)' uitype='account' value='{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}' />" +
"</filter>";

3. To filter based on the related entity (multiple records)

//create a filter xml 
var filter =
"<filter type='and'>" +
        "<condition attribute='accountid' operator='in'>" +
          "<value uiname='Fourth Coffee (sample)' uitype='account'>{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value uiname='Litware, Inc. (sample)' uitype='account'>{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value uiname='Adventure Works (sample)' uitype='account'>{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
        "</condition>" +
"</filter>";

//or you can just use this (uiname and uitype are not mandatory)

//create a filter xml
 var filter =
"<filter type='and'>" +
        "<condition attribute='accountid' operator='in'>" +
          "<value>{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value>{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value>{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
        "</condition>" +
"</filter>";

Result for Number 2 (Single Record):



Result for Number 3 (Multiple Records):



*Using the addCustomFilter() has advantage it will not break the column order and position you defined in the Customization, because you are just changing the criteria to filter, not creating a new custom view, so that you will not find any issue like I found and posted here.


Hope this helps!

Thanks.

Display the Missing Associated View in CRM 2013

Sometimes, you need to show the Associated View from the Relationship in CRM 2013, you know that adding new subgrid might be making your Form UI looks cluttered, then you should go to the Form Editor and add the Navigation.

But, some relationship are hidden and cannot be shown in CRM 2013.

So, how do we handle this?

For example, we cannot show the Associated View of related Lead from Account/Contact and we cannot show the related Lead owned by User in User form.

So, to handle this case, you should create a custom Navigation link + the custom Web Resource, you should refer first to this link, Thanks to Amreek Singh for the nice post:

http://mscrmshop.blogspot.com/2013/01/step-by-step-tutorial-to-add-missing.html

But, in CRM 2013, since the User Interface was changed and the parameters required were revamped as well, so the constructed URL will give error if you apply to CRM 2013.

So, still using the methods mentioned in that great post, in CRM 2013 you should add another parameter.
In the building URL, you should use this:

 //building a url 
var sUrl = "/userdefined/areas.aspx?oId=" + oId + "&oType=" + oType + "&pagemode=iframe&security=852023&tabSet=" + relName&rof=false&inlineEdit=1; 

Add the &inlineEdit=1 to show the CRM 2013 Command Bar (not Nav Bar)

So, the complete Web Resource code:

<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
    <title>Untitled Page</title>
    <style type="text/css">html,body{font-family: Segoe UI, Tahoma, Arial;background-color: #d6e8ff;border: 0px; margin: 0px; padding: 0px;}</style>
    <script type="text/javascript" language="javascript">

            //debugger;

            //get the context
            var Xrm = window.parent.Xrm;

            //get the current object id
            var oId = Xrm.Page.data.entity.getId();

            //get the object type
            var oType = Xrm.Page.context.getQueryStringParameters().etc;

            //set Relationship Name
            var relName = "lead_owning_user";

            //building a url var sUrl = "/userdefined/areas.aspx?oId=" + oId + "&oType=" + oType + "&pagemode=iframe&security=852023&tabSet=" + relName&rof=false&inlineEdit=1;
           
            //get relative path with orgname as required
            var relativeUrl = Xrm.Page.context.prependOrgName(sUrl);
            
            window.location = relativeUrl;
   
    </script>
</head>
<body>
</body>
</html>

And this is the appearance of that ‘custom Associated View’





and with InlineEdit = 1



*Remember, the behavior will be shown as expected if you show the web resource in the Navigation link in the parent Entity Form.

To make your job easier, you might try to test the URL first, copy it into the browser and execute it, before you put into the code.

The URL will be having this format:

http://servername:port/organizationname/userdefined/areas.aspx?oId=DB4A2D57-FBBA-E311-9403-  001CC4EECDD6&oType=1&pagemode=iframe&security=852023&tabSet=lead_customer_accounts&rof=false

Hope this helps!

Thanks.