MS CRM ActivityParty REST Queries

I recently had to create a rollup display showing all activities under a given entity. Pulling the sender or receiver out of an email activity requires a few steps.

Adding confusion the email entity has a text field ToRecipients which is an accumulated convenience text field. I would avoid using it as it is not complete if an email is sent to an email address that is not associated with a CRM contact, user etc. In this case the ToRecipients field will be null and the activity party AddressUsed field must be evaluated.

To fully reach the sender and receivers of email activities from an ActivityPointer query, the REST query needs to expand to include the activitypointer_activity_parties link table. You will need the ParticipationTypeMask to find out the role of each related activityparty, and both the AddressUsed and PartyId attributes.

Below is a sample query for all activities (ActivityPointer) related to an entity.

 function retrieveActivities() {

  var caseId = AllCases[ProcessCaseIndex].Id;

  var parameters = "$select=ActivityId, RegardingObjectId, Subject, ActivityTypeCode, CreatedOn, ScheduledStart, Description, StateCode, OwnerId";
  var expandParameters = ",activitypointer_activity_parties/ParticipationTypeMask,activitypointer_activity_parties/PartyId,activitypointer_activity_parties/AddressUsed "; 
  var expand = "&$expand=activitypointer_activity_parties"; 
  var filter = "&$filter=RegardingObjectId/Id eq (guid'" + caseId + "')";
  var orderby = "&$orderby=CreatedOn";
  var options = parameters + expandParameters + expand + orderby + filter;
  SDK.REST.retrieveMultipleRecords("ActivityPointer", options, retrieveActivitiesCallBack, function (error) { alert(error.message); }, activitiesRetrieveComplete);
}

The ActivityPointer.ActivityTypeCode specifies the activity types:

• email, appointment, phonecall, task, letter, fax, serviceappointment, campaignactivity, bulkoperation
• caseresolution, opportunityclose, quoteclose, campaignresponse

The ParticipationTypeMask specifies the activity party role.
1 From, 2 To, 3 CC, 4 BCC, Owner 9, Regarding 8, and a number of others.

