CRM 2011 Native Self-Referential N:N

So, anyone who’s worked with CRM for a while has probably created a self-referential relationship, either 1:N or N:1, well they are the same I guess. They can be a bit confusing sometimes, you have a lookup on the form and a navigation link on the left hand side representing the relationship, and you need to know which one to go to in order to see where you are. But with good views then it is clear.

Ever try to create a self-referential N:N relationship? Well, that’s a different story. I had the requirement recently of having an entity that contained a bunch of distinct lists, and the records in these linked needed to be related to each other in different ways. I ruled out a 1:N self-referential relationship, as it needed to be a little more flexible than that. There was no need for data defining the relationship, so I was happy to go for native N:N rather than manual N:N.

So setting up the relationship was straight forward, nothing out of the ordinary. I chose to display the relationships on the entity, as I know about the bug where a N:N relationship does not appear in the Advanced Find view if it is not displayed (still using 2011 unfortunately, hopefully this is fixed in later versions!).

Snap 2016-09-01 at 16.30.27

So when I opened the form after creating the relationship, I saw the following…

Snap 2016-09-01 at 16.36.30

Notice the two Test Entity links on the left hand side. This is confusing! Which one is which?! Well, this makes sense to me in a way because if you created an N:N relationship between 2 different entities, you would end up with a navigation link on either entity. In this case, they are both on the same form. Essentially they mean ‘Link To’, and ‘Link From’. See the following link to see a similar description of this process up to this point – http://sumedha8.blogspot.ie/2013/02/self-referential-relationship-explained.html

Now, so far this might make sense if you were setting up a hierarchical relationship in some way, but my requirement was a very loose relationship structure, where things were just ‘linked’, but neither record was parent or child. It would be unacceptable in my opinion to ask users to keep clicking on the Test Entity links until they found the relationship they needed. I just want them to see what the record was linked to. So I decided to add a sub-grid. This looked great, I was able to open a record, link to another record, and it showed up in the grid. But when I opened up the record I just linked to, expecting to see a link to the record I just came from, what I saw was an empty sub-grid.

Capture

So why was I not seeing the relationship from both ends? It’s the same reason I had two navigation links on the form. I think there is a peculiar issue with how CRM handles an N:N relationship with regards a sub-grid. If you can imagine two different entities with an N:N relationship; if you linked a record from one to a record on the other entity, and then clicked into the other record, you could immediately look back at the original record via a navigation link or a sub-grid. The same is not true for N:N self-referential relationships. I’m not sure I could even call this a bug, as it’s quite an edge-case, but all the same CRM could be designed to handle it better.

Let’s look at the intersect table to explain further.

dbsnap
Posted in Uncategorized

Deployment folder not created when building an SSIS project

This is a simple problem, but can be confusing. When you build an SSIS project, a deployment folder should be created in the bin directory, which will contain the manifest file and any other relevant files. If you are building the project but not finding the Deployment folder, then you need to update a project property. Right click on the project name, go to Properties. When the popup appears, click on Deployment Utility tab, and find the property ‘CreateDeploymentUtility’. If this is set to False, then the Deployment folder will not be created. Change this to True, save, and build again, and you should find the Deployment folder.

ssisprojectproperties

Posted in Uncategorized

SQL – Using Row Numbers, Partitions and Counts

So I had a requirement to update a new SQL field with data based on existing data on a related table. The complication is that the conditions used to determine the value of the new field were not straight forward, mainly involving counts. So I started off by writing a standard update query but soon found that the conditions would need to be handled a different way.

I’ll say straight up now that I am no SQL expert, and I can accept that at this stage I never will be! But I get by, and when I have some SQL to do I will muddle through it. My solutions might not be the most efficient way, but in general I tend to be writing one-shot statements to keep fields up to date, or as part of changes where fields are being added and need to be filled in retrospectively, so performance is not an issue.

In this example, I have two tables, Table A and Table B. Table A has a 1:N relationship with Table B (Table A can have many Table B’s). They both have a status field, but the one on Table A is new, and needs to be updated based on the status of Table B. The determining factors on what the value is depends on how many Table B’s each Table A along with the status of the most recent Table B record.

