Few days back, I was asked to write FetchXML that would give a list of incidents, which were shared with a team.
The result should contain incident details along with the names of the teams with which the incident was shared.
The result was ought to have 4 fields,
- title
- createdon
- incident
- team
I wrote the below mentioned FetchXML for this requirement.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="incident" >
<attribute name="title" />
<attribute name="createdon" alias="createdon" />
<attribute name="incidentid" />
<link-entity name="principalobjectaccess" to="incidentid" from="objectid" link-type="inner" alias="share" >
<filter type="and" >
<condition attribute="accessrightsmask" operator="ne" value="0" />
<condition attribute="principalid" operator="not-null" />
</filter>
<link-entity name="team" from="teamid" to="principalobjectaccessid" link-type="outer" alias="team" >
</link-entity>
</link-entity>
</entity>
</fetch>
The equivalent T-SQL for this will be,
SELECT
i.title,
i.createdon as createdon,
i.incidentid,
t.team as teamname
FROM incident i
INNER JOIN principalobjectacess p ON i.incidentid = p.objectid AND accessrightsmask <> 0
INNER JOIN team t ON t.teamid = p.principalobjectaccessid.
Please take a look at following link for “principalobjectaccess”, if you are not familiar with it.
The problem
With this fetchxml, somehow, the result did not include the field of ‘team’ entity. But why?
I took help of XRMToolBox (http://www.xrmtoolbox.com/) to find the reason for the issue and tried executing the FetchXML on a CRM instance whose database was accessible to me.
I executed SQL queries to review the data and found that there was no team related to any of the incidents.
Hence, I shared an incident with a team and analyzed the data in the principalobjectaccess table.
With this change I was able to see the expected data in the SQL query.
And while observing the data, I found that the field ‘principalobjectaccessid’ that I used in to field was incorrect and I needed to use the field ‘principalid’ for join purposes.
Thus, based on the working with SQL query, I modified my fetchxml with the correct field value.
Along with this, I also removed the alias “teamname”.
I made these changes and tested it again. This time it gave me the correct results. The following is the modified FetchXML.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="incident" >
<attribute name="title" />
<attribute name="createdon" alias="createdon" />
<attribute name="incidentid" />
<link-entity name="principalobjectaccess" to="incidentid" from="objectid" link-type="inner" alias="share" >
<filter type="and" >
<condition attribute="accessrightsmask" operator="ne" value="0" />
<condition attribute="principalid" operator="not-null" />
</filter>
<attribute name="name" />
</link-entity>
</link-entity>
</entity>
</fetch>
Results were,
<result>
<title>
MyTitle
</title>
<createdon date="09/01/2016" time="4:00 PM" >
2016-09-01T16:00:00+05:30
</createdon>
<incidentid>
{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}
</incidentid>
<team.name>
XXXX
</team.name>
</result>
- The fields mentioned in FetchXML linked entity will only show up in the results if a linked entity record is actually present, otherwise the FetchXMl will simply ignore the fields.
- If we want an attribute of linked entity in the results, we should not give alias to it. Like, in aforesaid fetchxml, we want team name in the output, so I have not given an alias to it.
- Whenever we link one entity to another in Fetchxml, we should always keep in mind that the from field comes from the linked entity and to field comes from the main entity. Like, in aforesaid fetchxml, the from field has to be from the team entity.
<link-entity name="team" from="teamid" to="principalid" link-type="outer" >
Hope this helps!
A simple and quick way to insert data into Many-to-Many relationship in Dynamics CRM is the Drag and drop listbox. http://www.inkeysolutions.com/DynamicCRMDragAndDropListBox.html
Second point needs to corrected. You can give alias name. It will work.
ReplyDeleteManiraj, If we want an attribute of linked entity in the results, we should not give alias to it. Can you validate it for the linked entity's attribute?
Delete