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?

Answer 1

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.