One of the questions that was asked several times, is if it is possible to have a central Country table within CRM.
As it is not possible to have multiple relations between two entities (contact for example has two country fields in the address part and you might have a field like nationality), a custom entity by itself does not solve the problem.
Working with picklists requires to keep the values of the different picklists in sync and it's not the most user friendly way to select a country.
In search for a solution for this problem I came across
Joris Kaltz's blog where he posted an example for a type ahead country field. In this great example he showed how to create the type ahead box, but it was filled with a static list. Then I remembered reading
Arash Ghanaie's post on retrieving CRM data through CRM Webservices.
Combine the two and you can read the country values from your own country table!!!
Here's the result for the onload script of the form:
--------------------------------
function SuggestionTextBox(textfield, method, preload)
{ // max items in the suggestion box
var maxItems = 6;
this.suggestionList = new Array();
this.suggestionListDisplayed = new Array();
var actual_textfield = textfield;
var actual_value = '';
var selectedNumber = 0;
var countMatches = 0;
if (preload)
{
// load the data via external method
this.suggestionList = method();
}
// attach this function to the textfield
textfield.attachEvent("onfocus", initTextfield);
// init textfield and attach necessary events
function initTextfield()
{
// when leaving the field we have to clear our site
textfield.attachEvent("onblur", resetTextfield);
document.attachEvent("onkeydown", keyDown);
}
function resetTextfield(e)
{
//when leaving the field, we have to remove all attached events
document.detachEvent("onkeydown", keyDown);
textfield.detachEvent("onblur",resetTextfield);
}
function keyDown(e)
{
keyCode = e.keyCode;
switch (keyCode)
{
case 9: case 13:
// enter & tab key
if (countMatches > 0)
itemSelected();
break;
case 38:
//pressing up key
if(selectedNumber > 0 && countMatches > 0)
{
selectedNumber--;
createSuggestionTable();
}
return false;
break;
case 40:
// pressing down key
if(selectedNumber < countMatches-1 && countMatches > 0 && selectedNumber < maxItems)
{
selectedNumber++;
createSuggestionTable();
}
return false;
break;
default:
// do not call the function to often
setTimeout(
function()
{
executeSuggestion(keyCode)
}, 200 /* in ms */
);
break;
}
}
function itemSelected()
{
actual_textfield.value = suggestionListDisplayed[selectedNumber];
if (document.getElementById('suggestion_table') != null)
{
document.body.removeChild(document.getElementById('suggestion_table'));
}
}
function executeSuggestion(keyCode)
{
selectedNumber = 0;
countMatches = 0;
actual_value = textfield.value;
//todo add keyCode
// get all possible values from the suggestionList
if (!preload)
{
// load the data via external method
// todo add some caching function
this.suggestionList = method();
}
// using regular expressions to match it against the suggestion list
//var re = new RegExp(actual_value, "i");
//if you want to search only from the beginning
var re = new RegExp("^" + actual_value, "i");
countMatches = 0;
this.suggestionListDisplayed = new Array();
// test each item against the RE pattern
for (i = 0; i < this.suggestionList.length; i++)
{
// if it matche add it to suggestionListDisplayed array
if (re.test(this.suggestionList[i]) && actual_value != '')
{
this.suggestionListDisplayed[countMatches] = this.suggestionList[i];
countMatches++;
// if there are more values than in maxItems, just break
if (maxItems == countMatches)
break;
}
}
if (countMatches > 0)
{
createSuggestionTable();
}
else
{
if (document.getElementById('suggestion_table'))
{
document.body.removeChild(document.getElementById('suggestion_table'));
}
}
}
function createSuggestionTable()
{
if (document.getElementById('suggestion_table'))
{
document.body.removeChild(document.getElementById('suggestion_table'));
}
// creating a table object which holds the suggesions
table = document.createElement('table');
table.id = 'suggestion_table';
table.width = actual_textfield.style.width;
table.style.position= 'absolute';
table.style.zIndex = '100000';
table.cellSpacing = '1px';
table.cellPadding = '2px';
topValue = 0;
objTop = actual_textfield;
while(objTop)
{
topValue += objTop.offsetTop;
objTop = objTop.offsetParent;
}
table.style.top = eval(topValue + actual_textfield.offsetHeight) + "px";
leftValue = 0;
objLeft = actual_textfield
while(objLeft)
{
leftValue += objLeft.offsetLeft;
objLeft = objLeft.offsetParent;
}
table.style.left = leftValue + "px";
table.style.backgroundColor = '#FFFFFF';
table.style.border = "solid 1px #7F9DB9";
table.style.borderTop = "none";
document.body.appendChild(table);
// iterate list to create the table rows
for ( i = 0; i < this.suggestionListDisplayed.length; i++)
{
row = table.insertRow(-1);
row.id = 'suggestion_row' + (i);
row.attachEvent("onclick", selectedwithmouse);
column = row.insertCell(-1);
column.id = 'suggestion_column' + (i);
if (selectedNumber == i)
{
column.style.color = '#ffffff';
column.style.backgroundColor = '#316AC5';
}
else
{
column.style.color = '#000000';
column.style.backgroundColor = '#ffffff';
row.attachEvent("onmouseover", hoverwithmouse);
}
column.style.fontFamily = 'Tahoma';
column.style.fontSize = '11px';
column.innerHTML = this.suggestionListDisplayed[i];
}
}
function hoverwithmouse()
{
var itemNumber = event.srcElement.id.replace('suggestion_column','');
if (!isNaN(itemNumber))
{
selectedNumber = itemNumber;
createSuggestionTable();
}
}
function selectedwithmouse()
{
var itemNumber = event.srcElement.id.replace('suggestion_column','');
if (!isNaN(itemNumber))
{
selectedNumber = itemNumber;
itemSelected();
}
}
// return object
return this;
}
function AccessCRMWebServices()
{
var serverUrl = "http://crmdemo:5555/mscrmservices/2006";
var xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
xmlhttp.open("POST", serverUrl + "/crmservice.asmx", false);
xmlhttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8")
xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2006/WebServices/RetrieveMultiple")
xmlhttp.send("<?xml version='1.0' encoding='utf-8'?>"+"\n\n"+"<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">'+
' <soap:Body>' +
' <query xmlns:q1="http://schemas.microsoft.com/crm/2006/Query" xsi:type="q1:QueryByAttribute" xmlns="http://schemas.microsoft.com/crm/2006/WebServices">'+
' <q1:EntityName>new_country</q1:EntityName>'+
' <q1:ColumnSet xsi:type="q1:ColumnSet">'+
' <q1:Attributes>'+
' <q1:Attribute>new_name</q1:Attribute>'+
' </q1:Attributes>'+
' </q1:ColumnSet>'+
' <q1:Attributes>'+
' <q1:Attribute>statecode</q1:Attribute>'+
' </q1:Attributes>'+
' <q1:Values>'+
' <q1:Value xsi:type="xsd:string">Active</q1:Value>'+
' </q1:Values>'+
' </query>'+
' </soap:Body>'+
' </soap:Envelope>')
var result = xmlhttp.responseXML.xml;
//Separate the BusinessEntities XML tag
var BEs= result.split("<BusinessEntities>");
//Separate the BusinessEntity XML tag
var BE = BEs[1].split("<BusinessEntity");
//Walk through each Business Entity tag and extract account names
var landenstring = ""
for (i = 1; i < BE.length; i++)
{
first = BE[i].indexOf("<new_name>")+10;
second = BE[i].indexOf("</new_name>");
var s = BE[i].substring(first,second);
landenstring = landenstring + BE[i].substring(first,second) + ",";
}
landenstring = landenstring.substring(0, landenstring.length - 1);
var landenArray = landenstring.split(",");
return landenArray;
}
var obj = SuggestionTextBox(document.getElementById('address1_country'), AccessCRMWebServices, true);
--------------------------------
In the example above, we've created a new entity called new_country and put the name of the country in the new_name field. There's a check on the status of the records, so only active country's will be in the type ahead box.
* Thanks go out to my colleague Michel van Duijse who helped me out with splitting the result of the webservicecall.