Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

FetchXML for recursive relationships

  • 1.  FetchXML for recursive relationships

    Posted Mar 14, 2019 11:19 AM

    Hi all,

    I need to get all Opportunities that are linked to accounts that match my criteria for accounts (for simplicity sake, lets say I want all active accounts) AND all opportunities that are also linked to all of the accounts that have hierarchical relationship to the account the opportunity is directly linked to.

    For example, if I have an Account A and it's a parent to 3 other accounts (B,C,D), I want to fetch all Opportunities where Account A is the Client and all of the opportunities where Accounts B,C,D are also Clients. 

    Anyone knows how to achieve this? 

    TIA,

    -TS.



    ------------------------------
    Tony Suslovich
    Sr. Business Analyst/Programmer
    Ove Arup and Partners International Ltd
    Edison NJ
    ------------------------------


  • 2.  RE: FetchXML for recursive relationships

    GOLD CONTRIBUTOR
    Posted Mar 15, 2019 05:41 AM
    Not sure you can do this in one query to get all the opportunities that are in the hierarchy of all accounts that match some criteria, but if you have a single account then you can use the "Under" criteria:

    <fetch top="50" >
    <entity name="opportunity" >
    <filter>
    <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <link-entity name="account" from="accountid" to="customerid" >
    <filter>
    <condition attribute="accountid" operator="eq-or-under" value="e3345fae-c92e-4f68-b07e-73929d36d5be" />
    </filter>
    </link-entity>
    </entity>
    </fetch>


    ------------------------------
    Mark Carrington
    Chief Technologist
    Data8
    Chester
    ------------------------------



  • 3.  RE: FetchXML for recursive relationships

    Posted Mar 15, 2019 08:34 AM
    Hi Mark,

    Thanks for your suggestion. This is the path I initially took, but I need to pick up all opportunities from a group of accounts, not just from a specific account and there seems to be no way to have a variable name from linked entity as a condition value. I would love to be able to do something like:

    <fetch top="50" >
     <entity name="opportunity" >
      <filter>
       <condition attribute="statecode" operator="eq" value="0" />
      </filter>
      <link-entity name="account" from="accountid" to="customerid" >
       <filter>
        <condition attribute="accountid" operator="eq-or-under" value="customerid" />
       </filter>
      </link-entity>
     </entity>
    </fetch>

    ------------------------------
    Tony Suslovich
    Sr. Business Analyst/Programmer
    Ove Arup and Partners International Ltd
    Edison NJ
    ------------------------------



  • 4.  RE: FetchXML for recursive relationships

    SILVER CONTRIBUTOR
    Posted Mar 15, 2019 10:02 AM
    HI @Tony Suslovich

    Is the net result you simply want all the opportunities associated to an account, why does it matter if they are in hierarchical relationship? Just trying to understand.


    ​​

    ------------------------------
    Abdul Majid
    Westwood Software Limited
    ------------------------------



  • 5.  RE: FetchXML for recursive relationships

    Posted Mar 15, 2019 10:23 AM
    Hi Abdul,

    Thanks for your reply.

    It's not a specific Account I want to pull all opportunities for. I want to pull opportunities for all Accounts in the same hierarchy as the Account linked to the Opportunity.

    For example:

    I have hierarchy in CRM that looks like this:

    Account A (parent)
    ------Account B (child of Account A)
    ------Account C (child of Account A)
    ---------------------Account D (child of Account C)
    ---------------------Account E (child of Account C)
    ------Account F (child of Account A)
    ---------------------Account G (child of Account F)
    ---------------------Account H (child of Account F)
    ------Account I (child of Account A)

    If I have an opportunity linked to Account A, I want to pull all of the opportunities linked to accounts in this hierarchy (Accounts B,C,D,E,F,G,H,I).

    If I have an opportunity linked to Account G, I also want to pull all of the opportunities linked to accounts in this hierarchy (Accounts A,B,C,D,E,F,H,I).

    At the very least I want to pull all opportunities that are 'over' the account directly linked to an Opportunity.

    Thanks.

    ------------------------------
    Tony Suslovich
    Sr. Business Analyst/Programmer
    Ove Arup and Partners International Ltd
    Edison NJ
    ------------------------------



  • 6.  RE: FetchXML for recursive relationships

    SILVER CONTRIBUTOR
    Posted Mar 15, 2019 10:32 AM
    Thanks Tony Suslovich

    If you do a simple query like so:

    <fetch top="50" >
    <entity name="account" >
    <attribute name="name" />
    <link-entity name="opportunity" from="customerid" to="accountid" >
    <attribute name="name" />
    </link-entity>
    </entity>
    </fetch>

    This will give you all the opportunities linked to an account record, irrespective of the relationship between the accounts.

    Does this meet your requirement.



    ------------------------------
    Abdul Majid
    Westwood Software Limited
    ------------------------------



  • 7.  RE: FetchXML for recursive relationships

    Posted Mar 15, 2019 11:19 AM
    Hi again Abdul,

    This won't really satisfy the requirements I'm trying to achieve. It will only pull opportunities for either a specific group of accounts or all accounts. I need all opportunities linked to accounts in the same hierarchy as the account linked to an opportunity.

    ------------------------------
    Tony Suslovich
    Sr. Business Analyst/Programmer
    Ove Arup and Partners International Ltd
    Edison NJ
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role