All about outer join queries in CRM 2011 and CRM 2013

Outer joins are the way to implement the commonly requested queries in which you are searching records that have no child records (e.g. All accounts without contacts, all users without security roles, etc.). This article explains what the options are for such queries in CRM.
A recurring business scenario is to have a view of records with no child records (display all leads without activities, accounts that have no contacts, etc.) and we also have sometimes a similar requirement but for N:N relationships, for example, provide a list of all users without security roles. Unfortunately achieving these requirements is not as straight forward as we’d like.
Ideally we’d like to be able to use Advanced Find to produce those results. The reality is that Advanced Find does not support outer join constructs so we are not able to build such queries. So what are the options?

1. CRM 2011 or CRM 2013: SQL Report
If you are not in CRM Online you can always build a custom SSRS report and build your SQL query to provide such results. This is a very straight forward query in SQL. For example, if you’d like to retrieve all leads that have no tasks associated the following query will do:

SELECT lead.FullName
FROM Leads as lead
LEFT OUTER JOIN Tasks as ab
ON (lead.leadId  =  ab.RegardingObjectId)
WHERE ab.RegardingObjectId is null

2. CRM 2011: Use the SDK

You can always use the CRM SDK to query records and process the results. However, there is no way to specify an outer join in a QueryExpression, FetchXML or LinqExpression in CRM 2011 in the way that we need. Note that you might find a “LeftOuter” join type in the JoinOperator enum, however, this will not help you in this case. Therefore if you want to retrieve all leads without tasks you’d have to do multiple queries and filter the results manually in your application. For example, if you are looking for all leads without tasks then you’d have to first retrieve all leads and then retrieve all tasks regarding a lead. Finally, you’d then have to merge the results to exclude from your entire lead list those that have a task from your second query. This is less than ideal because if you have thousands of records then making use of paging can be a challenge. It is also not possible to have a view in CRM that would return these results because the CRM platform in CRM 2011 does not support this construct in a single query operation.



3. CRM 2013: Use FetchXML

The good news is that in CRM 2013 the platform has been improved to be able to process these type of queries. Therefore FetchXML capabilities and syntax have been expanded to be able to use outer joins. For our example of retrieving all leads without tasks, this would be the fetchXML you would need:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="lead">
    <attribute name="fullname" />
    <link-entity name="task" from="regardingobjectid" to="leadid" alias="ab" link-type="outer">
       <attribute name="regardingobjectid" />
    </link-entity>
    <filter type="and">
        <condition entityname="ab" attribute=" regardingobjectid" operator="null" />
    </filter>
  </entity>
<fetch/>
The bad news is that the Advanced Find interface does not yet support designing these type of queries. However, you can update the fetchXML of your system view or your saved query and it will work (users can access this query via CRM views), nonetheless, they will not be able to design or modify this query in the Advanced Find designer, they can only see the results. To update the FetchXML you would need to export the view in a solution and then modify the FetchXML of your view in the customizations.xml file, then you can import the solution back and voila.


4. CRM 2013: Use QueryExpression

Since we know that besides aggregation capabilities, FetchXML and QueryExpression are equivalent (any FetchXML query can be converted to QueryExpression and vice-versa), then we know that QueryExpression has also been expanded to support outer joins. Here is the CRM 2013 syntax you can use in your QueryExpression for outer joins, the following query returns all leads that have no tasks associated:

QueryExpression qx = new QueryExpression("lead");
qx.ColumnSet.AddColumn("subject");

LinkEntity link = qx.AddLink("task", "leadid", "regardingobjectid", JoinOperator.LeftOuter);
link.Columns.AddColumn("subject");
link.EntityAlias = "tsk";

qx.Criteria = new FilterExpression();
qx.Criteria.AddCondition("tsk", "activityid", ConditionOperator.Null);

Perfect, now what about N:N relationships?

For example, how can you query users that have no security roles associated? (There is an N:N relationship between user and security role). The trick with N:N relationships is that the “intersect” entity is actually an entity that can be queried, therefore you can see N:N relationships as two 1:N relationships:

SystemUser : Role (N:N)

Is the same as the combinaation of these two:

SystemUser : SystemUserRoles (N:1)

SystemUserRoles : Role (1:N)

