Navigation

Tag Archives | MSCRM2011

PowerShell script to create a contact in Microsoft CRM 2011 via REST/ODATA

By on May 25, 2012 in MicrosoftCRM, PowerShell

Below is a PowerShell function to create a contact in CRM 2011. Hope it is helpful to some one.

FUNCTION JBM-CRM-CreateContact {
PARAM(
    [string][ValidateSet("crmserver.company.com", "dev-crmserver.company.com")]$ServerName="crmserver.company.com",
    [string][string][ValidateSet("CRMOrganizationName")]$OrganizationName="CRMOrganizationName",
    [string][parameter(Mandatory=$true)]$FirstName,
    [string][parameter(Mandatory=$true)]$LastName,
    [string]$MiddleName,[string]$Suffix,[string]$Email,[string]$JobTitle,
    [string]$Telephone,[string]$Description,[string]$ParentCustomerId,[string]$Address_Line1,[string]$Address_Line2,
    [string]$Address1_Country,[string]$Address1_PostalCode,[switch]$MyDebug
    )

$ContactInfo = @{
FirstName=$FirstName
LastName=$LastName
MiddleName=$MiddleName
Suffix=$Suffix
EMailAddress1=$Email
NickName=$NickName
JobTitle=$JobTitle
Telephone1=$Telephone
Address1_Line1=$Address_Line1
Address1_Line2=$Address_Line2
Address1_City=$Address1_City
Address1_PostalCode=$Address1_PostalCode
Address1_Country=$Address1_Country
Description=$Description
}
if (!($ParentCustomerId -eq "")){
$ContactInfo.Add("ParentCustomerId" , @{Id=$ParentCustomerId;LogicalName= "account"})
}

if ($MyDebug){
$ParentCustomerId
$ContactInfo
}

$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$json=new-object System.Web.Script.Serialization.JavaScriptSerializer
$ContactInfoData=$json.Serialize($ContactInfo)

$url="http://$ServerName/$($OrganizationName)/xrmservices/2011/OrganizationData.svc/ContactSet"
 
$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Accept", "application/json")
$http_request.setRequestHeader("Content-Type", "application/json; charset=utf-8")
$results=$http_request.send($ContactInfoData)
if ($MyDebug){
$http_request.statusText
$http_request
}
$ContactId=$($json.DeserializeObject($http_request.responseText)).d.ContactId
return $ContactId
}

PowerShell script to add an activity via OData/REST

By on May 24, 2012 in MicrosoftCRM, PowerShell

I wanted to create some crm activities based on data in a spreadsheet. Looping thorough the csv file would be easy, the challenge came when I wanted to create new activities in Microsoft CRM 2011, specifically appointments. Below is a PowerShell function that I pieced together to create a new activity if you know GUIDs of the contacts for the regarding, required fields. This is a more fleshed out function compared to this

