FetchXML ordering results and pagination based on linked entity's attributes

Question

I am writing a FetchXML query to retrieve a list of persons to be displayed in a table. This table should be able to provide search, pagination and sorting on all columns. The problem here is that the person entity has the father as a link-entity, and the father's "firstName" is already a column in the table that I am currently building. I need a query that gives me the ability to retrieve the results sorted by the father's "firstName", filtering the results based on the same attribute and applying pagination (skip and take) not having to get all the rows and applying filtering afterwards. What I tried so far is every possible thing derived from the method mentioned in this post: https://nishantrana.me/2012/04/27/sorting-on-link-entitys-attribute-in-fetch-xml-crm-2011/

Unfortunately, not working. Any suggestions?

Where are you displaying this table? Web, winform, xamarin...? The solution is most likely dependent on the platform where you are trying to accomplish this. — Jonas Rapp
Though I don't find anything related to the platform on which I should be displaying the table, anyway, it's an Angular 5 app. — Eyad Haddad
Maybe I should be providing a bit of more detail. There is a C# based REST API that provides an Angular 5 web app with data. — Eyad Haddad

Jonas Rapp Jonas Rapp · Accepted Answer · 2018-05-21T11:57:27

I'm guessing you have a query like this (though this is simplified):

<fetch count='50' returntotalrecordcount='true' page='2' >
  <entity name='contact' >
    <attribute name='firstname' />
    <attribute name='lastname' />
    <filter>
      <condition entityname='father' attribute='firstname' operator='like' value='Jonas%' />
    </filter>
    <link-entity name='contact' from='parentcustomerid' to='contactid' alias='father' >
      <attribute name='firstname' />
      <order attribute='firstname' />
    </link-entity>
  </entity>
</fetch>

The filter condition can be applied as above using the alias of the link-entity to father, or by adding a filter/condition under the link-entity element.

The order element added under link-entity in this example is valid to specify, however it does not seem to have any effect.

So to be able to sort by information on link-entities you have to do it client side.

FetchXML ordering results and pagination based on linked entity's attributes

1 Answers