Every N:N relationship has an intersect entity, in the case of the user : role relationship, the name of the intersect entity is “systemuserroles”. Therefore if you want to query users that have no security roles, this is the same as querying users who have no SystemUserRoles (intersect entity) associated, and that is a simple 1:N relationship. So you could simply do it in this way:

QueryExpression qx = new QueryExpression(SystemUser.EntityLogicalName);
LinkEntity link = qx.AddLink(SystemUserRoles.EntityLogicalName, "systemuserid", "systemuserid", JoinOperator.LeftOuter);
link.Columns.AddColumn("fullname");
link.EntityAlias = "sur";
qx.Criteria = new FilterExpression();
qx.Criteria.AddCondition("sur", "systemuserroleid", ConditionOperator.Null);

Pregúntale a los expertos CRM!

Para todos los hispanos interesados, nuestros amigos de la Comunidad CRM han organizado un evento este jueves 20 de Febrero para responder preguntas generales de la comunidad. Regístrate y envía tus preguntas para que varios líderes de la comunidad CRM te respondan en vivo.
Las siguientes personas (MVPs o empleados de Microsoft) estarán presentes para responder tus preguntas:
•Gus Gonzalez (MVP, Zero2Ten)
•Damián Sinay (MVP, Remoting Coders)
•Ramón Tebar (MVP, MetroBank)
•Demian Raschkovan (MVP, Infoaván)
•Gonzalo Ruiz (MVP, Avanade)
•Pablo Peralta  (MVP, UruIT Dynamix | CRMGamified)
•Jimmy Larrauri (Microsoft)
No olvides registrarte y enviar tus preguntas!

Quick Views in CRM 2013

One of the simplest and most exciting features of CRM 2013 (Orion) is the ability to embed and display fields from the parent entity in the form a given record. This feature is not nearly as promoted as other features but I like it so much I will write about it.

Many times in the past I have struggled with the need to include fields from a related entity in the form of a given record. Typical scenario is that you would want to include the account number and the primary contact of the account in the form of a given case for which the customer is an account. Additionally, if the customer of the case is a contact, then I’d like to display in the case form the contact name, contact phone number and list of activities associated with the contact in the case form. While this was a nightmare to implement in previous version of CRM (require custom web resources that query and display data from related entities), all this is possible very easily out-of-the-box with MS CRM 2013 without having to write a line of code.

First, I will define the quick view forms for contact and account which I will later include in the case form:

 

Now that I have defined the quick view forms for both the accounts and contact, I can modify my contact form to include the related information form the account/contact. I have created a “Customer Details” section in the case form and will now insert the quick view forms I defined earlier:

 

Then I need to specify the quick view forms that I will use and for which lookup field it applies:

 

After I publish my case form, this is what it looks like if the contact of the case is an account:

 

If I change the customer field on the case, the form is updated dynamically and the “customer details” section is updated to display the contact quick view form I defined:

 

You can see that I can even insert sub-grids for records related directly to my lookup field (in this case activities related only to my customer contact). It is also  worth noting that fields from the quick view form are always read-only (e.g. From a case form, I cannot see the phone number of the contact associated, but I cannot edit it). If I need to edit fields from the related record I have to open the record and edit them in the regular form.

Filtering activities by type in CRM views

In some scenarios you might want to wish to filter your activity views by activity types. While it sounds very trivial it can be a bit more complex in the context of solutions because activity type is stored as objecttypecode (OTC) which is different in each environment for custom activities.

 

Scenario

I defined a custom activity called “Follow-up” and I would like a view/sub-grid in the account form which shows me all the related Follow-ups to the account (I don’t want to see any other activity there). I also don’t want to create an additional relationship between Account and Follow-up but rather use the generic “Regarding” lookup that is used by all custom activities.

 

Solution #1

I can simply define or edit an existing view and use the field “Activity Type” (activitytypecode) to filter and show only the activity type that I desire:

After I do this I can use my view in a sub-grid and voila, very simple, I now have a sub-grid in the form which displays related activities but only those of type “Task” and “Follow-up”:

 

The problem with solution #1

The problem now is that when I transport this view and form to QA/Prod or to another CRM organization, it might break. The reason is that the view uses the activitytypecode field to filter on activity type. This field references an objecttypecode (OTC) which is a unique number given to each entity. For system entities (task, accounts, etc) the OTC is the same in all environments. However, for custom entities, new OTCs are assigned incrementally so there is no guarantee that they are consistent across environments. In my dev environment, this is what my view definition looks like in the exported solution:

While I can be sure that 4212 is always “task” in all environments, I cannot be sure that 10007 is my custom activity “Follow-up'” in all environments. In fact, an entity with OTC 10007 might not even exist in other environments, even if my custom “Follow-up” entity does exist (but with a different OTC).

 

Solution #2

If I would like to avoid my solution referencing a hard-coded OTC, I can use linked-entity in my view as a work-around. However, I cannot edit the views in the view designer of the application because it does not support joins. I would need to export my solution and edit the view XML in the solution file and then import it back. I will update the XML of the view I defined above in order to retrieve all tasks and follow-ups but without using the activitytypecode attribute:

Note that there is no reference to any objecttypecode in the view definition above, but it will automatically filter to show only activities of type new_followup because of the inner join. In order to update the view XML you will need to include the Activity entity to a solution, export the solution and then look for your view in the customizations.xml file and update the view definition fetchxml.

The problem with solution #2

Although solution #2 will work in all environments and will not break even if you transport the solution to another organization (as long as the new_followup entity exists), there are some disadvantages of this approach: First, the view filter criteria cannot be edited using the application view designer (so you will always need to update the XML if changes are required). Second, this only works if you want to filter to display a single activity type. However, if your requirement is to filter the activities to show 2 or more activity types (e.g. Follow-ups and tasks) then you will not be able to do this solution and only then I’d recommend solution #3.

 

Solution #3

Thanks Jukka for suggesting this. Every activity has at least on activity party (at least the owner). Therefore, we can leverage the activity party relationship to display “all activities which have an activity party which has a parent activity of type X”. This is what the query would look like if I only want to display Follow-up activities for example:

The problem with solution #3

The only problem with this solution is that it only works if you want to display a single activity type. If for example you have multiple custom activities and you want a view to display only follow-up and task activities then this will not be possible. Note however that if you want to display only custom activities you can easily filter out the out-of-the-box activities using solution #1 with a filter of “Does Not Equal (task,appointment, etc).” and that will be fine to transport across organizations because OTC for out-of-the-box activities are always constant.

 

Solution #4

You might be disappointed but if you need to filter to display more than 1 activity type but not all activity types, and you are using custom activities in your filter, then the only other solution would be to stick to solution #1 (see above) and then create a tool that automatically remaps the objecttypecode in the views xml to replace it with the appropriate objecttypecode in the target environment. You could do so as a stand-alone tool or you can have a post-solution action which does this for you (using the solution configuration page web resource).

Join the CRM 2013 experts round table in Spanish

On October 31st the most distinguished CRM experts in the Hispanic community are getting together to discuss in a virtual round table the new features of Microsoft Dynamics CRM 2013 (“Orion”) and the challenges for migrating.

The following attendees are invited:

• Gus Gonzalez (MVP, Zero2Ten, moderador de la sesión)
• Damián Sinay (MVP, Remoting Coders)
• Ramón Tebar (MVP, MetroBank)
• Demian Raschkovan (MVP, Infoaván)
• Gonzalo Ruiz (MVP, Avanade)
• Pablo Peralta  (MVP, CRMGamified)
• Jimmy Larrauri (Microsoft)

 

Additionally, you can ask your own questions in advance! Please leave your questions here and let the experts do the best to answer* during the session.

* We will try our best to respond all questions during the discussion and the timeframe we have. Given the time limitations we cannot commit to answer all questions although we will try our best.

Don’t miss out this opportunity, even if is just to practice your Spanish (even my Spanish is a bit rusty)! Thanks to Gus and Pablo for being the champions organizing this event!

Register for the event here.

Availability of “Add New” and “Add Existing” buttons in sub-grids

When you have a subgrid in CRM, you will notice there is an “Add New” and an “Add Existing” button. But the question is: Why sometimes only one of the two buttons is visible while some others both are visible?

The answer is actually not very straight forward and it depends on multiple factors:

1. Is it a 1:N or N:N relationship?

This determines the display rules of the given buttons.

 

2. On which entity?

Some out of the box entities have different display rules than others.

 

3. Is it the sub-grid of a custom entity?

Custom entities should all have the same display rules.

 

4. Is the parent lookup a required field in the child records?