FUNCTION JBM-CRM-CreateActivityODATA {
PARAM([string][parameter(Mandatory=$true)][ValidateSet("Email", "PhoneCall", "Appointment")]$EntityType,
    [string][ValidateSet("crmserver.company.com", "dev-crmserver.company.com")]$ServerName="crmserver.company.com",
    [string][ValidateSet("CRMOrganizationName")]$OrganizationName="CRMOrganizationName",
    [string][parameter(Mandatory=$true)][ValidateScript({ $_ -match("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$")})]$OwnerGUID,
    [string][ValidateScript({ $_ -match("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$")})]$RegardingGUID,
    [ValidateScript({-not @( $_ | where {$_ -notmatch("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$") }).Count})]$ReqiredGUIDs,
    [string]$Subject,[string]$Description,[datetime]$ScheduledStart,[datetime]$ScheduledEnd,[string]$Location
    )

Function CreateActivityParty{
    PARAM($ActivityId,$ParticipationTypeMask,$EntityType,$GUIDs)

    foreach ($Id in $GUIDs){
    $activityParty = @{}
    write-host $Id
    $activityParty.Add("PartyId", @{Id= $Id;LogicalName= "contact"})
    $activityParty.Add("ActivityId", @{Id= $ActivityId;LogicalName= "$($EntityType.ToLower())"})
    $activityParty.Add("ParticipationTypeMask" , @{ Value=$ParticipationTypeMask })
    $json=new-object System.Web.Script.Serialization.JavaScriptSerializer
    $activityPartyData=$json.Serialize($activityParty)
    $activityPartyData

    $url="http://$ServerName/$($OrganizationName)/xrmservices/2011/OrganizationData.svc/ActivityPartySet"
 
    $http_request = New-Object -ComObject Msxml2.XMLHTTP
    $http_request.open('POST', $url, $false)
    $http_request.setRequestHeader("Accept", "application/json")
    $http_request.setRequestHeader("Content-Type", "application/json; charset=utf-8")
    $http_request.send($activityPartyData)
    $http_request.statusText
    $results=$http_request
    }
}

$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$activityObject = @{
ActivityTypeCode=$EntityType
Subject=$Subject
Description=$Description
ScheduledStart=$($ScheduledStart.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ"))
ScheduledEnd=$($ScheduledEnd.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ"))
Location=$Location
RegardingObjectId=@{
Id= $RegardingGUID
LogicalName= "contact"
}
}

$json = new-object System.Web.Script.Serialization.JavaScriptSerializer
$activityData=$json.Serialize($activityObject)


$url="http://$ServerName/$($OrganizationName)/xrmservices/2011/OrganizationData.svc/$($EntityType)Set"
 
$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Accept", "application/json")
$http_request.setRequestHeader("Content-Type", "application/json; charset=utf-8")
$http_request.send($activityData)
$results=$http_request
$ActivityId=$($json.DeserializeObject($results.responseText)).d.ActivityId

# Set Required Attendees
CreateActivityParty -ActivityId $ActivityId -ParticipationTypeMask 5 -EntityType $EntityType -GUID $ReqiredGUIDs
# Set Orgaizer
CreateActivityParty -ActivityId $ActivityId -ParticipationTypeMask 7 -EntityType $EntityType -GUID $OwnerGUID
}

PowerShell function to search CRM 2011 for and Entity (via REST/oData)

By on May 9, 2012 in MicrosoftCRM, PowerShell

I wrote about how to retrieve records from CRM 2011 via oData. I wanted to wrap that up in a function that I can use to do a quick search:


Function JBMURPHY-CRM-SearchEntity{
PARAM([parameter(Mandatory=$true)][ValidateSet("Contact", "Account","SystemUser")]$EntityType,[parameter(Mandatory=$true)]$SearchString,[parameter(Mandatory=$true)]$SearchField,$FieldsToReturn)
$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$baseurl="http://crmserver.company.com/Organization/xrmservices/2011/OrganizationData.svc"
$urlparams="/$($EntityType)Set?`$filter=substringof('$($SearchString)',$($SearchField))"
$url=$baseurl+$urlparams
$Count=0
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){
            $Count=$Count+1
            write-host -NoNewline "$Count. "
            foreach ($field in $FieldsToReturn){
            write-host -NoNewline "$field : "
            Write-Host -NoNewline $result."$field"
            Write-Host -NoNewline ", "
            #write-host "$($result.FullName) , $($result.EMailAddress1)"
            }
            Write-Host
    }
    if ($data.d.__next){
        $url=$data.d.__next.ToString()
    }
    else {
        $url=$null
    }
}
}

And to use this function:

JBMURPHY-CRM-SearchEntity -EntityType Account -SearchField Name -SearchString “Sard” -FieldsToReturn Name,AccountId

Using PowerShell to query CRM 2011 SOAP endpoint – Answer!

By on May 8, 2012 in MicrosoftCRM, PowerShell

Big thanks to @JLattimer. He helped me figure our the error in my SOAP envelope.

In my last post, I was trying to retrieve FullName from Crm 2011’s web services via PowerShell. Below is the code to do that.

$xml = "<?xml version='1.0' encoding='utf-8'?>"
$xml += "<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'>"
$xml += "<soap:Body>"
$xml += "<Retrieve xmlns='http://schemas.microsoft.com/xrm/2011/Contracts/Services'>"
$xml += "<entityName>contact</entityName>"
$xml += "<id>12345678-1234-1234-1234-123456789012</id>"
$xml += "<columnSet xmlns:q1='http://schemas.microsoft.com/xrm/2011/Contracts' xsi:type='q1:ColumnSet'>"
$xml += "<q1:Columns xmlns:c='http://schemas.microsoft.com/2003/10/Serialization/Arrays'>"
$xml += "<c:string>fullname</c:string>"
$xml += "<c:string>telephone1</c:string>"
$xml += "</q1:Columns>"
$xml += "</columnSet>"
$xml += "</Retrieve></soap:Body></soap:Envelope>"

