GoogleMaps within MSCRM

Here’s a way to have GoogleMaps integrated with Microsoft Dynamics CRM.

The aim in this example is as follows: On the account form show the route to the account, having:
- address1 as the destination point
- a flexible starting point (home, office or otherwise)

The setup used is as follows:
- a new tab named ‘GMaps’
- a new section on the tab 
- a new picklist field named ‘new_gmstartpointselect’ values ‘Office’, ‘Home’ and ‘Otherwise’
- a new nvarchar field named ‘new_gmstartpoint’
- a new IFRAME named ‘IFRAME_GMaps’

Make sure you have the following attributes on the account form to set destination address:
- address1_line1
- address1_line2 (used for housenumber in this example)
- address1_city

In everyday use end users may want to use different starting points. In this example I assumed that the user might want to depart from a) a predefined office address or b) from the end users home address or c) any other address.

To use the end user address, make sure to fill out address1 on the user form. 

On the picklist field ‘new_gmstartpointselect’ a javascript is used to determine the starting point:

if(crmForm.all.new_gmstartpointselect.SelectedText == "Home")
{
var xml = "" +
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
GenerateAuthenticationHeader() +
" <soap:Body>" +
" <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
" <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
" <q1:EntityName>systemuser</q1:EntityName>" +
" <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
" <q1:Attributes>" +
" <q1:Attribute>address1_line1</q1:Attribute>" +
" <q1:Attribute>address1_line2</q1:Attribute>" +
" <q1:Attribute>address1_postalcode</q1:Attribute>" +
" <q1:Attribute>address1_city</q1:Attribute>" +
" <q1:Attribute>address1_country</q1:Attribute>" +
" </q1:Attributes>" +
" </q1:ColumnSet>" +
" <q1:Distinct>false</q1:Distinct>" +
" <q1:Criteria>" +
" <q1:FilterOperator>And</q1:FilterOperator>" +
" <q1:Conditions>" +
" <q1:Condition>" +
" <q1:AttributeName>systemuserid</q1:AttributeName>" +
" <q1:Operator>EqualUserId</q1:Operator>" +
" </q1:Condition>" +
" </q1:Conditions>" +
" </q1:Criteria>" +
" </query>" +
" </RetrieveMultiple>" +
" </soap:Body>" +
"</soap:Envelope>" +
"";
var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);

var resultXml = xmlHttpRequest.responseXML;
var entityNode = resultXml.selectSingleNode("//RetrieveMultipleResult/BusinessEntities/BusinessEntity");

var line1Node = entityNode.selectSingleNode("q1:address1_line1");
var line2Node = entityNode.selectSingleNode("q1:address1_line2");
var postalcodeNode = entityNode.selectSingleNode("q1:address1_postalcode");
var cityNode = entityNode.selectSingleNode("q1:address1_city");
var countryNode = entityNode.selectSingleNode("q1:address1_country");

var line1 = (line1Node == null) ? "" : line1Node.text;
var line2= (line2Node == null) ? "" : line2Node.text;
var postalcode = (postalcodeNode == null) ? "" : postalcodeNode.text;
var city = (cityNode == null) ? "" : cityNode.text;
var country = (countryNode == null) ? "" : countryNode.text;

if((line1 == null) || (line1 == ""))
    {
        alert("End User Home address undefined");
    }
    else
    {
        crmForm.all.new_gmstartpoint.DataValue = line1 + "  " + line2 + ", " + city + ", " + country;
        crmForm.all.new_gmstartpoint.FireOnChange();
    }
}

if(crmForm.all.new_gmstartpointselect.SelectedText == "Office")
{
    crmForm.all.new_gmstartpoint.DataValue = "Amsterdamsestraatweg 1, Utrecht, Nederland";
    crmForm.all.new_gmstartpoint.FireOnChange();   
}

if(crmForm.all.new_gmstartpointselect.SelectedText == "Otherwise")
{
    crmForm.all.new_gmstartpoint.DataValue = null;
    crmForm.SetFieldReqLevel("new_gmstartpoint", 1);
}

The first part of this script retrieves the end users address when ‘Home’ is chosen. When ‘Office’ is chosen, a predefined address (Amsterdamsestraatweg 1, Utrecht, Nederland) is used. The script sets the value of ‘new_gmstartpoint’ is set and the onchange is fired. When ‘Otherwise’ is chosen, the end user will have to fill out the starting point manually.