This affects the visibility of these buttons. For example, if you have an 1:N relationship between Airports and Terminals then the visibility of the “Add Existing” button for the Terminals sub-grid on the Airport form depends on whether “Airport” is a required field for the Terminal or not.

 

This table summarizes the availability of the “Add New” and “Add Existing” buttons:

 

Relationship Type	Is the parent required on the child (lookup field is marked as required)	“Add New” button visible?	“Add Existing” button visible?
1:N	Yes	Yes	Yes*
1:N	No	Yes	No
N:N	N/A	No	Yes

 

* Not available on some Out-of-the-box entities

 

 

You can always modify the default behaviour by editing the ribbon and updating the display rules on the sub-grid buttons of the entity you would like to change. Before you do that, here is some useful information you might need:

When you look at the sub-grid ribbon you might find that there are two “Add Existing” buttons defined (this is true for most system entities and all custom entities): “Add Existing Standard” and “Add Existing Associated”:

So what’s the difference? It is misleading to believe that “Standard” is the button shown when there is an actual sub-grid inserted in the main form and “Associated” is the one visible in the associated view when using the form navigation (associated view) instead of a sub-grid. In fact, that’s not it at all. The actual difference is that “Standard” is the button that applies to 1:N relationships and “Associated” is the button that applies to N:N relationships. Therefore, depending on the type of relationship for your entity you will need to modify one button or the other. Note that some entities (e.g. contact) do not have an “Add Existing Standard” button so by default the “Add Existing” button is only available to N:N relationships.

To make your life simpler, I suggest using the visual ribbon editor which will show you all the buttons available in all entities as well as the display rules that apply.

Enterprise Data Integration Strategies with CRM 2011

In case you missed the excellent presentation by Daniel Cai in XrmVirtual about the different data integration strategies for CRM, you can view the recording here.

Data integration is a very common scenario in enterprise CRM implementations. Therefore it is important to understand what the options are and what the advantages and disadvantages are. Here is a small extract from Daniel’s presentation about 4 different data integration options:

 

1. CRM 2011 Import Data Wizard

What’s it?

• Free utilities offered by the platform

Pros

• Works for simple and small data import scenarios
  
• Works within application, available for CRM users for self-served data
• imports
  
• Undoable
      works well for new insert, but not update though
  
• Free

Cons

• No delete
       don’t confuse with the above undo capability
  
• No transformation
  
• No scheduling, no automation
  
• Difficult to manage incremental changes
  
• Maximum number of records is constrained by file size
  
• Limited capability of handling relationship
  
• Exceptions to be expected when used for some special entities, fields

 

2. Custom Integration Development

How does it work?

• Write custom code against CRM web service interfaces using SDK or service references

Pros

• Leverage your .NET programming (C# or VB.NET) skills
  
• More granular control
  
• Flexible integration points
      Plugins
      Workflows
      Standalone applications (Console, Windows Form, and probably third-party apps)

Cons

• Could be an exhaustive effort, particularly when infrastructure coding is involved
      Scheduling
      Threading
      Intricacies of working with CRM web service interfaces
• Most likely much higher maintenance cost down the road
• Often the case, the implementation ends up with a tightly-coupled architecture style, which leads to poor maintainability

3. ETL Tools

What’s ETL

• Extract, Transform, Load

Pros

• Development Productivity
  
• Visual development environment for data flows and control flow tasks
  
• Scheduling engine
  
• Performance
  
• Scalability
  
• Extensibility
  
• Can be part of your overall BI and data warehousing strategies

Cons

• Learning curve of the ETL tool
  
• Extra license cost of the ETL tool and/or the adapters
  
• Probably not ideal solution for real-time requirements

Options

• SSIS (SQL Server Integration Services)
  
• Informatica
  
• Scribe
  
• Connectors for Microsoft Dynamics
  
• …

 

4. Service Bus / BizTalk

What’s service bus?

• A software architecture model used for designing and implementing the interaction and communication between mutually interacting software applications in service-oriented architecture (SOA)

Pros

• Messaging based approach
       Optimized to move single transactions between different systems or processes in near real time or real time
• Decoupled integration architectural model
       Best suited for decoupled heterogeneous systems by using a middleware

Cons

• Probably not best fit with large volume data load
• Performance overhead due to serialization and deserialization
  

 

Thanks to Daniel for sharing. I have also provided a Spanish translation of this post here.