Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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,545

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

  • Kaustubh Tendulkar Profile Picture
    Kaustubh Tendulkar 15 on at
    RE: FetchXML "OR" Condition using multiple link-entities

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

  • MRVIX Profile Picture
    MRVIX 1,258 on at
    RE: FetchXML "OR" Condition using multiple link-entities

    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?

  • yleclerc Profile Picture
    yleclerc 1,545 on at
    RE: FetchXML "OR" Condition using multiple link-entities

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

    I will keep this piece of code preciously!

  • Verified answer
    ashlega Profile Picture
    ashlega 34,475 on at
    RE: FetchXML "OR" Condition using multiple link-entities

    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,545 on at
    RE: FetchXML "OR" Condition using multiple link-entities

    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>

  • ashlega Profile Picture
    ashlega 34,475 on at
    RE: FetchXML "OR" Condition using multiple link-entities

    Hi Yvan,

     could you post your fetchXml with OR at the top?

  • yleclerc Profile Picture
    yleclerc 1,545 on at
    RE: FetchXML "OR" Condition using multiple link-entities

    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

  • Aric Levin Profile Picture
    Aric Levin 30,188 on at
    RE: FetchXML "OR" Condition using multiple link-entities

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

  • Suggested answer
    ashlega Profile Picture
    ashlega 34,475 on at
    RE: FetchXML "OR" Condition using multiple link-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

  • gdas Profile Picture
    gdas 50,085 on at
    RE: FetchXML "OR" Condition using multiple link-entities

    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.

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

photo of Muhammad Affan

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

thumbnail image of a pie chart section

Top 10 leaders for November!

Congratulations to our November super stars!

thumbnail image of the word Tip

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans