How to implement custom FetchXML with aggregate as a System View in Dynamics365

Question

I have a custom entity called course enrolment which has a lookup to the contact entity.
I want to a view of contacts that have an active course enrolment.

I don't believe that this can be done from the contact entity, so I have to create a view of active course enrolments which only displays the contact fields.

Doing this will give me duplicate contacts if they have more than one course enrolment which I do not want.

To avoid this, I tried creating an aggregate query in FetchXML:

<fetch version="1.0" aggregate="true" >
  <entity name="flr_courseenrolment" >
    <link-entity name="contact" from="contactid" to="flr_contact" alias="a_69c0f6b80d94e711812ae0071b66a541" >
      <attribute name="emailaddress1" alias="contactemailaddress1" groupby="true" />
      <attribute name="fullname" alias="contactfullname" groupby="true" />
      <filter type="and" >
        <condition attribute="statecode" operator="eq" value="0" />
        <condition attribute="flr_islearner" operator="eq" value="1" />
        <condition attribute="flr_advisor" operator="eq" uiname="Test User" uitype="systemuser" value="{28C0D53B-5E19-E611-8106-C5346CC262D1}" />
      </filter>
    </link-entity>
    <attribute name="flr_courseenrolmentid" alias="aggregateflr_enrolmentid" groupby="true" />
  </entity>
</fetch>

This query works in the XRM Toolbox FetchXML Tester. But will not work when I import it into CRM.

I am importing it into CRM by exporting a solution containing a view that contains the same fields; and replacing the FetchXML that was generated by the system by the FetchXML that I created, and re-importing it.

I receive a generic CRM Error when trying to view this view after importing it again which says that "Columns should always be specified on Retrieve Multiple."

<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
  <ActivityId>b3b1d783-a8be-4454-9ed5-b71c42053299</ActivityId>
  <ErrorCode>-2147220970</ErrorCode>
  <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
  <Message>System.Xml.XmlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #2955ADDE</Message>
  <Timestamp>2017-09-11T03:22:55.50674Z</Timestamp>
  <ExceptionRetriable>false</ExceptionRetriable>
  <ExceptionSource i:nil="true" />
  <InnerFault>
    <ActivityId>b3b1d783-a8be-4454-9ed5-b71c42053299</ActivityId>
    <ErrorCode>-2147220970</ErrorCode>
    <ErrorDetails xmlns:d3p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
    <Message>Columns should always be specified on RetrieveMultiple.</Message>
    <Timestamp>2017-09-11T03:22:55.50674Z</Timestamp>
    <ExceptionRetriable>false</ExceptionRetriable>
    <ExceptionSource i:nil="true" />
    <InnerFault>
      <ActivityId>b3b1d783-a8be-4454-9ed5-b71c42053299</ActivityId>
      <ErrorCode>-2147220970</ErrorCode>
      <ErrorDetails xmlns:d4p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
      <Message>System.InvalidOperationException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #F043AB2E</Message>
      <Timestamp>2017-09-11T03:22:55.50674Z</Timestamp>
      <ExceptionRetriable>false</ExceptionRetriable>
      <ExceptionSource i:nil="true" />
      <InnerFault i:nil="true" />
      <OriginalException i:nil="true" />
      <TraceText i:nil="true" />
    </InnerFault>
    <OriginalException i:nil="true" />
    <TraceText i:nil="true" />
  </InnerFault>
  <OriginalException i:nil="true" />
  <TraceText i:nil="true" />
</OrganizationServiceFault>

I have a feeling that either:

• The alias on the flr_courseenrolmentid is causing the view to break
• Aggregate/groupby is not supported at all in CRM views
• I need to customise the LayoutXML in some way as well as the FetchXML

Is what I want to achieve possible? Is there a way to display this FetchXML in a view in CRM, or is there another way to achieve the view I need?

Answer 1

Create the view under Contact.

as conditions, Pick the related entity "Course Enrollment (Contact)" (i'm assuming the name here) and as conditions put "Contact contains data" and "Status equals active".

This is the same as fetching all accounts with active contacts.

Answer 2

You cannot use aggregate queries in views, unfortunately. It would probably help by adding distinct="true" to the fetch element, and not using aggregate.

Easiest way to test your different options is to use the FetchXML Builder plugin in XrmToolBox, from this you can open the view you would like to alter, change the query, and then save it back to CRM. No solution imports or manual customizations.xml editing necessary.