FetchXML Filters With Different Entities

Question

I wish to filter my entities in CRM2011 using a fetchXML filter. However, I'm having issues with AND and OR groupings over different entities.

I am searching for clients based on their consent, where each client will have either 1 or 0 valid consents. I want to return the client if there is no valid consent. I also want clients returned if they have a consent, but not if the have a 'restricted' consent without the agency specified (eg. client.consent.type == 'restricted' AND client.consent.users CONTAINS user)

So far I have this:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="thr_verifiedproofofidentity" />
    <attribute name="thr_interpreterrequired" />
    <attribute name="emailaddress1" />
    <attribute name="thr_consent" />
    <attribute name="birthdate" />
    <attribute name="thr_individualreferencenumber" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="thr_consent" operator="not-null" />
    </filter>
    <link-entity name="thr_consent" from="thr_clientid" to="contactid" alias="aa">
      <filter type="and">
        <filter type="or">
          <condition attribute="thr_consenttype" operator="eq" value="130120003" />
          <condition attribute="thr_consenttype" operator="ne" value="130120003" />   *
        </filter>
      </filter>
      <link-entity name="thr_thr_consent_thr_agency" from="thr_consentid" to="thr_consentid" visible="false" intersect="true">
        <link-entity name="thr_agency" from="thr_agencyid" to="thr_agencyid" alias="ab">
          <filter type="and">
            <condition attribute="thr_agencyid" operator="eq" uiname="Test" uitype="thr_agency" value="(agency id goes here)" />   *
          </filter>
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

The only thing missing from this code is that I need an AND grouping for the two condition attributes market with the '*'.

Can this be done?

you can create your query using advanced find first, and after download the fetchxml. In the advanced find form there are also options for OR, AND grouping — Guido Preite
This code was generated using the advanced find form, however the form doesn't allow grouping of different entities. I was at first trying to use QueryExpressions, but I couldn't get that working either. The rreason I want FetchXML or QueryExpressions is that the search uses generic code for a lot of entities, and the query needs to be entered in first before the generic search runs. — Irish Yobbo
What do you mean by "generic code for a lot of entities" and "the query needs to be ''entered'' in first before the generic code runs?" — Daryl

Daryl Daryl · Accepted Answer · 2013-04-10T12:44:34

As Guido Preite points out, it would be easiery to use the Advanced find to create the Fetch Xml, rather than hand editing it.

But...

Since you're not doing any grouping, I would suggest not even using Fetch Xml, but instead go with one of the other supported SDK options (QueryExpressions, Linq to CRM, oData, etc).

I'm not sure I completely understand your request (if you could write your query as a SQL statement, it would be helpful), but I think you'll need to have 2 thr_consent link entity of link-type="outer". The first has your equal to 130120003 condition, the second has your links to the agency id condition.

FetchXML Filters With Different Entities

1 Answers