RSMUS.comAnyone who develops reports in Microsoft Dynamics CRM Online knows that the data sets have to be written in FetchXML; and anyone who has tried to write a complicated report using FetchXML knows that there are some limitations to FetchXML. When I started off writing FetchXML, I utilized tools like FetchXML builder in the XRM ToolBox and as I became more comfortable, I started using Advanced Find to get the framework of my FetchXML then modify it myself.
Both the XRM ToolBox and Advanced Find allow you to create link entities. Link entities created by these tools always utilize a relationship that exists in Dynamics CRM. For example you could fetch Contacts then link to Accounts through the Company Name field, which would return all contacts and their account. Because all of the tools that I used to create FetchXML only utilized relationships to create link entities, I incorrectly assumed that FetchXML needs to link through a relationship that exists in the system, but it does not.
In this first example, I have three entities. The entities are cleverly named Entity 1, Entity 2 and Entity 3, but they could easily be the Account, Quote and Contact entity. The FetchXML below returns all Entity 1 records and links to Entity 2, where Entity 1’s lookup to Entity 3 and Entity 2’s lookup to Entity 3 lookup to the same Entity 3 record. To put that in a real world example, you could return an Account and link to all Quotes where the customer on the Quote is the Primary Contact for the Account.
Fetch Entity 1, link to Entity 2, where they both have a lookup to the same Entity 3 record:
In this example, I have two entities:Entity 1 and Entity 2 (Entity 1 and Entity 3 have no relationship to each other). A real world example of this could be a situation where there are transactions related to an opportunity/quote/order/invoice and those transactions are also tracked to a specific opportunity/quote/order/invoice line item. As you may know the line item entities prohibit custom relationships. To get around this you could add a text field from your custom transaction entity, and a text field to the line item and set them to the same value. To facilitate the real word example, Entity 1 could be Invoice Product, Entity 2 would be your custom Transaction Entity, and new_name in the link line would be the text field which is in common for the two entities
Fetch Entity 1, link to Entity 2, where they both have the same value in a text field:
To learn more about getting the most out of your Microsoft Dynamics CRM solution, contact our experts at RSM 855-437-7202
by Steve Trefz for RSM
