Archive | MicrosoftCRM

Using PowerShell to extract all contacts from MS CRM 2011

We are moving to Salesforce from MSCRM 2011. We need to get our data out so we can import into Salesforce. Here is the PowerShell script I am using to export contacts to csv.

$url="http://crm.sardverb.com/Company/xrmservices/2011/OrganizationData.svc/ContactSet?`$filter=StatusCode/Value eq 1"

$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$count=0
$output = @()

while ($url){
    function GetData ($url) {
    $webclient = new-object System.Net.WebClient
    $webclient.UseDefaultCredentials = $true
    $webclient.Headers.Add("Accept", "application/json")
    $webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
    $data=$webclient.DownloadString($url)
    return $data
    }
    $data=GetData($url) | ConvertFrom-Json
    $output += $data
    $count=$count+$data.d.results.length
    write-host $count
    if ($data.d.__next){
        #$url=$null
        $url=$data.d.__next.ToString()
    }
    else {
        $url=$null
    }
}

$output.d.results | Select -ExcludeProperty ParentCustomerId,__metadata @{l="ParentCustomerID";e={$_.ParentCustomerID.Id}},* | Export-Csv -NoTypeInformation C:\Contact.csv

Hope that helps someone.

0

When using PowerShell to pull REST data from MS CRM, escape `$filter !

Note to self.

When trying to filter a REST response in PowerShell, by using the “$filter” parameter in the url (as with MS CRM 2011), you must escape the “$” with “`$”.

For example:

Does not work:
$url=”http://crmserver.company.com/Organization/xrmservices/2011/OrganizationData.svc/ContactSet$filter=StateCode/Value eq 0″

Works:
$url=”http://crmserver.company.com/Organization/xrmservices/2011/OrganizationData.svc/ContactSet`$filter=StateCode/Value eq 0″

Gets me every time, and I can’t figure out why my filters are being ignored!

2

A simple javascript/AJAX function to post a SOAP request to CRM 2011

This is a simple function that I use to post a SOAP envelope to CRM 2011. Just pass the URL and the xml (you can create that with this function) and you should be good to go

    function soapToCRM(URL, data) {
        var returnValue
        $.ajax({
            type: "POST",
            contentType: "text/xml; charset=utf-8",
            datatype: "xml",
            async: false,
            url: URL,
            data: data,
            beforeSend: function (XMLHttpRequest) {
                XMLHttpRequest.setRequestHeader("Accept", "application/xml, text/xml, */*");
                XMLHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
                XMLHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute");
            },
            success: function (data, textStatus, XmlHttpRequest) {
                //alert("success");
                var NewCRMRecordCreated = data["d"];
                returnValue = true
            },
            error: function (XMLHttpRequest, textStatus, errorThrown) {
                alert("failure " + errorThrown);
                returnValue = false;
            }
        });
        return returnValue;
    }

Using AJAX and SOAP to create a CRM 2011 activity

I have posted a bunch of PowerShell scripts to interact with CRM 2011, now I am going to put up similar javascript versions. I feel that there is a lot of javascript content out there for CRM 2011, but most of it is javascript inside the actual CRM interface. I don’t see a lot of content about using javascript from a different webpage/site with ajax, so I thought I would post some of the code I put together.

This is a function that I put together to build a SOAP envelope for creating an activity in CRM 2011. Credit for the template goes to Jamie Miley and this post. I took his template a little further and created a function for either email,Phone call or appointment. In addition this function allows for multiple contacts in an phone call’s to and multiple required contacts in a meeting.

Continue Reading →

How to hide a field in a SharePoint EditForm.aspx page, append a replica, add a jQuery autocomplete, and put the selected value in the original field.

If you look at this old post, you can see a technique that I used to hide a form field, and then append read-only data after it. I wanted to use this technique to hide a field in a form, append a replica with a jQuery autocomplete, and based on the selected value from the drop down, put the value in the original filed. I actually wanted a comma separated concatenation of all the selected values (multiple lookups). For example, I wanted to create a form to capture all the people at a meeting, the the attendees field would be hidden and replaced with a input box that can lookup contact GUIDs from a CRM, and once the contact is selected, the GUID is appended to the contents of the original Attendees field.

