On D365 I have to create a view "Created By Users Department". On User and Case we have Department lookup.
<fetch>
<entity name="incident">
<attribute name="ticketnumber" />
<attribute name="title" />
<attribute name="department" />
<attribute name="prioritycode" />
<attribute name="caseorigincode" />
<attribute name="customerid" />
<attribute name="statuscode" />
<link-entity name="systemuser" from="systemuserid" to="ownerid" link-type="inner" alias="user">
<attribute name="department" />
<link-entity name="department" from="departmentid" to="department" alias="dept">
<attribute name="name" />
</link-entity>
</link-entity>
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="openedbydepartment" operator="eq" valueof="dept.dss_name" />
</filter>
<attribute name="ownerid" />
<attribute name="incidentid" />
</entity>
</fetch>
I tried this fetchxml where I am trying to get the "department" of current user and get the name of department from another like-entity name="department"
On case create I have a field openedbydepartment which get updated by the department(text) of created by user automatically.
I am trying to filter by openedbydepartment where openedbydepartment is equal to current logged in user's department.
the output is not accurate, it is not considering current user's dept and openedbydepartment.
I tried multiple link-entities but behavior is not consistent.
I fixed it using below joins
<link-entity name="systemuser" from="systemuserid" to="owninguser" link-type="inner" alias="user">
<attribute name="department" />
<link-entity name="department" from="departmentid" to="department" alias="dept">
<attribute name="name" />
</link-entity>
</link-entity>
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="openedbydepartment" operator="eq" valueof="dept.name" />
</filter>