MS CRM 2011 Fetch XML Query

Question

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.

Was Daryl spot on or do you intend to achieve something else? Please explain in plain text, if possible. And SQL formalization is almost always a great idea. — Konrad Viltersten
Hi, Daryl made a good start but I need to see results where one of the relationships exist. At the moment with Daryl's answer it also brings back results where both link entities are not applicable. — Simon
OK SQL added to help understand the issue. I've executed this SQL and it returns the desired results. Thanks for your help. — Simon
I have to admit that I'm very impatient. If I don't get the query expression or fetch XML to do what I want fairly immediately, I go shotgun - get all records (more or less) and then LINQ'em. Not the most optimal solution but a solution. :) — Konrad Viltersten
Hi Konrad, I'm trying to modify QueryExpresion in the plugin context for a retrieve multiple when a user looks at Activity History, it needs to include emails with connections. I'm loathed to get all results and parse in memory as I then have to handle the original filtering, sorting, paging and counting, that all feels like too much work. — Simon

Daryl Daryl · Accepted Answer · 2013-02-27T00:42:51

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.

MS CRM 2011 Fetch XML Query

1 Answers

Edit 1