Skip to main content

Notifications

Microsoft Dynamics CRM forum
Suggested answer

FetchXML Builder - N:N "Not In" Query

Posted on by 12

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!
  • Suggested answer
    Joergen Profile Picture
    Joergen 455 on at
    RE: FetchXML Builder - N:N "Not In" Query

    Hi Mark,

    based on the out of the box functionality I don't think that your query is supported as you would need to add the relationship twice to your FetchXML Query:

    First relationship with Filter "Does not Contain Data" OR

    Second relationship contains data but equals a specific value.

    Trying this in Advanced find, you will receive the error, that "The relationship you are adding already exists in the query."

    I recommend to read the following blog post by MVP Debajit Dutta: {Dynamics CRM} How to perform intersect queries using the same relationship twice in the in Dynamics CRM fetchxml. - Debajit's Power Apps & Dynamics 365 Blog (debajmecrm.com)

    Maybe this will help you to create the necessary view.

    If this is helpful for you, please mark the answer as verified.

    Best regards,

    Joergen

  • MarkBates Profile Picture
    MarkBates 12 on at
    RE: FetchXML Builder - N:N "Not In" Query

    Assume in your example that MultiValue has two Records “A” and “B” and that 2 Accounts are associated with “A” and 2 Accounts are associated “B” and 15 Accounts are not associated with either. The query I am looking for is a query that can be ran on Accounts to identify the Accounts that Are Not associated with “A”. In this example, it should return 17 of your 19 accounts.

  • Suggested answer
    Joergen Profile Picture
    Joergen 455 on at
    RE: FetchXML Builder - N:N "Not In" Query

    Hi Mark,

    If I understand your request correctly, you want to have a view which shows you all the accounts where no record of a specific table is related to - and the relationship between acount and the other table is N:N. Correct?

    So in my demo case the n:n related table is called MultiValue (pod_multivalue // pod_multivalue_account)

    I was able to achieve the view already with Advanced find but below you can also see the FetchXMLQuery:

    1. Select Account table
    2. Account Contains Data
    3. Related -> Multi Values Does Not Contain Data

    pastedimage1647708606141v1.png

    FetchXMLBuilder:
    pastedimage1647708805616v2.png

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="account">
    <attribute name="name" />
    <link-entity name="pod_multivalue_account" from="accountid" to="accountid" intersect="true" link-type="outer">
    <link-entity name="pod_multivalue" from="pod_multivalueid" to="pod_multivalueid" link-type="outer" alias="ah" />
    </link-entity>
    <filter type="and">
    <condition entityname="ah" attribute="pod_multivalueid" operator="null" />
    </filter>
    </entity>
    </fetch>

    Result of the above view: 
    In my demo environment I have 19 active accounts in total.

    The view above will show me as an result 14 of these accounts. All 14 does not have any related MultiValue record.

    When using "Multi Values" Contains data instead of "Does Not Contain Data" in the Advanced find, the view will show me the 5 accounts which have at least 1 MultiValue record attached. 

    If this answer has been helpful for you, please mark as verified.
    Best regards,

    Joergen

  • MarkBates Profile Picture
    MarkBates 12 on at
    RE: FetchXML Builder - N:N "Not In" Query

    Hi Manoj.  FetchXML Builder is actually what I used to build the attempted query with no luck.  The first query was built in D365 Advanced Find to get the "reverse" of what I am looking for.  The second query in my initial post was built with FetchXML Builder in my attempt to get the opposite of the first query.  I feel like I am close with the 2nd query but I am missing something.  Thanks for the suggestion.

  • Manoj Mane Profile Picture
    Manoj Mane 1,012 on at
    RE: FetchXML Builder - N:N "Not In" Query

    Hi MarkBates,

    Can you try your query in the FetchXML builder it is a tool in XRMToolBox.

    pastedimage1647631988814v1.png

    Thanks,
    Manoj.

    Please mark this as VERIFIED if it helps. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,524 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,493 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans