CODEWITHSUNDEEP
dynamics-crm-2011fetchxml
Simon
Simon

Reputation: 609

MS CRM 2011 Fetch XML Query

I'm trying to create some fetch xml or a query expression for CRM 2011.

I would like to OR the two linked entity nodes below. Is this possible at all, I need to do it in one request.

If I can perform this query I intend to modify the Activity History RetrieveMultiple views events by injecting extra criteria, similar to below. 

<fetch  mapping='logical' distinct='true'>
  <entity name='activitypointer'>
    <attribute name='activitytypecode' />
    <attribute name='subject' />
    <attribute name='statecode' />
    <attribute name='prioritycode' />
    <attribute name='modifiedon' />
    <attribute name='activityid' />
    <attribute name='instancetypecode' />
    <order attribute='modifiedon' descending='false' />
    <filter type='and'>
      <condition attribute='statecode' operator='eq' value='1' />
    </filter>
    <link-entity name='activityparty' from='activityid' to='activityid' alias='aa'>
      <link-entity name='account' from='accountid' to='partyid' alias='ab'>
        <filter type='or'>
          <condition attribute='accountid' operator='eq' uiname='A new Trust' uitype='account' value='{756CE4E9-F6F0-E111-8948-000C297B9BDA}' />
        </filter>
      </link-entity>
    </link-entity>
    <link-entity name='connection' from='record2id' to='activityid' alias='ad'>
      <link-entity name='account' from='accountid' to='record1id' alias='ae'>
        <filter type='or'>
          <condition attribute='accountid' operator='eq' uiname='A new Trust' uitype='account' value='{756CE4E9-F6F0-E111-8948-000C297B9BDA}' />
        </filter>
      </link-entity>
    </link-entity>
   </filter>
  </entity>
</fetch>

Here's the SQL that brings back the results I'm looking for, please note that the issue issue is recreating the fetchXml for the where clause, specifically checking that one of the joins exist.

SELECT DISTINCT
    ap.activitytypecode,
    ap.[subject],
    ap.statecode,
    ap.prioritycode,
    ap.modifiedon,
    ap.activityid,
    ap.instancetypecode

FROM 

dbo.FilteredActivityPointer ap

--First Link
LEFT OUTER JOIN dbo.FilteredActivityParty party
ON ap.activityid = party.activityid
AND party.partyid = '756CE4E9-F6F0-E111-8948-000C297B9BDA'

--Second Link
LEFT OUTER JOIN dbo.FilteredConnection connection
ON ap.activityid = connection.record2id
AND connection.record1id = '756CE4E9-F6F0-E111-8948-000C297B9BDA'

WHERE
    ap.statecode =1

    AND (
        NOT party.partyid IS NULL
     OR NOT connection.record1id IS NULL
    )
ORDER BY 
    ap.modifiedon

Please help.

Upvotes: 1

Views: 7962

Answers (1)

Daryl
Daryl

Reputation: 18895

If I'm way off the mark, maybe you can write the basic SQL statement that you're looking to achieve. But I think you're wanting to return all Activity Pointers where a particular account is a connection or activity party? If so, then you'll need to change your links to be outer links.

Try adding the link-type='outer' attribute and value to your link entities like so:

<fetch  mapping='logical' distinct='true'>
  <entity name='activitypointer'>
    <attribute name='activitytypecode' />
    <attribute name='subject' />
    <attribute name='statecode' />
    <attribute name='prioritycode' />
    <attribute name='modifiedon' />
    <attribute name='activityid' />
    <attribute name='instancetypecode' />
    <order attribute='modifiedon' descending='false' />
    <filter type='and'>
      <condition attribute='statecode' operator='eq' value='1' />
    </filter>
    <link-entity name='activityparty' from='activityid' to='activityid' alias='aa' link-type='outer'>
      <link-entity name='account' from='accountid' to='partyid' alias='ab' link-type='outer'>
        <filter type='or'>
          <condition attribute='accountid' operator='eq' uiname='A new Trust' uitype='account' value='{756CE4E9-F6F0-E111-8948-000C297B9BDA}' />
        </filter>
      </link-entity>
    </link-entity>
    <link-entity name='connection' from='record2id' to='activityid' alias='ad' link-type='outer'>
      <link-entity name='account' from='accountid' to='record2id' alias='ae' link-type='outer'>
        <filter type='or'>
          <condition attribute='accountid' operator='eq' uiname='A new Trust' uitype='account' value='{756CE4E9-F6F0-E111-8948-000C297B9BDA}' />
        </filter>
      </link-entity>
    </link-entity>
   </filter>
  </entity>
</fetch>

Edit 1

After looking at your SQL query, I believe what you're attempting to do is not supported in CRM in a single query, although right now I can't find any documentation to back up my hunch... I don't think you can do a count of the outer joined records then say, only return where the count of the first Link Entity + the count of the second Link Entity > 0, but I could be mistaken.

Upvotes: 1

Related Questions