Join/Filter syntax for activity pointers using fetchxml

Question

I have a requirement to pass in a fetch statement for a subgrid to use in CRM 2013. It needs to get the following:

All Activities regarding contacts who are connected to a contact through a connection. (This statement gets those)

<?xml version="1.0" encoding="UTF-8"?>
<fetch distinct="true" mapping="logical" output-format="xml-platform" version="1.0">
   <entity name="activitypointer">
      <attribute name="activitytypecode" />
      <attribute name="subject" />
      <attribute name="statecode" />
      <attribute name="modifiedon" />
      <attribute name="instancetypecode" />
      <attribute name="scheduledend" />
      <attribute name="actualend" />
      <attribute name="regardingobjectid" />
      <attribute name="activityid" />
      <order descending="false" attribute="modifiedon" />
      <link-entity name="contact" to="regardingobjectid" from="contactid">
         <link-entity name="connection" to="contactid" from="record1id">
            <filter type="and">
               <condition attribute="record2id" value="someID" uitype="contact" operator="eq" />
            </filter>
         </link-entity>
      </link-entity>
   </entity>
</fetch>

All activities regarding opportunities related to the contact. ((This statement gets those))

<?xml version="1.0" encoding="UTF-8"?>
<fetch distinct="false" mapping="logical" output-format="xml-platform" version="1.0">
   <entity name="activitypointer">
      <attribute name="activitytypecode" />
      <attribute name="subject" />
      <attribute name="statecode" />
      <attribute name="modifiedon" />
      <attribute name="instancetypecode" />
      <attribute name="scheduledend" />
      <attribute name="actualend" />
      <attribute name="regardingobjectid" />
      <attribute name="activityid" />
      <order descending="false" attribute="modifiedon" />
      <link-entity name="opportunity" to="regardingobjectid" from="opportunityid">
         <filter type="and">
            <condition attribute="customerid" value="someID" uitype="contact" operator="eq" />
         </filter>
      </link-entity>
   </entity>
</fetch>

And all activities regarding the contact itself.

---

I've tried a few different combinations of joins, but I'm not sure I'm getting their positioning or syntax correct. I unfortunately deleted those attempts after they all returned no results. Here is one of the last version I tried which also returned no results.

<?xml version="1.0" encoding="UTF-8"?>
<fetch distinct="false" mapping="logical" output-format="xml-platform" version="1.0">
   <entity name="activitypointer">
      <attribute name="activitytypecode" />
      <attribute name="subject" />
      <attribute name="statecode" />
      <attribute name="modifiedon" />
      <attribute name="instancetypecode" />
      <attribute name="scheduledend" />
      <attribute name="actualend" />
      <attribute name="regardingobjectid" />
      <attribute name="activityid" />
      <order descending="false" attribute="modifiedon" />
      <link-entity name="opportunity" to="regardingobjectid" from="opportunityid">
         <attribute name="name" />
      </link-entity>
      <link-entity name="contact" to="regardingobjectid" from="contactid">
         <link-entity name="connection" to="contactid" from="record1id">
            <attribute name="record1id" />
         </link-entity>
      </link-entity>
      <filter type="or">
         <condition entityname="connection" attribute="record2id" value="&quot; + Xrm.Page.data.entity.getId() + &quot;" uitype="contact" operator="eq" />
         <condition entityname="activitypointer" attribute="regardingobjectid" value="&quot; + Xrm.Page.data.entity.getId() + &quot;" uitype="contact" operator="eq" />
         <condition entityname="opportunity" attribute="customerid" value="&quot; + Xrm.Page.data.entity.getId() + &quot;" uitype="contact" operator="eq" />
      </filter>
   </entity>
</fetch>

If anyone can help me out with formatting I'd appreciate it.

Thanks in advance.

Answer 1

It appears to me that although you can now use an OR filter across tables in CRM 2013 (your syntax looks correct) the OR does not apply to the link itself. In SQL terms it is as if you did a full join between the tables and then put the OR logic within the WHERE statement. If any of the joins doesn't match you will return no results.

select *
from activitypointer
join opportunity on ..
join connection on...
join contact on...
WHERE (opportunity.regardingID = SomeID) OR (opportunity.customerid = SomeID) OR (activitypointer.regardingobjectid = SomeID)

In your bottom example your activity must linked BOTH to an opportunity AND to a contact through a connection and one OR the other must meet your criteria within the OR filter. Unfortunately I do not think you can write the fetch you desire but I am not 100% sure of this so if anyone can confirm or refute this please do. This link has a good example of what you can do using OR filters across tables.