FetchXML link entity including null values

Question

I'm trying to query CRM using FetchXML. Here is the query to account entity, this returns me 10 records, here new_primaryactivityname field has NULL for few records.

<fetch mapping="logical" version="1.0">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="new_primaryactivity" />
  </entity>
</fetch>

Now i want to link it with another entity to get a new field, i want to do LEFT OUTER JOIN like in sql, to keep the NULL values while linking, so i provided link type as outer and i applied a filter for few conditions.

<fetch mapping="logical" version="1.0">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="new_primaryactivity" />
    <link-entity name="stringmap" from="attributevalue" to="new_primaryactivity" alias="new_primaryactivity_lookup_name" link-type="outer">
       <attribute name="value" />
    </link-entity>
    <filter type="and">
        <condition entityname="new_primaryactivity_lookup_name" attribute="attributename" operator="eq" value="new_primaryactivity" />
        <condition entityname="new_primaryactivity_lookup_name" attribute="objecttypecode" operator="eq" value="999" />            
    </filter>
  </entity>
</fetch>

The result(less than 10 records) ignores the records with new_primaryactivityname as NULL, what am i missing in FetchXML query?

I do not have much access to to system other than use FetchXML, i referred few suggestions to use tool in help building FetchXML query but i can't do it due to restrictions.

Answer 1

I think it's the location of your filter block.

The way it is written now, you're linking account and stringmap together with the outer-join, and then you're applying the filter to the results.

I think you need to do the filter inside the outer-join like so:

<fetch mapping="logical" version="1.0">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="new_primaryactivity" />
    <link-entity name="stringmap" from="attributevalue" to="new_primaryactivity" alias="new_primaryactivity_lookup_name" link-type="outer">
       <attribute name="value" />
       <filter type="and">
          <condition attribute="attributename" operator="eq" value="new_primaryactivity" />
          <condition attribute="objecttypecode" operator="eq" value="999" />            
       </filter>
    </link-entity>
  </entity>
</fetch>

Alternatively you could try to write the filter differently and explicitly allow for the null values

<fetch mapping="logical" version="1.0">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="new_primaryactivity" />
    <link-entity name="stringmap" from="attributevalue" to="new_primaryactivity" alias="new_primaryactivity_lookup_name" link-type="outer">
       <attribute name="value" />
    </link-entity>
    <filter type="or">
      <condition entityname="new_primaryactivity_lookup_name" attribute="attributename" operator="eq" value="" />
      <filter type="and">
        <condition entityname="new_primaryactivity_lookup_name" attribute="attributename" operator="eq" value="new_primaryactivity" />
        <condition entityname="new_primaryactivity_lookup_name" attribute="objecttypecode" operator="eq" value="999" />            
      </filter>
    </filter>
  </entity>
</fetch>

I haven't tested either of these, but hopefully one of these works for you