So my solution was to split the queries up. I could not think of a way of doing this in one query, although I am sure there is a way, maybe with multiple sub-queries involved. My first query sets up the data that I need, including a count of the Table B’s and only including that latest Table B entry for every Table A entry. So what I wanted was, for every Table A entry, to only select the latest Table B record, but also to include the count of how many Table B entries there are for that Table A entry. So after some searching I came across the wonderful Over and Partition keywords, both of which solved the problem. It allows me to deal with records as a group, but without grouping them.

WITH cte AS
(
   SELECT TableA.Id as 'Table_A_ID'
	  ,TableB.Id as 'Table_B_ID'
	  ,TableB.Status as 'Table_B_Status'
	  ,ROW_NUMBER() OVER (PARTITION BY TableA.Id ORDER BY TableB.Id DESC) AS rn
	  ,Count(*) OVER (PARTITION BY TableA.Id) AS cnt
   from TableA a
   left outer join TableB b
   on b.tablea_id = a.Id
)
SELECT Table_A_ID, Table_B_ID, Table_B_Status, cnt
into #tempTable
FROM cte
WHERE rn = 1
order by TableA.Id

So I add a row number to each entry, but the number starts at 1 for each new Table A entry, and increments for every associated Table B entry. I also include a Count on every row, again based on how many Table B entries per Table A. This is a bit redundant to have the count. on every row but it just simplifies things. Finally I filter the results based on the row number, only keeping the ones with Row Number = 1.

Just to focus in on the Partition command for a moment; the value after the Partition By statement is what the dataset will be partitioned by, allowing me to add the row numbers to each individual group. I also use Partition By to perform counts of each individual group based on the same criteria, just no ordering required, or allowed actually. So it’s very useful. Obviously I could group the records based on the relationship, which would allow to me easily get the counts, but then I’ve lost the granular data at the TableB level. So Partition By allows me to deal with the groups in turn but without losing any data to groupings.

Finally, with my resulting dataset, the updating of the field is straight-forward. The conditions for what to set the new field to are meaningless here, I’ve replaced the real code with letters, but it doesn’t matter, the point is that it is easy to do once the data is prepared correctly.

update TableA
set Status = 
(
	CASE
		WHEN ((tmp.Table_B_ID= 'X' OR tmp.Table_B_ID= 'Y') AND cnt = 1) THEN 1
		WHEN ((tmp.Table_B_ID= 'X' OR tmp.Table_B_ID= 'Y') AND cnt > 1) THEN 2
		WHEN (tmp.Table_B_ID!= 'x' AND tmp.Table_B_ID!= 'Y') THEN 3
	END
)
from #tempTable tmp
where TableA.Id = tmp.Table_A_ID

Hope this will be of some use to someone out there. Often when I work with SQL all I need is a pointer in the right direction, then there is ample material on the web to provide examples.

By for now, and happy SQL scripting!

Tagged with: , ,
Posted in SQL, Uncategorized

SSIS using Table variable but returning no rows (SET NOCOUNT ON)

Recently had a problem in an SSIS package, which was retrieving data from a SQL database. There was a column that needed a bit of logic to construct, based on other values on the record, so I decided to return all the relevant data needed for the string construction first, and then update the column in a second SQL statement.

For this I needed to use temporary tables. I decided against using the Create command, as I didn’t want to keep the table for any longer than the script was to run. I initially used the ‘select * from X into #MyTable’ syntax but this was not valid in SSIS. Eventually I settled on the ‘Declare @MyTable’ syntax, which only creates a table for the duration of the script.

Capture

This worked fine in SQL, but when I ran it in SSIS, no rows were returned. After some digging, I found that the data being returned from a successful query (the data that normally shows you how many rows were affected in SQL Server Management Studio) is actually quite large and needs to be turned off in this example. You can use the SET NOCOUNT ON command to not return any data after the query. The script worked fine in SSIS once this line was in place.

Capture

Here’s the blog that alerted me to this first, and here is more information from MSDN about the data returned after a query.

 

 

Posted in Uncategorized

MVC – Calling pages individually

If you developing a new page that you want to test in isolation, you can go to it directly by using the following URL:

http(s)://{servername}/{Name of Controller class without the word ‘Controller’}

Now, for this to be true there are a few assumptions. Let’s take a sample URL:

https://localhost:44306/Complaints/

This URL implies that there is a Controller in the project called ComplaintsController. If so, the next piece of convention is how it finds the View. Take the sample Controller below:

MVCController

This class and method assumes that there is a View:

View\{Name of Controller class without the word ‘Controller’}\{Name of method in Controller}.cshtml.

