viewdynamics-crmdynamics-365fetchxml

I need to create a fetchxml retrieving cases where department is opening user's (user profile->) department


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.


Solution

  • 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>