In the following code snipet we are just looking for the Sender or From activity party for email activities. The PartyId.Name is used if available otherwise it looks at the AddressUsed field, but this field is used in all the other email activity parties (From, To, CC, BCC).

     function retrieveActivitiesCallBack(retrievedActivities) {                            
      
        for (var i = 0; i < retrievedActivities.length; i++) {

            var activity = retrievedActivities[i]; 
            
            var type = activity.ActivityTypeCode;
            
            if (type == "email") {                    
                        
            var activityParties = activity.activitypointer_activity_parties.results;
                                    
            for (var j = 0; j < activityParties.length; j++) {

                var activityMask = activityParties[j].ParticipationTypeMask.Value;

                // Sender Only
                if (activityMask == 1) {
                   if (activityParties[j].PartyId.Name == null) {
                        senderName = activityParties[j].AddressUsed
                   }
                   else {
                        senderName = activityParties[j].PartyId.Name;
                   }
                 break;
                 }
           }
    }                                    
        

...

MS CRM Email Template with dynamic id in link

You are trying to create a custom email to a group of contacts and want to create a URL with a contactid in the query string to customize what they see when they click on that link.

It turns out that you need to understand two things to make this work.

In the Template Editor when you press Insert/Update there is no option for the contactid.

#1 Generating a contact id

To create a contactid merge field you need to type the following which is case sensitive.

{!contact:contactid;}

Now Save the Template and this text will turn into a working merge field that returns the contact id of the contact the email is being sent to.

{!Contact:Contact;}

#2 Be careful about links in the Email Template Editor

The following two lines will not be processed the same way when the emails are sent.

The following line will be merged properly and the contact's email browser will typically display a link.

https://www.myportal.com/OrderService.aspx?id={!Contact:Contact;}&type=0


This line may surprise you since the email will look fine but the link won't work. 
https://www.myportal.com/OrderService.aspx?id={!Contact:Contact;}&type=0

What you get is the following:

Notice that the text of the link is merged, but the href value is not.

What if you want a nicely formatted link?

Please click here to find out more!

At this point I "believe" your only course of action is to manually modify the XML in the email template body in SQL. I haven't tried that yet, but will post additional information if I do.

MS Dynamics CRM Web Resources Basic Starter Page

When creating a Web Resource Page in MS CRM there are some basic parts that I've tend to use in all of my pages. These are all contained in the script block above the body.

User Information:

     UserId = Xrm.Page.context.getUserId();
     UserName = Xrm.Page.context.getUserName();

Query String parameters to drive the page

     QueryStringParameters = Xrm.Page.context.getQueryStringParameters();
     
     QueryStringParm = "None";
   
     if (QueryStringParameters["feature"] != null) {

         QueryStringParm = QueryStringParameters["feature"].toString();             
     }

Entity Id  - when you are running in a Form iframe and the entity has been saved.

     EntityId = window.parent.Xrm.Page.data.entity.getId();
 
     Id = QueryStringParameters["id"].toString();

Trigger for Query

  document.onreadystatechange = function () {

         if (document.readyState == "complete") {

           // Typically call a query to populate the page

         }
     }

Function to Open a CRM Form in a new window

  function OpenCRMForm(entityName, entityId) {

         var windowOptions = {
             openInNewWindow: true
         };

         Xrm.Utility.openEntityForm(entityName, entityId, null, windowOptions);

     }

On Premises MS CRM 2011 to CRM 2015 Upgrade

Other than my CRM Online customers, the rest of my customer base which are using on premises systems still run CRM 2011 and are looking to upgrade to CRM 2015. While my customer base is a small subset of the microcosm, I am betting they are not alone. This is a high level overview that I will modify and embellish over time.

This upgrade will need to be done in stages and will require an intermediate upgrade to CRM 2013. 

Step 1: Upgrade your SQL server system if necessary:

CRM 2015 discontinues support for SQL Server 2008 / 2008R2

However CRM 2015 continues to support Windows 2008 / 2008R2

Step 2: Any CRM 4.0 SDK solutions will no longer be supported, so make sure that all of your solutions are using the CRM 2011 SDK as a starting point.  This includes all of your plugins, custom workflows, web applications and scheduled applications that use the CRM API.

Step 3: it is recommended that CRM 2011 should be updated to Rollup 14 before starting.

Step 4: Run the Custom Code Validation Tool to test your JavaScript Form customizations. http://blogs.msdn.com/b/crm/archive/2012/06/21/microsoft-dynamics-crm-2011-custom-code-validation-tool-released.aspx

Step 5: Run the Legacy Feature Check Tool to see if you are still using any CRM 4.0 API calls.

Warning:  Inventory any SQL Views or direct SQL queries or updates that you may have created against the actual CRM database tables. It is recommended to always use the CRM Views and not access the CRM database directly, but in the event that you did anything unsupported, the Base and Extension tables will be merged at the end of this process.

Running the CRM 2013 Upgrade

There are 3 upgrade options ranging from upgrading In-place which is over the top of your existing system to migrating your data from your existing CRM system to a completely new CRM 2013 system. 

If you have VM’s you will want to try the In-place upgrade first as it updates similar to a rollup and if successful is the fastest.

CRM 2013 Modifications

Optional 1: Most of the XRM systems I’ve worked on have numerous plugins that fire when the vast majority of the CRM entities are modified. If you have a large number of plugins that fire like this and your system could be bogged down by frequent saves by your users, you may consider disabling the new Autosave feature in 2013 at least initially. 

http://blogs.msdn.com/b/crm/archive/2013/10/15/manage-auto-save.aspx

This feature can be disabled on a form by form basis later.

Optional 2: Microsoft recommends migrating to the new forms created for each entity in 2013.  The CRM 2011 forms will be named “Information”. The new 2013 forms will have the entity name like “Account”.  From the new entity form there is a Merge feature that will pull the customizations from the 2011 form and put those customizations at the bottom of the form.  There will be some work to clean these forms up.

Optional 3: You can merge your database in 2013 however this is a required step in the CRM 2015 Upgrade, so I prefer to let the latest upgrade code take care of this step. This is the merger of the Base and Extension tables in the CRM database.

Running the CRM 2015 Upgrade

There are 3 upgrade options ranging from upgrading In-place which is over the top of your existing system to migrating your data from your existing CRM system to a completely new CRM 2013 system.

Since this is the step when the database tables will be merged, I would suggest going with the MS recommendation to migrate from the Updated 2013 system to a fresh CRM 2015 system especially on a production system. If you have VM copies to play with, upgrading In-place is less of a risk.

CRM 2015 Modifications

MS says that CRM 2015 will use CRM 2011 SDK plugins but substitute calls to the 2011 SDK with calls from the CRM 2015 SDK.

However there are some important changes that need to be handled.

As of CRM 2015 Update 1 which is currently only used on CRM Online as I type this.  For plugin registration the SetState and Assign messages have been deprecated. Ownership and the state of an entity can now be changed using Update rather than separate AssignRequest and SetStateRequest messages.

Once your plugins are registered to fire on “Update” make sure to set their filtering attributes to be appropriate  “ownerid” and “statecode” for example and you may need to check your execution order if you have other plugins firing on the Update of an entity.

MS CRM 2013 Online upgrade to Office 365 authentication

Recently many CRM Online customers were transitioned from Live ID to Office 365 authentication.

The result is that the earlier Live ID users accounts no longer exist and any software still using Live ID authentication stopped working.

This requires updating how any portal code authenticates and using a new Office 365 user account to authenticate with.

In addition the CRM 2013 Email Router needs to be reconfigured and updated to the Rollup 2 version ( at this moment in time ).

O365 Authentication

If you start with the latest CRM 2013 SDK  example AuthenticateWithNoHelp solution, you can add the code snippet below to the file  AuthenticateWithNoHelp.cs to get O365 authentication to work.

First open and compile this solution. Than add the code snippet below to the AuthenticationWithNoHelp class.

The following gives you an IOrganizationService and an IOrganizationServiceContext

If you are incorporating this into an existing CRM 2011 solution, you will need to update your Microsoft.Xrm.Client and Microsoft.Xrm.Sdk references.

The global variables beginning with underscores are used internally in the AuthenticationWithNoHelp class. Substitute your organization name for {orgname}.

public IOrganizationService OrgService { get; set; }
public OrganizationServiceContext OrgContext { get; set; }

private String _discoveryServiceAddress = "https://dev.crm.dynamics.com/XRMServices/2011/Discovery.svc";
private String _userName = "newusername@{orgname}.onmicrosoft.com";
private String _password = "password";
private String _domain = ""; 


public Authenticate()
{
    IServiceManagement<IDiscoveryService> serviceManagement = ServiceConfigurationFactory.CreateManagement<IDiscoveryService>(new Uri(_discoveryServiceAddress));
    AuthenticationProviderType endpointType = serviceManagement.AuthenticationType;
    
    IServiceManagement<IOrganizationService> orgServiceManagement = ServiceConfigurationFactory.
        CreateManagement<IOrganizationService>( new Uri("https://{orgname}.api.crm.dynamics.com/XrmServices/2011/Organization.svc "));

    AuthenticationCredentials credentials = GetCredentials(orgServiceManagement, endpointType);
    
    using (OrganizationServiceProxy organizationProxy =
        GetProxy<IOrganizationService, OrganizationServiceProxy>(orgServiceManagement, credentials))
    {
        OrgService = organizationProxy;

        organizationProxy.EnableProxyTypes();
     
        OrgContext = new OrganizationServiceContext(organizationProxy);
    }
}

Email Router

If you are running an older version of the email router update to the appropriate 32 or 64 bit version found here.

http://www.microsoft.com/en-us/download/details.aspx?id=42272
Install and reboot.

 

To reconfigure your Email Router:

1. Change the Server URL  from dev.crm.dynamics.com to disco.crm.dynamics.com

2. Change the User Name and Password to new a new Office 365 account

Then under Configuration Profiles Set the Outgoing Access Credentials to match the Office 365 Credentials used above.

Now

1. Publish your changes
2. Load Data
3. Test Access

That may not cover every Email Router configuration change, but it should cover most of them.

MS CRM and Portals (fast caching method)

Let’s say that you have a portal application that is very data driven by highly related data, or that has some displays that require a lot of heavily related data to very flexibly dynamically build the controls on the pages.  Leveraging the quick prototyping of MS CRM to design your schema, and managing this controlling data within  MS CRM helps abstract away a lot of grunt work and very quickly get  your portal online,  but the time required to retrieve a long stream of related data by web services is affecting the performance of your website. Because your pages are very data driven depending on the user who logs in some of the website caching available out of the box isn’t helping much because of many web services calls required to customized the information.

What if you could cache all of this related data that you needed in an extremely quick and efficient way so that the portal could get the data that drives it in an nearly instant way?

In this example there is a MS CRM Online system and an interactive Dashboard displayed on a Portal. This is a simple example, but the real project contains a very large amount of data nested many levels deep.  All the queries by web services would take well over a minute or two to run, but a typical portal user is not that patient.

Caching Mechanism

Advantages of this mechanism:

1. Requires a single select to a local database to fetch extremely complex relational data that is already organized into a consumable format.

2. Utilizes a background task to do the heavy lifting to keep things up to date without the portal user having to wait for a many web services calls to MS CRM and also allows for processing of the raw data if need be.

3. Can be part of a plan to allow a portal to be self sufficient for periods of time when the CRM system is not available.

Overview

Part 1: Filling the Cache

1. Query data from MS  CRM via web services in a background task. This could be a scheduled applications or service on a timer that keeps the cached updated.
2. Put data into related serializable containers under one container with all of the information needed for a single account in the format needed to drive your application.
3. Serialize this container
4. Write it to the local SQL database as a single blob with an identifier.

Part 2: Reading the Cache:

1. With a single select from the local database return the entire blob of information needed.
2. Deserialize this blob
3. Cast it to the Parent container class.
4. Consume it in your Portal application.

Example Code 

This example is very simplistic. The class structure and queries to pull MS CRM data can be as complex as needed. The real gains to using this are when there is a lot of related data requiring a large number of queries.

Nested Serialized Classes

All classes must be serializable. The top level container class will contain all other containers or lists of containers

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Xml;
using System.Xml.Serialization;


/// <summary>
/// Top Level Serializable Container 
/// All classes contained must also be serializable 
/// </summary>
[Serializable]
public class DashboardBlob
{
    public Guid AccountId;
    public DateTime LastUpdated;
    public List<ContactS> students = new List<ContactS>();
}

/// <summary>
/// Nested Serializable class
/// </summary>
[Serializable]
public class ContactS
{
    public Guid ContactId;
    public string FirstName;
    public string LastName;
    public string EMailAddress1;
}

Filling the Serialized Classes

This can be done in any way that queries the data needed and fills the appropriate serialized classes.

/// <summary>
/// Create Top level container and call methods to fill all nested containers
/// </summary>
/// <param name="accountId"></param>
public static DashboardBlob FillDashboardBlob(Guid accountId)
{
    var blob = new DashboardBlob
        {
            AccountId = accountId,
            LastUpdated = DateTime.Now,
            students = GetSerializedContactsByAccount(accountId)
            //pull all other related data and nested data.
        };

    return blob;
}

/// <summary>
/// Query that pulls data from a CRM context and stores it in a serializable class
/// </summary>
/// <param name="accountId"></param>
/// <returns></returns>
public static List<ContactS> GetSerializedContactsByAccount(Guid accountId)
{
    var serializedContacts = 
        (from c in DemoContext.ContactSet
        join students in DemoContext.new_account_studentSet on c.ContactId equals students.contactid
        where students.new_accountid == accountId
        orderby c.LastName, c.FirstName
        select new ContactS
        {
            ContactId = c.ContactId.Value,
            FirstName = c.FirstName,
            LastName = c.LastName,
            EMailAddress1 = c.EMailAddress1
        }).ToList();

    return serializedContacts;
}

The code below this point is nearly generic with the exception of the field names for your SQL tables.

Create a simple local SQL table for cache storage

Save/Update the Local SQL Cache

Now that you have the data stored in a serialized container, serialize that container and store in the local SQL server database as a blob.

/// <summary>
/// Update that writes the blob back to SQL
/// </summary>
/// <param name="blob"></param>
/// <param name="connectionString"></param>
/// <param name="isCreate"></param>
public static void SaveUpdateDashboardBlob(DashboardBlob blob, string connectionString, bool isCreate)
{
    var bytes = SerializeAnObject(blob);
    var connection = new SqlConnection(connectionString);
    SqlCommand sqlCmd;
    connection.Open();
    if( isCreate) 
        sqlCmd = new SqlCommand("INSERT INTO dbo.MainCache(accountid,updated,dashboardxml) VALUES (@id,@updated,@xml)", connection);
    else
        sqlCmd = new SqlCommand("UPDATE dbo.MainCache Set updated=@updated, dashboardxml=@xml where accountid=@id", connection);
    sqlCmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier);
    sqlCmd.Parameters["@id"].Value = blob.AccountId;
    sqlCmd.Parameters.Add("@updated", SqlDbType.DateTime);
    sqlCmd.Parameters["@updated"].Value = blob.LastUpdated;
    sqlCmd.Parameters.Add("@xml", SqlDbType.Xml, Int32.MaxValue);
    sqlCmd.Parameters["@xml"].Value = bytes;
    sqlCmd.ExecuteNonQuery();
    connection.Close();
}

public static string SerializeAnObject(object AnObject)
{
    var Xml_Serializer = new XmlSerializer(AnObject.GetType());
    var Writer = new StringWriter();
    Xml_Serializer.Serialize(Writer, AnObject);
    return Writer.ToString();
}

Read the Local SQL Cache

This is the payoff. A single call to the local database returns a completely organized sorted and ready for consumption data container.

/// <summary>
/// Read the blob from local SQL database, deserialize it and cast it back to the container class.
/// </summary>
/// <param name="accountId"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
public static DashboardBlob GetDashboardBlob(Guid accountId, string connectionString)
{
    var connection = new SqlConnection(connectionString);
    connection.Open();
    var adapter = new SqlDataAdapter(string.Format("select dashboardxml from dbo.MainCache where accountid='{0}' ", accountId), connection);
    var ds = new DataSet();
    adapter.Fill(ds);
    DataTable table = ds.Tables[0];
    connection.Close();

    if (table.Rows.Count != 0)
    {
        var data = (DashboardBlob)DeSerializeAnObject(table.Rows[0][0].ToString(), typeof(DashboardBlob));
        return data;
    }
    return null;
}

public static Object DeSerializeAnObject(string xmlOfAnObject, Type objectType)
{
    var strReader = new StringReader(xmlOfAnObject);
    var xmlSerializer = new XmlSerializer(objectType);
    var xmlReader = new XmlTextReader(strReader);
    try
    {
        Object anObject = xmlSerializer.Deserialize(xmlReader);
        return anObject;
    }
    finally
    {
        xmlReader.Close();
        strReader.Close();
    }
}

The new MS CRM 2011 64 bit VM and VirtualBox

I’ve gotten a number of emails asking what was needed to get the new Partner Source MS CRM 2011 64bit Virtual Machine to work in Virtual Box in Windows 7.

The compatibility issue appears to be the default hard disk settings. The following is a workaround that has worked for many people.  I’ve been told this doesn’t work on Windows 2008 Servers, but on a 2008 server you could also run Hyper-V.

Change your VHD to run from an IDE controller:

Open up your storage settings and add the vhd as an IDE drive.  Click the right + icon to create a new virtual hard  drive. Choose an existing drive.

Select the VHD file the you decompressed and open it.

Now  remove your SATA drive

Now Click OK.

Memory

This VM likes 2.5Gb or more RAM to operate in. As always more is better especially if you are going to demonstrate both CRM and SharePoint together.

Now Start it up!

Addendum

You should enable hardware virtualization when running 64-bit OS’s.  This  is a firmware setting on your motherboard. This is normally called “Intel Virtualization”,  “VT-x” or “Intel VT” and AMD has an equivalent setting. Laptops frequently have this disabled by default.