Finally, if you are testing a particular View over and over, you can specify a URL for the project to run on startup, but going into Project Settings, Web, and set the Starting URL to be the URL you want to test.

MVCWebSettings

Posted in Uncategorized

Auto-Registering a Plugin in CRM 2011

I recently had to write code that would auto-register a plugin step & image for a given entity. The CRM SDK allows for this by exposing the required objects representing the Plugin itself, the Plugin Steps, and the Plugin Step Images. The structure of these objects was at first a little confusing to me, so I drew the relationships out and formed the diagram below.

pluginstructure

PluginType

The PluginType object represents the actual plugin registered against an assembly. This object needs to be retrieved via the normal CRM web services, based on the textual name of the plugin. See the screenshot below to identify the name required. The example used here is a generic Count Mapping functionality, to count the number of ‘N’ records in a 1:N relationship, and store the result in a field in the ‘1’ entity.

pluginregistrationtool

SDKMessageProcessingStep

This object represents the step registered against a plugin, and is the equivalent of adding a new step in the Plugin Registration Tool. It has a number of attributes, equivalent to the ones you see in the Plugin Registration Tool. I was expecting this object to have text properties for MessageType and Entity, as they are text inputs in the Plugin Registration Tool, but it turns out it is more structured than that. The SDKMessageProcessingStep relies on the the SDKMessageFilter object to know what message type and entity the step is registered against.

SDKMessageFilter

This object represents a unique combination of entity and message. This object matches up directly with the SDKMessageFilterBase table in the SQL database, and contains an entry for each unique combination of entity and message type. For example, see the screenshot to the right, showing the SDKMessageFilter table just for the Email entity. There is a record for each message type. When an entity is created, this table must be populated with every combination to make this possible.SDKMessageFilters

SDKMessage

This object matches up directly with the SDKMessageBase table in the SQL database. It contains an entry for every type of message possible. See the screenshot below for a sample of the contents of the table. The CRM web services can be used to retrieve these entries as SDKMessage objects, for use programmatically.

SDKMessages

SDKMessageProcessingStepImage

This object represents images registered against plugin steps. There is nothing really complicated about this object, other than it just needs to reference a SDKMessageProcessingStep to know what plugin step to register against.

 

Creating the Plugin Step/Image

So, in order to create a step programmatically, the following steps need to be performed:

  • Retrieve the PluginType object
  • Retrieve the MessageType object (Create, Update, Delete, Associate, etc.)
  • Retrieve the SDKMessageFilter object (eg. Create for Email, Update for Contact, etc.)
  • Construct the SDKMessageProcessingStep object and update via the organisation service
  • Construct the SDKMessageProcessingStepImage object and update via the organisation service

Below are code samples for how to achieve this. Note that I used Dynamic Entities and QueryExpression so there were no dependencies in this code. This means it can be lifted out of one CRM environment and put into another