$url="http://crmserver.company.com/Organization/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/Retrieve");
$http_request.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
$http_request.setRequestHeader("Content-Length", $xml.length);
$http_request.send($xml);
$http_request.responseText
[ xml ]$results=$http_request.responseXML.xml
$ns = New-Object Xml.XmlNamespaceManager $results.NameTable
$ns.AddNamespace( "b", "http://schemas.microsoft.com/xrm/2011/Contracts" )
$ns.AddNamespace( "c", "http://schemas.datacontract.org/2004/07/System.Collections.Generic" )
$FullName=$results.selectSingleNode("//b:KeyValuePairOfstringanyType[c:key='fullname']/c:value/text()",$ns).Value
$Telephone=$results.selectSingleNode("//b:KeyValuePairOfstringanyType[c:key='telephone1']/c:value/text()",$ns).Value

Using PowerShell to query CRM 2011 SOAP endpoint – help!

By on May 4, 2012 in MicrosoftCRM, PowerShell

I don’t know what I am doing wrong. All I am trying to do is to query  CRM 2011 via PowerShell and SOAP. The following PowerShell script should return the Contact’s full name, but I get nothing. Any ideas??

Here is the PowerShell:

$xml = "<?xml version='1.0' encoding='utf-8'?>"
$xml += "<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'>"
$xml += "<soap:Body>"
$xml += "<Retrieve xmlns='http://schemas.microsoft.com/xrm/2011/Contracts/Services'>"
$xml += "<entityName>contact</entityName>"
$xml += "<id>12345678-1234-1234-1234-123456789012</id>"
$xml += "<columnSet xmlns:q1='http://schemas.microsoft.com/xrm/2011/Contracts' xsi:type='q1:ColumnSet'>"
$xml += "<q1:Attributes>"
$xml += "<q1:Attribute>fullname</q1:Attribute>"
$xml += "<q1:Attribute>telephone1</q1:Attribute>"
$xml += "</q1:Attributes>"
$xml += "</columnSet>"
$xml += "</Retrieve></soap:Body></soap:Envelope>"

$url="http://crmserver.company.com/Organization/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/Retrieve");
$http_request.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
$http_request.setRequestHeader("Content-Length", $xml.length);
$http_request.send($xml);
$http_request.responseText

And here is the response:

$http_request.responseText

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Body><RetrieveResponse xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">
<RetrieveResult xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<a:Attributes xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
<a:KeyValuePairOfstringanyType>
<b:key>accountid</b:key>
<b:value i:type="c:guid" xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/">12345678-1234-1234-1234-123456789012</b:value>
</a:KeyValuePairOfstringanyType>
</a:Attributes>
<a:EntityState i:nil="true"/><a:FormattedValues xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic"/>
<a:Id>12345678-1234-1234-1234-123456789012</a:Id><a:LogicalName>account</a:LogicalName>
<a:RelatedEntities xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic"/>
</RetrieveResult>
</RetrieveResponse>

I just don’t know what I am doing wrong. Why won’t the code return “fullname”, it is in the columnSet.

Update-2012-05-08:I have posted my question here, no answer yet

Update2-2012-05-05: I have posted and received and answer here on stack overflow. Big thanks to @JLattimer

SharePoint 2010, Client OM, jQuery Autocomplete and BCS/External lists

By on April 24, 2012 in jQuery, MicrosoftCRM, SharePoint

In this previous post, I used jQuery/SOAP/SPServices to access a SharePoint BCS list (an external list). I wanted to do the same thing using the Client Object Model (Some call it Clien OM? Or maybe CSOM?). Below is the code to access contact data in a BCS connection to Microsoft CRM 2011, and use it for autocomplete values. Things to note:

  1. You need to have a method tag in your CAML statement. This tells the list which “Operation” (as it is listed in SharePoint Designer) to use
  2. You need to use the “include method” with the executeQueryAsync method
  3. You need to include the ViewFields tags in the CAML statement, and I think they should match the include statement above
