top row only in a link-entity? RRS feed

  • Question

  • How can I create a fetchXML query which will retrieve only the top row from a linked-entity?

    I have a situation in which I want a report which shows cases and the last action carried out on a case, ie in SQL it would be something like;

    SELECT stuff FROM cases
    LEFT JOIN (SELECT TOP 1 morestuff FROM casehistories ON ...)

    I've found the count property in FetchXML but it seems to be ignored in linked entities, so the report gets multiple duplicate rows. How can I get just one row?

    Wednesday, June 4, 2014 3:56 PM

All replies

  • Unfortunately I don't think this is possible with FetchXML. A possible workaround would be to create additional fields on the case entity, and populate them (using a workflow or plugin) from the child entity, such that they would show the values from the most recently updated (or created, depending on the event you use to trigger the workflow/plugin)

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Wednesday, June 4, 2014 5:27 PM
  • Hmm, considered that. I can make an ad hoc change to cover this requirement but unfortunately it means that although the database has all the data I might need I need to anticipate now all possible future reporting requirements because FetchXML isn't capable of retrieving it.

    FetchXML is really rubbish isn't it ;-)

    Thursday, June 5, 2014 9:07 AM