/// <summary>
/// Retrieves the actual registered plugin type, that steps will be registered against
/// </summary>
/// <param name="service"></param>
/// <returns></returns>
public static Guid RetrievePluginType(IOrganizationService service, String pluginName)
{
   try
   {
      QueryExpression query = new QueryExpression()
      {
         Distinct = false,
         EntityName = "plugintype",
         ColumnSet = new ColumnSet("plugintypeid"),
         Criteria =
         {
            Filters = 
            {
               new FilterExpression
               {
                  FilterOperator = LogicalOperator.And,
                  Conditions = 
                  {  
                      new ConditionExpression("typename", ConditionOperator.Equal, pluginName)
                  }
               }
            }
         }
      };

      EntityCollection entitiesRetrieved = service.RetrieveMultiple(query);

      if (entitiesRetrieved != null && entitiesRetrieved.Entities != null && entitiesRetrieved.Entities.Count > 0)
         return entitiesRetrieved.Entities[0].Id;
      else
         return Guid.Empty;
   }
   catch (Exception ex)
   {
      throw ex;
   }
}
/// <summary>
/// Retrieves a message type (Create, Delete, Associate, etc)
/// </summary>
/// <param name="service"></param>
/// <returns></returns>
public static Guid RetrieveMessageType(IOrganizationService service, string messageType)
{
   try
   {
      QueryExpression query = new QueryExpression()
      {
         Distinct = false,
         EntityName = "sdkmessage",
         ColumnSet = new ColumnSet("sdkmessageid"),
         Criteria =
         {
            Filters = 
            {
               new FilterExpression
               {
                  FilterOperator = LogicalOperator.And,
                  Conditions = 
                  {
                     new ConditionExpression("name", ConditionOperator.Equal, messageType)
                  }
               }
            }
         }
      };
      EntityCollection entitiesRetrieved = service.RetrieveMultiple(query);
      if (entitiesRetrieved != null && entitiesRetrieved.Entities != null && entitiesRetrieved.Entities.Count > 0)
         return entitiesRetrieved.Entities[0].Id;
      else
         return Guid.Empty;
   }
   catch (Exception ex)
   {
      throw ex;
   }
}
/// <summary>
/// Retrieves a SDK Message Filter, which is a record linking an entity to a message
/// (There is a record in the system for every combination of message type and entity)
/// </summary>
/// <param name="service"></param>
/// <returns></returns>
public static Guid RetrieveSDKMessageFilter(IOrganizationService service, Guid messageTypeId, string entityName)
{
   try
   {
      QueryExpression query = new QueryExpression()
      {
         Distinct = false,
         EntityName = "sdkmessagefilter",
         ColumnSet = new ColumnSet("sdkmessagefilterid"),
         Criteria =
         {
            Filters = 
            {
               new FilterExpression
               {
                  FilterOperator = LogicalOperator.And,
                  Conditions = 
                  {
                     new ConditionExpression("primaryobjecttypecode", ConditionOperator.Equal, entityName),
                     new ConditionExpression("sdkmessageid", ConditionOperator.Equal, messageTypeId)
                  }
               }
            }
         }
      };
      EntityCollection entitiesRetrieved = service.RetrieveMultiple(query);

      if (entitiesRetrieved != null && entitiesRetrieved.Entities != null && entitiesRetrieved.Entities.Count > 0)
         return entitiesRetrieved.Entities[0].Id;
      else
         return Guid.Empty;
   }
   catch (Exception ex)
   {
      throw ex;
   }
}
/// <summary>
/// Retrieves the registered plugin step for a given SDK Message Filter (combo of message type & entity name) and Plugin Type
/// </summary>
/// <param name="service"></param>
/// <returns></returns>
public static Guid RetrieveSdkMessageProcessingStep(IOrganizationService service, Guid pluginTypeId, Guid sdkMessageFilterId)
{
   try
   {
      QueryExpression query = new QueryExpression()
      {
         Distinct = false,
         EntityName = "sdkmessageprocessingstep",
         ColumnSet = new ColumnSet("sdkmessageprocessingstepid"),
         Criteria =
         {
            Filters = 
            {
               new FilterExpression
               {
                  FilterOperator = LogicalOperator.And,
                  Conditions = 
                  {
                     new ConditionExpression("plugintypeid", ConditionOperator.Equal, pluginTypeId),
                     new ConditionExpression("sdkmessagefilterid", ConditionOperator.Equal, sdkMessageFilterId)
                  }
               }
            }
         }
      };
      EntityCollection entitiesRetrieved = service.RetrieveMultiple(query);

      if (entitiesRetrieved != null && entitiesRetrieved.Entities != null && entitiesRetrieved.Entities.Count > 0)
         return entitiesRetrieved.Entities[0].Id;
      else
         return Guid.Empty;
   }
   catch (Exception ex)
   {
      throw ex;
   }
}
/// <summary>
/// Retrieves all registered plugin steps for a given Plugin Type
/// </summary>
/// <param name="service"></param>
/// <returns></returns>
public static EntityCollection RetrieveAllSdkMessageProcessingStepsForPlugin(IOrganizationService service, Guid pluginTypeId)
{
   try
   {
      QueryExpression query = new QueryExpression()
      {
         Distinct = false,
         EntityName = "sdkmessageprocessingstep",
         ColumnSet = new ColumnSet("sdkmessageprocessingstepid"),
         Criteria =
         {
            Filters = 
            {
               new FilterExpression
               {
                  FilterOperator = LogicalOperator.And,
                  Conditions = 
                  {
                     new ConditionExpression("plugintypeid", ConditionOperator.Equal, pluginTypeId)
                  }
               }
            }
         }
      };
      EntityCollection entitiesRetrieved = service.RetrieveMultiple(query);
      return entitiesRetrieved;
   }
   catch (Exception ex)
   {
      throw ex;
   }
}
/// <summary>
/// Creates a plugin step
/// </summary>
/// <param name="service"></param>
/// <param name="messageName"></param>
/// <param name="messageId"></param>
/// <param name="entityName"></param>
/// <param name="pluginTypeId"></param>
/// <param name="pluginStepStage"></param>
/// <param name="pluginStepMode"></param>
/// <param name="pluginStepSupportedDeployment"></param>
/// <returns></returns>
public static Guid CreatePluginStep(IOrganizationService service, String messageName, Guid messageId, String entityName, Guid pluginTypeId, PluginStepStage pluginStepStage, PluginStepMode pluginStepMode, PluginStepSupportedDeployment pluginStepSupportedDeployment)
{
   Entity step = new Entity();
   step.LogicalName = "sdkmessageprocessingstep";
   step.Attributes["name"] = "Cdss.Count.Plugins.CountPlugin: " + messageName + " of " + entityName;
   step.Attributes["plugintypeid"] = new EntityReference("plugintype", pluginTypeId);
   step.Attributes["sdkmessagefilterid"] = new EntityReference("sdkmessagefilter", RetrieveSDKMessageFilter(service, messageId, entityName));
   step.Attributes["sdkmessageid"] = new EntityReference("sdkmessage", messageId);
   step.Attributes["mode"] = new OptionSetValue((int)pluginStepMode);
   step.Attributes["asyncautodelete"] = false;
   step.Attributes["rank"] = 1;
   step.Attributes["stage"] = new OptionSetValue((int)pluginStepStage);
   step.Attributes["supporteddeployment"] = new OptionSetValue((int)pluginStepSupportedDeployment);

   Guid stepId = service.Create(step);
   return stepId;
}
/// <summary>
/// Creates a plugin step image
/// </summary>
/// <param name="service"></param>
/// <param name="pluginStep"></param>
/// <param name="pluginImageType"></param>
/// <returns></returns>
public static Guid CreatePluginStepImage(IOrganizationService service, Guid pluginStep, PluginImageType pluginImageType)
{
   Entity image = new Entity();
   image.LogicalName = "sdkmessageprocessingstepimage";
   image.Attributes["sdkmessageprocessingstepid"] = new EntityReference("sdkmessageprocessingstep", pluginStep);
   image.Attributes["name"] = Enum.GetName(typeof(PluginImageType), pluginImageType);
   image.Attributes["entityalias"] = Enum.GetName(typeof(PluginImageType), pluginImageType);
   image.Attributes["imagetype"] = new OptionSetValue((int)pluginImageType);
   image.Attributes["messagepropertyname"] = "Target";

   Guid imageId = service.Create(image);

   return imageId;
}
Posted in CRM 2011, Plugin, Plugin Registration Tool