First up, the code to hide the field I want:

	attendeesRow='<tr id="attendeesRow"> \
		<td nowrap="true" valign="top" width="190px" class="ms-formlabel"><h3 class="ms-standardheader"><nobr>Attendee<nobr></h3></td> \
		<td valign="top" class="ms-formbody" width="400px"> \
		<div id="AddAttendees"><input type="text" id="AddAttendeesSearchTextbox" /> (add an EXISTING CRM Contact)</div><br/> \
		</td></tr>';
	$('nobr:contains("Attendees")').closest('tr').hide();
	$('nobr:contains("Attendees")').closest('tr').before(attendeesRow);

Lines 1-5 is the code for the new replica field
Line 6 hides the existing field
Line 7 prepends the new replicate created in Lines 1-5

Next is the jQuery code to attach an autocomplete to the new text box (AddAttendeesSearchTextbox). I am using a little knockout to organize my code and I use some of the observable arrays to make the page more dynamic.

$('#AddAttendeesSearchTextbox).autocomplete({
	    source: function (request, response) { VM.contactsSearchSourceREST(request, response) },
		delay: 600,
		minLength: 3,
		select: function(event, ui) {
			var selectedObj = ui.item;
			VM.addAttendee(selectedObj.fullname,"","",selectedObj.ParentCustomerIdName,"5",selectedObj.id)
			$(this).val("");
			return false;
		}
	});

And here is the javascript code (part of the view model) that is used for the source of the jQuery AutoComplete (CRM 2011 oData REST endpoint). Just a simple ajax call to CRM 2011.

	self.contactsSearchSourceREST = function (request, response) {
	    var serverUrl = "crm.server.com"
	    var ODATA_ENDPOINT = "/ORGNAME/XRMServices/2011/OrganizationData.svc";
	    var ODATA_EntityCollection = "/ContactSet";
	    var strSelect = "$select=FullName,ParentCustomerId,ContactId"
	    var strFilter = "$filter=substringof('" + request.term + "',FullName) and StateCode/Value eq 0"
	    var URL = serverUrl + ODATA_ENDPOINT + ODATA_EntityCollection + "?" + strFilter + "&" + strSelect
	    //alert(URL);
	    $.ajax({
	        type: "GET",
	        contentType: "application/json",
	        datatype: "json",
	        async: false,
	        url: URL,
	        beforeSend: function (XMLHttpRequest) {
	            XMLHttpRequest.setRequestHeader("Accept", "application/json");
	            XMLHttpRequest.setRequestHeader("Content-Type", "application/json")
	        },
	        success: function (data, textStatus, XmlHttpRequest) {
	            response($.map(data.d.results, function (item) {
	                return {
	                    label: item.FullName + ' (' + item.ParentCustomerId["Name"] + ')',
	                    value: item.FullName + ' (' + item.ParentCustomerId["Name"] + ')',
	                    fullname: item.FullName,
	                    ParentCustomerIdName: item.ParentCustomerId["Name"],
	                    id: item.ContactId
	                }
	            }));
	        },
	        error: function (XMLHttpRequest, textStatus, errorThrown) {
	            alert("failure " + errorThrown);
	            return false;
	        }
	    });
	}

Next is the knockout code (part of the view model) that is used to push the new contact guid into an observable array upon selecting the AutoCompleted contact

    self.Attendees = ko.observableArray();
    self.contact = function(fullname, firstname, lastname,ParentCustomerIdName, activitypartytype, guid) {
	this.fullname = fullname;
	this.firstname = firstname;
	this.lastname = lastname;
	this.ParentCustomerIdName = ParentCustomerIdName;
	this.role = activitypartytype;
	this.guid = guid;
    };
    self.addAttendee = function(fullname, firstname, lastname,ParentCustomerIdName, activitypartytype, guid) {
        self.Attendees.push(new self.contact(fullname, firstname, lastname,ParentCustomerIdName, activitypartytype, guid));
        return
    };

And finally the knockout code to concatenate the GUIDs and put them in the hidden (original) field (Attendees).

    ko.utils.arrayForEach(self.Attendees(), function(contact) {
        total = total + contact.guid + ';'
    	});
    total = total.substring(0, total.length - 1);
    $('input[type=text][title="Attendees"]').val(total)
    return total
	});

