web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / FetchXML "OR"...
Microsoft Dynamics CRM (Archived)

FetchXML "OR" Condition using multiple link-entities

(0) ShareShare
ReportReport
Posted on by yleclerc 1,549

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

I have the same question (0)
  • gdas Profile Picture
    gdas 50,091 Moderator on at

    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.

  • Suggested answer
    ashlega Profile Picture
    ashlega 34,477 on at

    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

  • Aric Levin - MVP Profile Picture
    Aric Levin - MVP 30,190 Moderator on at

    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...

  • yleclerc Profile Picture
    yleclerc 1,549 on at

    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

    2018_2D00_05_2D00_11_5F00_08_2D00_59_2D00_33.jpg

  • ashlega Profile Picture
    ashlega 34,477 on at

    Hi Yvan,

     could you post your fetchXml with OR at the top?

  • yleclerc Profile Picture
    yleclerc 1,549 on at

    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>

  • Verified answer
    ashlega Profile Picture
    ashlega 34,477 on at

    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>

  • yleclerc Profile Picture
    yleclerc 1,549 on at

    Wow Thank you so much Alex. This works great. This is all magic to me!

    I will keep this piece of code preciously!

  • MRVIX Profile Picture
    MRVIX 1,421 on at

    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.

    2019_2D00_02_2D00_19_5F00_08h02_5F00_16_5F00_adv.png

    Any idea of what we can do to fix this?

  • Kaustubh Tendulkar Profile Picture
    Kaustubh Tendulkar 15 on at

    Do you have any way to convert it to Odata 4.0?

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans