Announcements
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!
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
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.
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
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.
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.
André Arnaud de Cal... 291,359 Super User 2024 Season 2
Martin Dráb 230,370 Most Valuable Professional
nmaenpaa 101,156