Pretty complex, lots of different techniques bing used (knockout, jQuery, ajax). Hope it makes sense.

SharePoint 2010 modal dialog (showModalDialog) without an existing page

I was retrieving Activity data from a Microsoft CRM 2011 REST query. I wanted to have a popup with more information. I decided to use the built in showModalDialog. The problem was that all the examples I found showed how to popup an existing page. I wanted the modal to contain data that didn’t exist anywhere. The solution was to use the following code, specifically create a divElement and set the innerHTML to html that contained the data I want to show. I threw in some typical SharePoint css classes to keep the same look and feel as the rest of the site.

    displayActivityModalDialog = function () {
        var divElem = document.createElement('div');
        var htmlOutput = '<div class="ms-bodyareacell"><table>'
        htmlOutput += '<tr><td width="190" class="ms-formlabel">Type:</td><td class="ms-formbody">' + this.ActivityType + '</td></tr>';
        htmlOutput += '<tr><td width="190" class="ms-formlabel">Subject:</td><td class="ms-formbody">' + this.Subject + '</td></tr>';
        htmlOutput += '<tr><td width="190" class="ms-formlabel">Date:</td><td class="ms-formbody">' + this.ScheduledStart + '</td></tr>';
        htmlOutput += '<tr><td width="190" class="ms-formlabel">Regarding:</td><td class="ms-formbody">' + this.Regarding + '</td></tr>';
        htmlOutput += '<tr><td width="190" class="ms-formlabel">Organizer:</td><td class="ms-formbody">' + this.Organizer + '</td></tr>';
        htmlOutput += '<tr><td width="190" class="ms-formlabel">Description:</td><td class="ms-formbody">' + this.Description + '</td></tr>';
        htmlOutput += '<tr><td width="190" class="ms-formlabel">Other Attendees:</td><td class="ms-formbody">' + this.RequiredAttendee + '</td></tr>';
        htmlOutput += '</table></div>'

        divElem.innerHTML = htmlOutput;
        var options = SP.UI.$create_DialogOptions();
        options.html = divElem;
        options.title = "Activity details";
        options.showClose = true;
        options.showMaximized = false;
        SP.UI.ModalDialog.showModalDialog(options);
    }

Now I can put anything in a showModalDialog!

SharePoint 2010, CSOM and External Lists – must use LoadQuery

I was working on some JavaScript code to pull data from an External List that was pointing to a Microsoft CRM 2011 database. The code was being used to populate a jQuery autocomplete like this post. In that post you can see on line 10, I use the “load” method. This worked, but if I started a new query before the previous one ended, a javascript error was thrown. I am sorry I don’t recall the error – I am blogging this a month or so after I figured it out.

It took me a while, but I found one reference to figure out the issue. It is on page 143 of Scott Hillier’s book “Professional Business Connectivity Services in SharePoint 2010” (I found it via the google query “loadquery external list”)

The work around was that you have to use LoadQuery with External lists, Load is not supported. Not sure where this is documented, but it took me quite a while to figure that one out.

Hope that helps some one?

Looping through a SharePoint List Column with jQuery and replacing a GUID with a name from CRM 2011

This is a very specialized piece of code, but it came together nicely, so I thought I would share it.

I have a SharePoint List that has a bunch of Microsoft CRM 2011 Contact GUIDs in it. Some columns have one GUID and others have multiple GUIDs separated by semicolons. My goal was: when a user visits the default view for this list, the GUIDs are looked up agains CRM and displayed as the contact’s full name. I wrote the following code to do just that. The 4th, 5th and 10th columns has guide in them. I use this method to loop through the column.

