FetchXML Builder - N:N "Not In" Query

(0) Share

Report

Posted on by MarkBates

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:

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!

Categories:

Development / Customization / SDK

Sales

All responses (5)

Answers (0)

Suggested answer

Joergen 455 on at

Like (0)

Report

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 12 on at

Like (0)

Report

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 455 on at

Like (0)

Report

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

FetchXMLBuilder:

<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 12 on at

Like (0)

Report

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 1,012 on at

Like (0)

Report

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.

Thanks,
Manoj.

Please mark this as VERIFIED if it helps.