$('#ContactsSearchTextbox').autocomplete({
    source: function( request, response ) {
	    var CAMLQuery= "<View><Method Name='ContactReadList' /><Query><Where><Contains><FieldRef Name='FullName' /><Value Type='Text'>"+request.term+"</Value></Contains></Where></Query><ViewFields><FieldRef Name='FullName' /><FieldRef Name='ContactId' /><FieldRef Name='ParentCustomerIdName' /></ViewFields></View>";
		var context = new SP.ClientContext.get_current();
		var web = context.get_web();
		var list = web.get_lists().getByTitle('Contacts');
		var query = new SP.CamlQuery;
		query.set_viewXml(CAMLQuery);
		allItems = list.getItems(query);
		context.load(allItems, 'Include(FullName,ContactId,ParentCustomerIdName)');
		context.executeQueryAsync(function (){
			var arrayOfResults = new Array();
			var listItemEnumerator = allItems.getEnumerator(); 
			while(listItemEnumerator.moveNext()) {
				var oListItem = listItemEnumerator.get_current();
				arrayOfResults.push(oListItem);
			}
			response($.map(arrayOfResults ,function( item ) {
				return {
				label: item.get_item('FullName') + ' (' + item.get_item('ParentCustomerIdName') + ')',
				value: item.get_item('ContactId'),
				id: item.get_item('FullName')
				}
			}));
		},function(sender, args){
		   alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
		});        
	},
	minLength: 5,
});

Add an appointment/email/phone activity to CRM 2011 via PowerShell (REST/oDATA)

By on April 13, 2012 in MicrosoftCRM, PowerShell

I am looking to move some older list data into CRM 2011, so I wanted a way to create some “activities” via powershell. Below is the code to do that. In the first part of the code, I create a PowerShell object and then I convert it to a JSON object via PowerShell 3’s new ConvertTo-JSON cmdlet.

$JsonObject = New-Object psobject -Property @{
ActivityTypeCode = "appointment"
Subject = "jbmurphy TEST"
Description = "This is a descritpion"
ScheduledStart = "2012-03-20T18:00:00Z"
ScheduledEnd = "2012-03-20T18:00:00Z"
Location = "Office"
}

# I am using PowerShell 3's ConvertTo-Json
$jsonEntity=$JsonObject | ConvertTo-Json
#$url="http://crmserver.company.com/OrgName/xrmservices/2011/OrganizationData.svc/EmailSet"
#$url="http://crmserver.company.com/OrgName/xrmservices/2011/OrganizationData.svc/PhoneCallSet"
$url="http://crmserver.company.com/OrgName/xrmservices/2011/OrganizationData.svc/AppointmentSet"

$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Accept", "application/json")
$http_request.setRequestHeader("Content-Type", "application/json; charset=utf-8")
$http_request.send($jsonEntity)
$http_request.statusText

PowerShell code to query a table, and put the XML results into a SharePoint Document Library

By on March 16, 2012 in MicrosoftCRM, MSSQL, PowerShell, SharePoint

If you look at this prior post, I showed how you can use an xml file in a document library as a source for an input box’s jQuery autocomplete. I wanted to automate the creation of these XML files and upload them to a SharePoint Document Library. For example, I wanted to query Microsoft CRM for all our contacts, and have them appear as an autocomplete for a search input box. I wrote the following PowerShell script to automate this process:

Function JBM-SQL-XMLQueryResultsToSharePointDocLibrary{
PARAM([parameter(Mandatory = $true)]$SQLServerName,[parameter(Mandatory = $true)]$DatabaseName,[parameter(Mandatory = $true)]$Query,[parameter(Mandatory = $true)]$DocLibraryURL,[parameter(Mandatory = $true)]$FullPathFileName)
$FileName=$FullPathFileName.split("\")[$FullPathFileName.split("\").Count -1]
$xml=Invoke-Sqlcmd -ServerInstance $SQLServerName -database $DatabaseName -query "$Query" | ConvertTo-Xml -NoTypeInformation -As String
[xml]$output=$xml -replace '<Property Name="([^"]+)">([^<]+)</Property>', '<$1>$2</$1>' -replace '<Property Name="([^"]+)"\s*/>','<$1/>'
$output.save("$FullPathFileName")

$webclient = New-Object System.Net.WebClient;
$webclient.UseDefaultCredentials = $true
$webclient.UploadFile("$DocLibraryURL/$FileName","PUT","$FullPathFileName")
}

I had to use this thread’s method to change the XML produced by ConvertTo-Xml. The default output puts the field name in to a Property Name tag.

Example: Default = <Property Name=”Version”>0</Property> . I wanted it to be = <Version>0</Version>

In theory, this can be used for any SQL query.

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)