WPF UserControl with Binding Example

Just starting getting my teeth into WPF, trying to create a CRM 2011 Data Import Tool. I’m learning as I go, and blogging as I pick up stuff.

Today I want to address UserControls. As far as I understand, these are Xaml components in their own right, but can be embedded into normal windows. I see it like a sub-report in SSRS – the sub-report is a genuine report, but is designed to be used as part of a ‘parent’ window.

So I have a fairly simple example, where I’ve created a WPF application called TestWPFApp, with the default MainWindow window. I’ve then created a UserControl called TestUserControl, and added a TextBlock to it.

<UserControl x:Class="TestWPFApp.TestUserControl"
   xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
   xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
   xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
   xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
   mc:Ignorable="d"
   d:DesignHeight="300" d:DesignWidth="300">

   <Grid>
      <TextBlock Text="Hello (defined in Xaml)"/>
   </Grid>
</UserControl>

Everything in the code above is auto-populated except for the TextBlock, which is a simple as possible, declaring its text in the Xaml itself. So the next question is how we can use this in MainWindow. The first trick is to reference the namespace that the UserControl is part of, which in this case is TestWPFApp. See the Xaml below, which adds this namespace in Line 4 and calls the UserControl in Line 8 by referencing the namespace…

<Window x:Class="TestWPFApp.MainWindow"
   xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
   xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
   xmlns:local="clr-namespace:TestWPFApp"
   Title="MainWindow" Height="350" Width="525">

   <Grid>
      <local:TestUserControl/>
   </Grid>
</Window>

