Support Portal ContactGet in touch

Reporting using Link Entities in CRM

   Words by CRM Consultancy

   on 27/09/2016 09:30:00

Often when we want to query CRM we want to loop in another entity – this is particularly true when building reports or dashboards where data often needs to be pulled from multiple locations, and is common when working with relational databases.

As an example, when working with Sales Opportunities we may also want to retrieve the Customer Account associated with that Opportunity – so then include Fields from the Customer Account, or base the condition on details in the Customer Account.

In SQL terminology this is defined as a JOIN.

In CRM terminology, this is defined as a Link Entity – where we link our ‘starting’ entity to another entity as part of the Query we run past CRM.

image

Opportunity Link Entity to Account

For the Dashboard, linking the ‘opportunity’ entity to the ‘account’ entity via a particular relationship field – in this case the ‘customerid’ field.  This type of field that links entities together is defined as a Lookup field in CRM.

Therefore we can structure our code to JOIN the two entities on this field using a Link Entity in our Code Behind:

Code-Behind

QueryExpression q = new QueryExpression("opportunity");

LinkEntity joinToAccount = new LinkEntity("opportunity", "account", "crmcs_customer", "accountid", JoinOperator.Inner);

joinToAccount.EntityAlias = "A";

joinToAccount.Columns = new ColumnSet("accountid", "name", "crmcs_supporter",

    "crmcs_dealerbuyinggroup", "crmcs_brilliantpartner");

q.LinkEntities.Add(joinToAccount);

We then take this Query Expression and pass this into our Data Table:

Code-Behind

// the ActivitySource will then combine the Markup Conditions alongside this Query Expression

opportunityList.ActivityDefinition.ActivitySource("Opportunity Report").FixedQuery = q;

// the ActivitySource will also build the Columns from the Markup (as FixedColumnSet is 'false')

opportunityList.ActivityDefinition.ActivitySource("Opportunity Report").FixedColumnSet = false;

// DataBind then builds the Data Set

opportunityList.DataBind();

This, as the comments suggest, merges the Conditions in our List Control Markup with the Joins+Conditions defined in our Code Behind.

What can we do with this?

Well, this allows us to refer to Columns on the ‘account’ entity as well as the ‘opportunity’ entity:

Mark-up - Column Definition

<crmcs:ListDefinitionColumn DisplayName="Supporter" Type="OptionSet" Visible="false" WidthType="Fixed" Width="80"></crmcs:ListDefinitionColumn>

<crmcs:ListDefinitionColumn DisplayName="Dealer Group" Type="String" Visible="false" WidthType="Fixed" Width="80"></crmcs:ListDefinitionColumn>

<crmcs:ListDefinitionColumn DisplayName="Brilliant Partner" Type="OptionSet" WidthType="Fixed" Width="80"></crmcs:ListDefinitionColumn>

Mark-up - Read From Definition

<crmcs:ReadfromColumn ActivityColumn="Supporter" DatabaseName="A.crmcs_supporter" Type="OptionSet" />

<crmcs:ReadfromColumn ActivityColumn="Dealer Group" DatabaseName="A.crmcs_dealerbuyinggroup" Type="String" />

<crmcs:ReadfromColumn ActivityColumn="Brilliant Partner" DatabaseName="A.crmcs_brilliantpartner" Type="OptionSet" />

This allows us to build our Data Table from fields from both the Opportunity and Customer Account Entities.

clip_image002[6]

So the Brilliant Partner Column here is from the ‘account’ entity, whereas the other columns are from the standard ‘opportunity’ entity.

Alias

As you can see from the Markup above, we refer to these fieldnames using an Alias of A.xxxxx

This is defined in our JOIN or LinkEntity, we supply an Alias that allows us to separate how we address a field from the ‘account’ entity vs addressing a field from the starting ‘opportunity’ entity.

This concept of aliasing when multiple Entities or Tables are involved in a query is a common concept in SQL and CRM.

Charts

Where our Framework takes this a bit further – is that we can then take this and immediately involve the new Columns into our Totals and Charts.

Mark-up

