We have a requirement to create a view that will show if a specific account is part of marketing lists associated with an entity. In the entity, there are 3 lookup fields that are configured as lookups on marketing lists. The FetchXML looks like below. What we want is for the query to look if the account is part of 1 or more marketing list in the 3 lookup fields. I added some "OR" in red below to illustrate the behaviour we are looking for.
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="new_type" operator="eq" value="100000000" />
</filter>
<link-entity name="list" from="listid" to="new_liste" alias="aa">
<link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true">
<link-entity name="account" from="accountid" to="entityid" alias="ac">
<filter type="and">
<condition attribute="accountid" operator="eq" uiname="abc" uitype="account" value="{GUID}" />
</filter>
</link-entity>
</link-entity>
</link-entity>
"OR"
<link-entity name="list" from="listid" to="new_liste2" alias="ab">
<link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true">
<link-entity name="account" from="accountid" to="entityid" alias="ad">
<filter type="and">
<condition attribute="accountid" operator="eq" uiname="abc" uitype="account" value="{GUID}" />
</filter>
</link-entity>
</link-entity>
</link-entity>
"OR"
<link-entity name="list" from="listid" to="new_liste3" alias="ae">
<link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true">
<link-entity name="account" from="accountid" to="entityid" alias="af">
<filter type="and">
<condition attribute="accountid" operator="eq" uiname="abc" uitype="account" value="{GUID}" />
</filter>
</link-entity>
</link-entity>
</link-entity>
</entity>
</fetch>
*This post is locked for comments
Do you have any way to convert it to Odata 4.0?
Hi guys,
(Dynamics 365 v9)_ By applying complex filter to our Views, the view became unavailable in Advance Find and when you try to run Advance Find while the updated view is displayed (Select the updated view from entity view, then click on Adv. find) the Adv. Find is broken for other views the Adv Find is working fine.
Any idea of what we can do to fix this?
Wow Thank you so much Alex. This works great. This is all magic to me!
I will keep this piece of code preciously!
Try this:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
<entity name="new_misesajour" >
<attribute name="new_name" />
<attribute name="new_date" />
<attribute name="new_version" />
<attribute name="new_gamme" />
<attribute name="new_compte" />
<attribute name="new_misesajourid" />
<order attribute="new_gamme" descending="false" />
<order attribute="new_date" descending="true" />
<filter type="and" >
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="new_type" operator="eq" value="100000000" />
<filter type="or" >
<condition attribute="accountid" operator="eq" uiname="abc" uitype="account" value="{48AC436F-4C1F-E511-80BD-0050569871D9}" entityname="ac" />
<condition attribute="accountid" operator="eq" uiname="abc" uitype="account" value="{48AC436F-4C1F-E511-80BD-0050569871D9}" entityname="ad" />
<condition attribute="accountid" operator="eq" uiname="abc" uitype="account" value="{48AC436F-4C1F-E511-80BD-0050569871D9}" entityname="af" />
</filter>
</filter>
<link-entity name="list" from="listid" to="new_liste" alias="aa" link-type="outer" >
<link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true" link-type="outer">
<link-entity name="account" from="accountid" to="entityid" alias="ac" link-type="outer" />
</link-entity>
</link-entity>
<link-entity name="list" from="listid" to="new_liste2" alias="ab" link-type="outer">
<link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true" link-type="outer">
<link-entity name="account" from="accountid" to="entityid" alias="ad" link-type="outer" />
</link-entity>
</link-entity>
<link-entity name="list" from="listid" to="new_liste3" alias="ae" link-type="outer">
<link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true" link-type="outer">
<link-entity name="account" from="accountid" to="entityid" alias="af" link-type="outer" />
</link-entity>
</link-entity>
</entity>
</fetch>
Here is where I am standing now:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
<entity name="new_misesajour" >
<attribute name="new_name" />
<attribute name="new_date" />
<attribute name="new_version" />
<attribute name="new_gamme" />
<attribute name="new_compte" />
<attribute name="new_misesajourid" />
<order attribute="new_gamme" descending="false" />
<order attribute="new_date" descending="true" />
<filter type="and" >
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="new_type" operator="eq" value="100000000" />
</filter>
<filter type="or" >
<condition attribute="accountid" operator="eq" uiname="abc" uitype="account" value="{48AC436F-4C1F-E511-80BD-0050569871D9}" entityname="ac" />
<condition attribute="accountid" operator="eq" uiname="abc" uitype="account" value="{48AC436F-4C1F-E511-80BD-0050569871D9}" entityname="ad" />
<condition attribute="accountid" operator="eq" uiname="abc" uitype="account" value="{48AC436F-4C1F-E511-80BD-0050569871D9}" entityname="af" />
</filter>
<link-entity name="list" from="listid" to="new_liste" alias="aa" >
<link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true" >
<link-entity name="account" from="accountid" to="entityid" alias="ac" link-type="outer" />
</link-entity>
</link-entity>
<link-entity name="list" from="listid" to="new_liste2" alias="ab" >
<link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true" >
<link-entity name="account" from="accountid" to="entityid" alias="ad" link-type="outer" />
</link-entity>
</link-entity>
<link-entity name="list" from="listid" to="new_liste3" alias="ae" >
<link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true" >
<link-entity name="account" from="accountid" to="entityid" alias="af" link-type="outer" />
</link-entity>
</link-entity>
</entity>
</fetch>
Hi Yvan,
could you post your fetchXml with OR at the top?
Thanks Alex and Aric!
I tried to move the OR conditions at the top manually, but this did not work. Maybe I'm missing something.
I am also giving a show with FetchXml Builder as suggested by Aric but I am still trying to figure out how to apply OR conditions to linked entities. I keep trying!
Yvan
Hi Yvan,
This is not possible using the Out of the Box Advanced Find.
We had a similar requirement, where we actually combined two separate FetchXml statements.
I would check XRMToolbox FetchXml Builder or the solution from Alex above, which seems it should be able to accomplish it.
Thanks Alex...
Hope this helps...
EDIT: Just used FetchXml Builder and was able to implement multiple Or conditions with Linked Entities...
Hi,
it should be doable, but not through the advanced find (so, if/when you get fetch working.. you may be able to update the view either through XrmToolBox or if you just update it in the customizations.xml)
The idea is that you need to move those OR conditions to the filter at the very top of your fetch. Use outer-joins for all those linked-entities (so that it does not stop returning results because of the joins), then, using those linke-entity aliases, define an OR filter for your main entity.
For the details, have a look:
www.itaintboring.com/.../two-most-ignored-features-of-fetchxml
Hi,
This is limitation create FetchXML using advanced find , I have tried to make it but its giving me error that related entity conditions or groups cannot be grouped with the parent entity conditions.
[View:https://community.dynamics.com/cfs-file/__key/communityserver-discussions-components-files/117/FetchXMLLimit.png]
Hope this helps.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,151 Super User 2024 Season 2
Martin Dráb 229,993 Most Valuable Professional
nmaenpaa 101,156