Snap 2015-03-01 at 13.56.55

So now that we are using our UserControl from within a Window, the next step is to use binding to set the value dynamically. This requirement leads to the issue of Data Context, and the simplest way of approaching this so far is to set the DataContext at the MainWindow Initialise method, which will make every child element use that data context by default. Then we can simply point the TextBlock at the property we want.

In the example below, I have created a simple class called TestClass.cs. This class exposes only one property, TestProperty.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestWPFApp
{
    class TestClass
    {
        public string TestProperty { get; set; }
    }
}

To set the Data Context, create an instance of this class in the MainWindow Initialise method, and set the Datacontext on MainWindow directly.

public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();

        TestClass testClass = new TestClass();
        testClass.TestProperty = "Hello";

        this.DataContext = testClass;
    }
}

Once this is done, any child element will by default have this class as its Data context. From the UserControl TextBlock, we don’t even need to set the ElementName property, we can simply point to the property we want to bind to this control.

<UserControl x:Class="TestWPFApp.TestUserControl"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
             xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
             mc:Ignorable="d" 
             d:DesignHeight="300" d:DesignWidth="300">
    <Grid>
        <TextBlock Text="{Binding Path=TestProperty}"/>
    </Grid>
</UserControl>

Snap 2015-03-01 at 22.30.26

So now we have a re-usable UserControl using Binding. This is obviously a very simple example, if there were multiple classes in the data model you would obviously need to point to the correct one, and I’m not sure on best practices regarding the binding method, as no our UserControl is hard-coded to read a property called TestProperty, not very useful if you want to move it to another project. Will hopefully get to update this post as I learn more.

In the meantime, here are two good posts I used as a reference:
What is this ‘Data Context’?
User Control in WPF

Tagged with: , ,
Posted in Binding, Uncategorized, UserControl, WPF

When to use EntityReference in CRM queries

This post is aimed at trying to eradicate an irritation of mine, where I can never remember when comparing against an EntityReference object in a CRM 2011 LINQ query works or not. In the past, I came across an instance when it didn’t work and as a result abandoned the practice, but then I was left with the potential of NullReference exceptions. So this post will examine when they work and when they don’t!

The first example I want to discuss relates to using LINQ to query against the Service Context. See the two examples below

//Works fine, EntityReference is matched to AccountId object
var contacts1 = from contact in ctx.CreateQuery<Contact>()
                where contact.AccountId == new EntityReference(Account.EntityLogicalName, accountId)
                select contact;

//Also works fine, even if there are null values in the AccountId object
var contacts2 = from contact in ctx.CreateQuery<Contact>()
                where contact.AccountId.Id == accountId
                select contact;

So there is no problem using the EntityReference object for comparison when querying directly against the Service Context. There is also no problem accessing the .Id property of the EntityReference object, even if there are null values in the data set. There must be an in-built check for null values in this scenario. So far, so good.

It gets more complicated when running a LINQ query against the result of the queries above, or against a standard collection object. See the code below for an example of what works and what doesn’t work…

//Creating the collection object used in the next 3 queries...
List<Contact> contacts = (from contact in ctx.CreateQuery<Contact>()
                          select contact).ToList();

//Does Not Work! There is no error, but the EntityReference comparison will not find a match
var contacts3 = from contact in contacts
                where contact.AccountId == new EntityReference(Account.EntityLogicalName, accountId)
                select contact;

//The comparison here works, but if there is a null AccountId in the dataset,
//a NullReference exception will occur
var contacts4 = from contact in contacts
                where contact.AccountId.Id == accountId
                select contact;

//Works! The 'where' criteria are processed in sequence (like an If statement),
//so if a null is found, that record is skipped and does not produce a NullReference exception.
var contacts5 = from contact in contacts
                where contact.AccountId != null
                && contact.AccountId.Id == accountId
                select contact;

So the EntityReference object is not successfully compared when dealing with standard collection objects. As a result, you need to compare the EntityReference.Id property with the Guid, but this can lead to NullReference exception errors. To avoid this, simply check for nulls before using the .Id property, as seen in the last example above.

In summary, the most efficient ways of querying against the Service Context and against standard collection objects, regarding the use of the EntityReference class, is shown below.

//Querying against ServiceContext
var contacts = from contact in ctx.CreateQuery<Contact>()
               where contact.AccountId == new EntityReference(Account.EntityLogicalName, accountId)
               select contact;