$('table.ms-listviewtable td:nth-child(4),table.ms-listviewtable td:nth-child(5),table.ms-listviewtable td:nth-child(10)').each(function () {
        var guids = $(this).text().split(";");
        var names = ''
        $.each(guids, function () {
            var guid = this;
            var serverUrl = "http://crmserver.company.com"
            var ODATA_ENDPOINT = "/Organization/XRMServices/2011/OrganizationData.svc";
            var ODATA_EntityCollection = "/ContactSet(guid'" + guid + "')";
            var URL = serverUrl + ODATA_ENDPOINT + ODATA_EntityCollection
            $.ajax({
                type: "GET",
                contentType: "application/json",
                datatype: "json",
                async: false,
                url: URL,
                beforeSend: function (XMLHttpRequest) {
                    XMLHttpRequest.setRequestHeader("Accept", "application/json");
                    XMLHttpRequest.setRequestHeader("Content-Type", "application/json")
                },
                success: function (data, textStatus, XmlHttpRequest) {
                    names = names + data.d.FullName + ","
                }
            });
        });
        $(this).text(names.substring(0, names.length - 1));
    });

Line 1: is the jQuery selector for all the columns that have GUIDs. And we loop through each of them.
Line 2: splits the contents of the column.
Line 4: loops through the all the GUIDs in each row/column
Lines 5-9: are setting up the CRM 2011 REST url for the ajax call
Lines 10-24: query the Microsoft CRM 2011 rest endpoint with the contact’s GUID and assign the full name to a variable
Line 25: displays the full name instead of the GUID.

Some slim code that works pretty well.

Using PowerShell to add a Contact to a CRM 2011 MarketingList (SOAP)

We had a user delete a Marketing List. I needed to recreate it. I went to a database backup and found the GUID of the deleted list.
Then I used the following SQL query to find the GUIDs of all the members of that list:

SELECT FullName
    ,ParentCustomerIdName
    ,[EntityId]
    ,[ListId]
    ,[ListMemberId]
  FROM [CRMDataBaseName].[dbo].[ListMember],[CRMDataBaseName].[dbo].Contact
  where ListId = '787b77ca-c47d-431b-863e-12a98969b097' AND 
  [EntityId] = ContactId
  order by LastName,FirstName

I saved the EntityId column to a text file, and then I used the following PowerShell code to loop through the GUIDs and add them to a new MarketingList


$ListMembers = Get-Content C:\IT\Temp\ListMemberGUIDs.txt
foreach ($EntityId in $ListMembers){
$xml = ""
$xml += "<s:Envelope xmlns:s='http://schemas.xmlsoap.org/soap/envelope/'>";
$xml += "  <s:Body>";
$xml += "    <Execute xmlns='http://schemas.microsoft.com/xrm/2011/Contracts/Services' xmlns:i='http://www.w3.org/2001/XMLSchema-instance'>";
$xml += "      <request i:type='b:AddMemberListRequest' xmlns:a='http://schemas.microsoft.com/xrm/2011/Contracts' xmlns:b='http://schemas.microsoft.com/crm/2011/Contracts'>";
$xml += "        <a:Parameters xmlns:c='http://schemas.datacontract.org/2004/07/System.Collections.Generic'>";
$xml += "          <a:KeyValuePairOfstringanyType>";
$xml += "            <c:key>ListId</c:key>";
$xml += "            <c:value i:type='d:guid' xmlns:d='http://schemas.microsoft.com/2003/10/Serialization/'>5deb4efb-4ed7-47f3-8e8e-bb487e0db423</c:value>";
$xml += "          </a:KeyValuePairOfstringanyType>";
$xml += "          <a:KeyValuePairOfstringanyType>";
$xml += "            <c:key>EntityId</c:key>";
$xml += "            <c:value i:type='d:guid' xmlns:d='http://schemas.microsoft.com/2003/10/Serialization/'>$($EntityId)</c:value>";
$xml += "          </a:KeyValuePairOfstringanyType>";
$xml += "        </a:Parameters>";
$xml += "        <a:RequestId i:nil='true' />";
$xml += "        <a:RequestName>AddMemberList</a:RequestName>";
$xml += "      </request>";
$xml += "    </Execute>";
$xml += "  </s:Body>";
$xml += "</s:Envelope>";
 
$url="http://crm.sardverb.com/SardVerbinnen/XRMServices/2011/Organization.svc/web"
 
$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.Open('POST', $url, $false)
$http_request.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute")
$http_request.setRequestHeader("Content-Type", "text/xml; charset=utf-8")
$http_request.setRequestHeader("Content-Length", $xml.length)
$http_request.send($xml)
}