0

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.

Dave Hoyt
  • 37
  • 7

1 Answers1

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.

Zach Mast
  • 1,698
  • 1
  • 10
  • 17
  • That looks like you are looking for an left outer join, CRM 2013 supports that. Because in the query he needs the activity: 1. Has opportunities associated 2. Has contacts associated on regarding If this is what he needs, in fetch XML is a bit different. – Sxntk Nov 19 '14 at 13:27
  • Right. We know how to do it with SQL but it looks like it is not possible with fetch. – Zach Mast Nov 19 '14 at 14:24
  • I am thinking something like: `` `` `` `` I'm not sure what he wants but he may manipulate the fecth to get what he needs. – Sxntk Nov 19 '14 at 15:09
  • I think I may have already tried ^ that, but I'll give it another shot later today and tell ya what's up. Thanks for the input guys! – Dave Hoyt Nov 19 '14 at 15:34