On the ‘new_gmstartpoint’ field is a second javascript that will check if both starting point and destination are filled. If so, the innerHTML of the IFRAME will be replaced by the code needed to show GoogleMaps.

if((crmForm.all.new_gmstartpoint.DataValue == null) ||(crmForm.all.new_gmstartpoint.DataValue == ""))
{
    alert("Startingpoint undefined");
}
else if ((crmForm.all.address1_line1.DataValue == null) ||(crmForm.all.address1_line1.DataValue == "")||(crmForm.all.address1_city.DataValue == null)||(crmForm.all.address1_city.DataValue == ""))
{
    alert("Destination undefined");
}
else
{
    document.getElementById("IFRAME_GMaps_d").innerHTML = "<iframe id=IFRAME_GMaps_d class=ms-crm-Custom width='100%' height='100%' frameborder='0' scrolling='no' marginheight='0' marginwidth='0' src='http://maps.google.com/maps?f=d&amp;source=s_d&amp;saddr=" + crmForm.all.new_gmstartpoint.DataValue + "&amp;daddr="  + crmForm.all.address1_line1.DataValue +  "  " +crmForm.all.address1_line2.DataValue + ", " + crmForm.all.address1_city.DataValue +   "&amp;hl=nl&amp;output=embed'></iframe>";
}

If the end user wants to, he or she can just change the value of starting point and the map will refresh.

To clear the starting point fields when saving the account, just put the next line on the onsave event of the form:

crmForm.SetFieldReqLevel("new_gmstartpoint", 0); crmForm.all.new_gmstartpoint.DataValue = null;
crmForm.all.new_gmstartpointselect.DataValue = null;

Some final remarks: to view the actual directions and duration, it is possible to click the Google logo in the lower left-hand corner. This will open a new screen.

It is possible to add fields for more accurate address specification.
Integration with Google Maps will not work when offline.
This blog post does not deal with any licensing  or cost issues. Please check Google for more info.

 

Unit set automatically with Default Unit

When setting up a new product in the product catalog, Default Unit is a system required field.

Great! So when you choose the product on the the opportunity/quote/order/invoice-product screen the Default Unit is automatically filled in in the Unit field… NOT!

As most users will want to choose the default unit, let’s set it automatically. To do so, add the next script to the OnChange event of the product field:

GetAttributeValueFromID= function(sEntityName, sGUID, sAttributeName,sOrganizationName)
{
var xml = "" +
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" + GenerateAuthenticationHeader() +
" <soap:Body>" +
" <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
" <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
" <q1:EntityName>" + sEntityName +"</q1:EntityName>" +
" <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
" <q1:Attributes>" +
" <q1:Attribute>" + sAttributeName + "</q1:Attribute>" +
" </q1:Attributes>" +
" </q1:ColumnSet>" +
" <q1:Criteria>" +
" <q1:FilterOperator>And</q1:FilterOperator>" +
" <q1:Conditions>" +
" <q1:Condition>" +
" <q1:AttributeName>"+ sEntityName + "id</q1:AttributeName>" +
" <q1:Operator>Equal</q1:Operator>" +
" <q1:Values>"+
"<q1:Value xsi:type=\"xsd:string\">" + sGUID + "</q1:Value>" +
"</q1:Values>" +
" </q1:Condition>" +
" </q1:Conditions>" +
" </q1:Criteria>" +
" </query>" +
" </RetrieveMultiple>" +
" </soap:Body>" +
"</soap:Envelope>" +
"";
var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction"," http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);
var resultXml = xmlHttpRequest.responseXML;
if (resultXml != null)
{
var names = resultXml.selectNodes("//BusinessEntity/q1:" + sAttributeName);
if (names != null)
{
if (names.length > 0)
{
return names[0].text;
}
}
//return "*Error*";
}
}

//Set unit with default unit of product using CRM webservice
if(crmForm.all.productid.DataValue != null)
{
    var ProductSelected = new Array;
    ProductSelected = crmForm.all.productid.DataValue;

    var UnitId = "";
    var UnitDescription = "";
    UnitId = GetAttributeValueFromID("product",ProductSelected[0].id,"defaultuomid");
    UnitDescription = GetAttributeValueFromID("uom",UnitId,"name");

    var InputUnitId = new Array();
    InputUnitId[0] = new LookupControlItem (UnitId, 1055, UnitDescription);
    crmForm.all.uomid.DataValue = InputUnitId;
}
else
{
    crmForm.all.uomid.DataValue = null;
}

 

This will retrieve the default unit from the product and fill out the unit field as the product is set.

In this way, the end user still has the posibillity to choose another unit if necessary. But if not, is saves a couple of clicks!

Add Related Entity Button on Form

Say you are a car lease company. As a CRM user you want an easy way to register a lease made by a contact. Every unnecessary click is one too many. You just want to check contact details and click ‘New Car Lease’.

Within the ISV config XML you can define custom buttons. All you need is the function that is used when a user clicks Car Leases –> New Car Lease. With some investigation you will find that the javascript function used looks something like:

locAddRelatedToNonForm(10006,2,'{5B20590A-37D0-DC11-AA32-0003FF33509E}', '')

Let’s take a closer look: “locAddRelatedToNonForm” is the function used, “10006” is the entitytypecode for car lease, “2” is the entitytypecode for contact, and “{5B20590A-37D0-DC11-AA32-0003FF33509E}” is the GUID of the specific contact that the function is called from. Looks like something we can reproduce for any record. Just need to replace the hardcoded numbers with the right variables.

This is where my last post, Retrieve EntityTypeCode on Entity Name, comes in handy. The first ETC needed is of the related entity. In my case the new entity is named ‘bd_carlease’. To  retrieve the ETC, use GetEntityTypeCode('bd_carlease')*.

The second one, for contact, is already available on the form, just use crmForm.ObjectTypeCode. And the GUID is also available with crmForm.ObjectId.

So all you need to do, is edit the ISV config XML like so:

<Entity name="contact">
    <ToolBar ValidForCreate="0" ValidForUpdate="1">
        <Button Icon="/_imgs/car16.gif" JavaScript="locAddRelatedToNonForm(GetEntityTypeCode('bd_carlease'), crmForm.ObjectTypeCode, crmForm.ObjectId, '');" Client="Web">
            <Titles>
                <Title LCID="1033" Text="New Car Lease" />
            </Titles>
            <ToolTips>
                <ToolTip LCID="1033" Text="New Car Lease" />
            </ToolTips>
        </Button>
    </ToolBar>
</Entity>

And import it in MS CRM.

That’s all!

* make sure the you have the Retrieve EntityTypeCode funcions on the onload of the form.

Retrieve EntityTypeCode on Entity name

When customizing MS CRM you will sometimes need the EntityTypeCode (ETC) of a certain entity. The problem is that the ETC for custom entities isn’t always the same for a specific entity in different CRM environments. So the ETC for let’s say ‘new_project’ may be different in a test-environment than in a production-environment. This means that when using ETC’s in your (javascript) customizations, you will have to check and if necessary change the ETC in two places.

To overcome this problem, you can make use of a generic javascript that retrieves the ETC from Microsoft Dynamics CRMs Metadata. The first part is a generic function that is used to access the MS CRM metadata webservice.

QueryMetadataService = function(request) {

var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");

xmlhttp.open("POST", "/mscrmservices/2007/MetadataService.asmx", false);

xmlhttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");

xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Execute");

var soapMessage = "<?xml version='1.0' encoding='utf-8'?>" +

"<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " +

"xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" +

"<soap:Header>" +

"<CrmAuthenticationToken xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>" +

"<AuthenticationType xmlns='http://schemas.microsoft.com/crm/2007/CoreTypes'>" + AUTHENTICATION_TYPE + "</AuthenticationType>" +

"<OrganizationName xmlns='http://schemas.microsoft.com/crm/2007/CoreTypes'>" + ORG_UNIQUE_NAME + "</OrganizationName>" +

"<CallerId xmlns='http://schemas.microsoft.com/crm/2007/CoreTypes'>00000000-0000-0000-0000-000000000000</CallerId>" +

"</CrmAuthenticationToken>" +

"</soap:Header>" +

"<soap:Body><Execute xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>" + request+ "</Execute></soap:Body>" +

"</soap:Envelope>";

xmlhttp.send(soapMessage);

return xmlhttp.responseXML;

}

Now to call this function to access MS CRMs Metadata webservice and retrieve the ETC based on the entityname. For this, you can call the next javascript function:

GetEntityTypeCode = function(entityName) {

    var request = "<Request xsi:type='RetrieveEntityRequest'>" +

        "<MetadataId>00000000-0000-0000-0000-000000000000</MetadataId>" +

        "<EntityItems>EntityOnly</EntityItems>" +

        "<LogicalName>" + entityName + "</LogicalName>" +

        "<RetrieveAsIfPublished>true</RetrieveAsIfPublished>" +

        "</Request>";

    var result = QueryMetadataService(request);

    var entityTypeCode = result.selectNodes("//EntityMetadata/ObjectTypeCode")[0].text;

    //alert(entityTypeCode);

    return(entityTypeCode);

}

For Example, if you want to retrieve the ETC of ‘new_project’, just make sure the two functions shown before are on the OnLoad of your CRM Form and call the function like this: 
var projectEtc = GetEntityTypeCode("new_project");

This will allow you to write your code only once, without worrying whether or not you have the right ETC in your scripts.

My next post will be an example on how to use this in real life customizing.

Bertil

P.S. The first function (QueryMetadataService) I found at the CustomerEffective blog and inspired me to create the second function. You can some other examples on what you can retrieve using the metadataservice on their blog.

How to update CRM data from Excel

With CRM 4.0 it is possible to export data to Excel.
A much heard question is if it’s also possible to re-import changed data from Excel into CRM. That means an update of the existing data.

The next steps will explain how to update the data in the mobile phone field for the contact entity. Only using the default import functionality of CRM.

Step 1: Export and edit existing data
The easiest way to obtain the necessary data is to use Advanced Find. Select the contacts you want to edit the mobile phone numbers using Advanced Find and choose to export to Excel.

   Figure: all contacts have same mobile phone number.

When exporting, use the option ‘Dynamic Worksheet’.

 
   Figure: choose Dynamic worksheet.

Using a Dynamic worksheet export, causes the CRM GUIDs to be exported as well. This is a hidden column which can easily be unhidden. Just select the columns, rightclick  and choose unhide.

   Figure: unhide CRM GUIDs

Make sure that the column with the GUIDs becomes the first column in the sheet and rename the column header to the RM entity’s single name, i.e. Contact.

Edit the data in the column you want to update and remove unchanged columns.

  Figure: Edited data, GUIDs first 

When all necessary data is changed, save the file as a .csv-file.

Step 2: Import Data
With the standard import the .csv-file can be imported in MS CRM.

  Figure: import data

Select the file and set the correct field delimiters.

  Figure: set field delimiters 

CRM will recognize that an existing entity is imported and will show an automatic Data map. It will also show a field you normally don’t see when importing data “Enrich data by updating existing records rather than creating new records”. This field is already selected.  

When clicking Next, there is a screen where duplicate check options can be set (which will not be there as every GUID is unique). Clicking through the screen will start the import. 
When the import has completed, the CRM records will be updated.

  Figuur  - The contacts have a new mobile phone number.

Conclusion: with het steps mentioned above it is possible to export-update-import the data from MS CRM to Excel and back without any third party tools.

So does this work for every field? Unfortunately the answer is: No. The mobile phone field example used is a free text field (type: nvarchar). I haven’t tried all of the field types available in CRM, but lookup fields for example I have not been able to update. So depending on the field you will or will not be able to update. Tip: try your business case in your test environment first!

Happy updating!

What About…. ExAMining MSCRM??

Welcome to my new Blog. Clearly it’s dedicated to Microsoft Dynamics CRM. But what about ‘ExAMining’? Over the last couple of years I have seen lots of blogs about MS CRM. And lots of them I still follow with much interest. But still I believe there is room on the web for a blog that has something to add to the information available already. This is what this blog is about: Examples And More… on Microsoft CRM.

So why would this add anything to the information already available? Well, to my experience one part of the blogs about MS CRM contains general information, news facts and business stories and another part is more technical and contains javascripts and other types of ‘code’. Now the aim of this blog is to give practical examples of how MS CRM can be customized in a way to make life easier for end users. It will contain (a lot of) javascript, but by giving examples on how to use it in everyday customizations I’ll try to show the practical use of the code used.

Why this explanation in advanced you might think. Well, that’s the good news: I hope my examples inspire you to create your own use. And as a result return the favor with posting your example. So in the end.. we all benefit!