Dynamics Community Forum Thread Details

FetchXML to retrieve last child record

I'm building a report for my CRM app and need to figure out how to write the FetchXML statement. I have two custom entities in a parent-child relationship. I need to select all fields from both parent and child entities but for the child entity I only want to retrieve the latest record. I have seen examples of how to achieve this using "groupby" and "aggregate" clauses but they only bring back the date column. How do I fetch all the columns in the child entity?

Hi,

It cannot be achieve by fetchXML only. "groupby" and "aggregate" can only show the column which has been set with "aggregate".

The "order" can be used to order your results by the id of the parent entity and the date of the child entity. But it cannot show the latest one only.

Here is an example to show the contacts of the accounts order by the account ids and the create date of contacts.

As I know that what you need is not just fetchXML but to build a report. I did some search about it and find a similar issue.

( ) How to get Top Row Only In A Link-Entity in FetchXML Report? - Microsoft Dynamics CRM Forum Community Forum

Here is the solution used by the poster.

reporting services - Auto numbering to parent and child columns - Stack Overflow

Or you can create a custom field in the parent entity and when a child record is updated, the field will be updated to a value that uniquely identifies this child record.
It is also recommended to try using other ways of generating reports than fetchXML.

If this helped you, I'd appreciate it if you'd mark this as a Verified Answer, which may in turn help others as well.

Best Regards,

Nya

Quick Links

Leaderboard