FetchXML Builder – N:N “Not In” Query

0
48
Forum Post: Change the Filter On option on Opportunity Associated View for Account form


Hi Everyone.  Any knowledgeable FetchXML Builders willing to help this novice?  I have a N:N Relationship with Account Entity and a custom Keywords Entity.  I can find the Accounts that are “In” the related Keyword Entity just fine using Advanced Find.  Where I am struggling is finding the Accounts who are “Not In” a specific Keyword Entity Record. 

Here is the XML for the “In” records:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”>
<entity name=”account”>
<attribute name=”entityimage_url” />
<attribute name=”parentaccountid” />
<attribute name=”name” />
<attribute name=”primarycontactid” />
<attribute name=”accountid” />
<attribute name=”sechg_uiid” />
<attribute name=”sechg_taxreturn” />
<attribute name=”sechg_taxreturn” />
<order attribute=”name” descending=”false” />
<filter type=”and”>
<condition attribute=”sechg_relationshipclassification” operator=”in”>
<value>638460004</value>
<value>638460005</value>
<value>638460000</value>
</condition>
</filter>
<link-entity name=”sechg_sechg_keywords_account” from=”accountid” to=”accountid” visible=”false” intersect=”true”>
<link-entity name=”sechg_keywords” from=”sechg_keywordsid” to=”sechg_keywordsid” alias=”ae”>
<filter type=”and”>
<condition attribute=”sechg_keywordsid” operator=”eq” uiname=”Tax Client” uitype=”sechg_keywords” value=”{48E5F4D1-C631-EC11-B6E5-002248258F22}” />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>

Here is the XML for the “Not In” query that I thought would work but does not.  It returns all records that have match ANY of the Keyword Entity records:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="account">
<attribute name="entityimage_url" />
<attribute name="parentaccountid" />
<attribute name="name" />
<attribute name="primarycontactid" />
<attribute name="accountid" />
<attribute name="sechg_uiid" />
<attribute name="sechg_taxreturn" />
<attribute name="sechg_taxreturn" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="sechg_relationshipclassification" operator="in">
<value>638460004</value>
<value>638460005</value>
<value>638460000</value>
</condition>
<condition entityname="ae" attribute="sechg_keywordsid" operator="null" />
</filter>
<link-entity name="sechg_sechg_keywords_account" from="accountid" to="accountid" visible="false" intersect="true">
<link-entity name="sechg_keywords" from="sechg_keywordsid" to="sechg_keywordsid" link-type="outer" alias="ae">
<filter type="and">
<condition attribute="sechg_keywordsid" operator="eq" uiname="Tax Client" uitype="sechg_keywords" value="{48E5F4D1-C631-EC11-B6E5-002248258F22}" />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>

Thanks in advance for any suggestions!



Source link

Leave a reply

Please enter your comment!
Please enter your name here