Navigation

Archive | MicrosoftCRM

Update CRM 2011 from an Excel Sheet using PowerShell and the oData/REST endpoint

By on March 16, 2012 in MicrosoftCRM, PowerShell

Let’s say you exported a group of Contacts from CRM 2011 using the “Export to Excel” button and you selected the “Make this data available for re-importing by including required column headings” button. The important thing that this does is it adds the GUID for the contact to the first column.
Now, let’s say you have people edit this spreadsheet and make updates to contact data. Our workflow was that if you edited a contact, you highlighted the cell you modified, and marked the contact as modified (in a new column). All I would need to find all the columns/fields that have been modified (they are highlighted), and I should be able to loop through them and update the record via the GUID. I wrote the following VB function to generate the update string that will be exported with the GUID of the contact. This function looks for a cell that has a background color, then grabs the column heading for that field, and generates an update string. An example of an update string will look like this:

“{`”Telephone1`”:`”555-555-5555`”,`”Address1_Telephone1`”:`”555-555-5555`”}”

Function GenerateUpdateString(rRange As Range)
Dim rCell As Range
Dim vResult As String
vResult = "{"
For Each rCell In rRange
If rCell.Interior.ColorIndex > 0 Then
         ColumnHead = Cells(1, rCell.Column).Value
         vResult = vResult & "`""" & ColumnHead & "`"":`""" & Trim(rCell.Value) & "`"","
End If
Next rCell
If Right(vResult, 1) = "," Then
vResult = Mid(vResult, 1, Len(vResult) - 1)
End If
vResult = vResult & "}"
GenerateUpdateString = vResult
End Function

Now I just use the following powershell code to loop through the columns in the exported csv and update each record.

$CSVFile=import-csv -path c:\Contacts.csv
foreach ($line in $CSVFile){
$url="http://crm.server.com/Instance/xrmservices/2011/OrganizationData.svc/ContactSet(guid'$($line.GUID)')"
$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");
$webclient.Headers.Add("X-HTTP-Method", "MERGE")
# EXample  $line.UpdateString="{`"Telephone1`":`"555-555-5555`",`"Address1_Telephone1`":`"555-555-5555`"}"
$stringToUpload=$line.UpdateString
$resultString=$webclient.UploadString($url,$stringToUpload)

Waiting for some real data to try with, but so far, my small test data works. Will let you know how it works. What do you think?

PowerShell code to update a CRM 2011 field (using REST/oData)

By on February 21, 2012 in MicrosoftCRM, PowerShell

In this earlier post I showed how to loop through all the contacts in CRM 2011. Next thing I wanted to do was to update a field on each Account. So I needed to figure out how to update data, not just read it. Here is the code to do that:

	$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
	$url="http://your.crm.com/Instance/xrmservices/2011/OrganizationData.svc/AccountSet(guid'GUIDofAccount')"
	$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");
	$webclient.Headers.Add("X-HTTP-Method", "MERGE")
	$stringToUpload="{`"AccountNumber`":`"123456`"}"
	$resultString=$webclient.UploadString($url,$stringToUpload)

PowerShell, JSON, oData and CRM 2011 (or SharePoint 2010)

By on February 16, 2012 in MicrosoftCRM, PowerShell, SharePoint

I am working on how to consume data from/to SharePoint 2010 and from/to CRM 2011. I decided to try and see if I can get the data to display in PowerShell, figuring if I can get it there, I should be able to get it anywhere?  Here is the code to loop through all the Contacts in a CRM 2011 deployment.

Took me a while to figure this out. Should work with any oData source?

$url="http://your.crm.server/Instance/XRMServices/2011/OrganizationData.svc/ContactSet"
$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
while ($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");
	$dataString=$webclient.DownloadString($url)
	$json=new-object System.Web.Script.Serialization.JavaScriptSerializer
	$data=$json.DeserializeObject($dataString)
	foreach ($result in $data.d.results){
		write-host "$($result.FullName) , $($result.EMailAddress1)"
	}
	Write-Host "Press any key to continue ..."
	$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
	if ($data.d.__next){
		$url=$data.d.__next.ToString()
	}
	else {
		$url=$null
	}
}

To loop through the items of a SharePoint 2010 list, you would change $url to:

$url=”http://sharepoint2010.server.com/_vti_bin/listdata.svc/Announcements”

Not sure if this would be valuable to anyone, but here it is!

Dotted (period) NetBIOS Domain Name issues

By on January 18, 2012 in MicrosoftCRM, SCCM, Windows

I inherited a Dotted Netbios Domain Name. Technically it is allowed, but it is not recommended. With every new version of software, I cringe in fear that it will come back to bite us again. For example:

  1. Moving from Exchange 2003 to 2010 was a problem for a while, but it seemed to been resolved and we migrated from 2003 to 2010 with out issue.
  2. When I tried to upgrade SCCM 2007 to SP 2, it failed as I describe in this thread. I had to install fresh from a slipstreamed  installer.
  3. Most recently, when I try to install CRM 2011 in our dotted domain, it failed. In my reading yesterday, I realized Rollup 4 has the following: “When the NetBIOS name of the domain contains a period (.), the installation of Microsoft Dynamics CRM 2011 fails.”
    • I still can’t add a new organization, but I was able to restore an organization from a dev server in a non-dotted netbios domain.
  4. I will add more as I find them.
The problem seems to be that with each new installer, the developers assume that if there is a period in the domain name, then the user has supplied a FQDN by accident.