<crmcs:ListDefinitionColumn DisplayName="Supporter" Type="OptionSet" Visible="false" WidthType="Fixed" Width="80">

    <GroupingRanges>

        <crmcs:GroupingRange DateColumn="Est. Close" TotalColumn="Actual" DateGrouping="ByMonth" Categorise="true" AddOverRange="false" DateGroupingIncludeBlanks="false" TotalType="SUM" />

        <crmcs:GroupingRange DateColumn="Est. Close" TotalColumn="Estimate" DateGrouping="ByMonth" Categorise="true" AddOverRange="false" DateGroupingIncludeBlanks="false" TotalType="SUM" />

        <crmcs:GroupingRange DateColumn="Est. Close" TotalColumn="Estimate" DateGrouping="ByMonth" Categorise="true" AddOverRange="false" DateGroupingIncludeBlanks="false" TotalType="COUNT" />

    </GroupingRanges>

</crmcs:ListDefinitionColumn>

This will then immediately get picked up by our Split by Selector Logic to allow the Chart to be divided by the [Supporter] field, with the difference been that we are dividing our Opportunities by a field on the associated Account instead of a field on the Opportunity.

clip_image004[6]

So we sub-divide by the field on the related entity – in this case, allowing fields on the Account Entity to be included in the logic of the Chart.

Conditions

In addition to integrating new fields into our Query, we can also add Conditions to our Link Entity as way of filtering out Opportunities where the related Account does not meet the Condition.

This allows to build conditions such as ‘only show Opportunities for selected X Account Owners’ – where the Account Owner is the owner of the related Account, rather than the Opportunity record itself.

This can be done by adding a Condition to the Link Entity rather than the Query Expression:

Code Behind

QueryExpression q = new QueryExpression("opportunity");

LinkEntity joinToAccount = new LinkEntity("opportunity", "account", "crmcs_customer", "accountid", JoinOperator.Inner);

joinToAccount.EntityAlias = "A";

joinToAccount.Columns = new ColumnSet("accountid", "name", "crmcs_supporter",

    "crmcs_dealerbuyinggroup", "crmcs_brilliantpartner");

 

FilterExpression f = new FilterExpression();

f.FilterOperator = LogicalOperator.And;

f.AddCondition("ownerid", ConditionOperator.Equal,

    // specify the Parameter passed into our Dashboard Area

    opportunityList.ActivityDefinition.Parameter("userSelection").ParamValue);

joinToAccount.LinkCriteria = f;

This Condition could use one of our Parameters, or a fixed value.

This then allows the User to filter the Dashboard by the Owner of the Customer Account – which can often be required vs the Owner of the Opportunity.

clip_image006[6]

This ability to use a Link Entity style JOIN to pull in other Entities, either into the set of Columns or the Condition of which records form the Data Set, is then a powerful tool for Reporting on Lists and Charts.

Further Reading

MSDN – Link Entity Class

https://msdn.microsoft.com/en-us/library/microsoft.xrm.sdk.query.linkentity.aspx 

Dynamic CRM Query Expressions and Link Entities

https://www.crmcs.co.uk/content/dynamic-crm-query-expressions-and-link-entities.aspx 

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

CRMCS Quick Start Guide: How To Produce a Microsoft Teams Live Event Dynamics 365 Marketing: Lead Scoring and Sales Acceptance Designing and Developing Microsoft Power Apps Portals Thank You for Attending CRMCS’ Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Thank You for Attending Our Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Webinar: Discover How CRMCS Have United Dynamics 365, SharePoint and Microsoft Teams To Create Sales Excellence Ignite your workflow by adding DocDrive365 to Office 365 The CRMCS guide to everything you need to know about integrating Teams with Dynamics 365 Saving Time By Keeping Documents In One Place TDE Database Encryption with On Premise Dynamics The Key to Successful Compliance in 2020 Part 2: Let’s get GDPR Compliant with Microsoft Power Automate Top 3 Essential Tips for Remote Working Dynamics 365 Marketing: Top 5 Best Features Dynamics Day in the Life - Puma Investments Can you use Teams to amplify collaboration in Dynamics? Part 1: Using a Scheduled Power Automate to Trigger Expiry Date Reminders The secrets of successful document collaboration in Dynamics CRMCS launches new AppSource approved DocDrive365 Dynamics Day in the Life - Moneypenny Release Management Add the App to Dynamics DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Building a New Scheduled Process using Flow
  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.