//Querying against standard collection object
var contacts = from contact in contacts
               where contact.AccountId != null
               && contact.AccountId.Id == accountId
               select contact;

As a final note, the above applies to other LINQ operations. See below the use of the Exists function, where using EntityReference comparison does not work.

//Doesn't work (EntityReference comparison does not find a match)
bool contactExists = contacts.Exists(x => x.AccountId == new EntityReference(Account.EntityLogicalName, accountId));

//Works
bool contactExists = contacts.Exists(x => x.AccountId != null && x.AccountId.Id == accountId);
Posted in CRM 2011, Uncategorized

CRM 2011 Email Templates – Keeping markup consistent

This post is aimed at minimising an annoyance in CRM 2011. When creating Email Templates, the text editor is not trustworthy, and you can often be left with different sized font than expected. Also, trying to keep an Email Template to one font is surprisingly difficult. After fighting it for a while, I found an easy solution to keep things consistent.

The Problem: Often when creating Email Templates, they will look fine in the editor, only to look different when you receive the email itself. The font might be different, and line spacing is a constant problem. A single space in the Email Template translates to a double space in the actual email.

Example: The following screenshots show a simple example of this. I created a brand new Email Template and started typing into the Body field without selecting any font. Then I created a temporary workflow which uses Custom Workflow Activities to instantiate the template and send an email. The first screenshot is the Email Template, and the second shows the actual email created in Outlook.

Snap 2014-07-11 at 13.15.17Snap 2014-07-11 at 13.29.57

As you can see, the line spacing is different in the final version. To investigate further, see below the contents of the Body field in SQL for this template.

Snap 2014-07-11 at 14.51.15

As you can see, each entry is wrapped in the paragraph tag. This tag must be dealt with differently in different browsers/email providers etc. Unfortunately, there is no way in the Text Editor in an Email Template to go to a new line without pressing the Enter key, which in turn creates markup for a paragraph.

Also note that the font used is Tahoma, Verdana and Arial. This occurs when you start typing into the Body field of an Email Template without first selecting a font. While this is ok when you are creating the email template in the beginning, it can lead to problems when others come in to mad updates, which may occur in a different font. Using the text editor to edit the font can lead to complicated markup which is very hard to undo.

Solution: The easiest way to avoid all these complications is to do 2 things:

  • When the Email Template is first created, click into the Body field. Then choose Arial Font, Size 10. Then type anything into the Body field, and save the record. This will simplify the markup. Note that if you don’t type anything in and press Save, you will get the Tahoma, Verdana, Arial markup.
  • Do not press the Return key! If you want to perform a line return, use the <br/> tag. If you want to leave a space between two lines, use the line return twice, <br/><br/>. Basically, write your email in markup, using <b>, <u> and <a> tags where necessary. This for me is the surest way that your email will appear in the correct state. It unfortunately means that the email template will not be easy to edit, but it does rule out any strange formatting problems.

See below the contents of an email template that was created according to these practices, along with the resulting email:

Snap 2014-07-11 at 14.55.58 Snap 2014-07-11 at 14.56.14

Summary: This is not an area you want to spend too much time on, it’s just an annoyance if you find that you’re wasting a lot of development time trying to fix your email templates. As a result, I recommend following the above practices, and keep the email templates simple, to a standard size and font. The text will be hard to read, but will be less prone to errors in future updates, and more predictable.

Happy CRM’ing!

Tagged with: ,
Posted in CRM 2011, Email Templates

SQL – Disabling Foreign Key Constraints

Setting up a Foreign Key between two tables means that you cannot delete data from one table that is required by another. This is fine from a data quality point of view in a production environment, but during development where you might be regularly refreshing the data it can get in the way. You can try to delete the data in the order of most-dependent to least-dependent, but if you just want to refresh one table you might not want to delete a whole hierarchy of data first.

A Foreign Key does not necessarily force constraint though. There is an option in the properties of a Foreign Key where you can specify this. Right-click on a Foreign Key in SQL Management Studio, and click Modify. You can toggle the option Enforce Foreign Key Constraint to turn the constraint on and off.

ForeignKeyProperties

A quicker option is to do it using SQL script. See the excerpt below, which does the exact same thing in script. It turns the constraint off in the first line, and back on in the second line.

Snap 2014-06-06 at 10.58.20

Tagged with: ,
Posted in SQL