Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Fetch XML with outer

Jump to Best Answer
  • 1.  Fetch XML with outer

    TOP CONTRIBUTOR
    Posted Jan 09, 2020 05:36 AM
    Hi,
    I am struggling with a Fetch XML and I think I try to do something that is not possible and need a confirmation. The assignment is: Our customers can buy a monthly subscription (fs_paidcontent) and every month a new fs_paidcontent record is created and associated with the contact. I need a list of contacts where the fs_paidcontent expired last month and they have not signup for a new fs_paidcontent this month.
    I tried different approaches already but I always get customers with new subscriptions in my list.

    Fetch XML where i link the entity 2 times:
    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
    <entity name="contact" >
    <attribute name="fullname" />
        <link-entity name="fs_paidcontent" from="fs_customer" to="contactid" alias="am" >
              <filter type="and" >
                    <condition attribute="fs_cancelationdate" operator="not-null" />
                    <condition attribute="fs_expiredate" operator="last-month" />
              </filter>
         </link-entity>
         <link-entity name="fs_paidcontent" from="fs_customer" to="contactid" link-type="outer" alias="an" >
              <filter type="and" >
                    <condition attribute="fs_expiredate" operator="this-month" />
              </filter>
         </link-entity>
    </entity>
    </fetch>

    Fetch XML where i link the entity to the contact again:
    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
    <entity name="contact" >
    <attribute name="fullname" />
         <link-entity name="fs_paidcontent" from="fs_customer" to="contactid" alias="ar" >
         <filter type="and" >
              <condition attribute="fs_cancelationdate" operator="not-null" />
              <condition attribute="fs_expiredate" operator="last-month" />
         </filter>
                   <link-entity name="contact" from="contactid" to="fs_customer" link-type="outer" alias="as" >
                        <link-entity name="fs_paidcontent" from="fs_customer" to="contactid" link-type="outer" alias="at" > (doesn't work with or without the outer here)
                             <filter type="and" >
                             <filter type="or" >
                                  <condition attribute="fs_expiredate" operator="this-month" />
                                  <condition attribute="fs_expiredate" operator="next-month" />
                             </filter>
                                  <condition attribute="fs_cancelationdate" operator="null" />
                             </filter>
              </link-entity>
         </link-entity>
    </link-entity>
    </entity>
    </fetch>

    ------------------------------
    Axel Girgensohn
    Dynamics CRM Specialist
    Aller Media AB
    ------------------------------
    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: Fetch XML with outer
    Best Answer

    TOP CONTRIBUTOR
    Posted Jan 09, 2020 07:16 AM

    Hello,
    There is one piece to make your first query to return valid data - it is an additional filter to enable "Outer Join" feature to work the right way:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
    <entity name="contact" >
    <attribute name="fullname" />
        <link-entity name="fs_paidcontent" from="fs_customer" to="contactid" alias="am" >
              <filter type="and" >
                    <condition attribute="fs_cancelationdate" operator="not-null" />
                    <condition attribute="fs_expiredate" operator="last-month" />
              </filter>
         </link-entity>
         <link-entity name="fs_paidcontent" from="fs_customer" to="contactid" link-type="outer" alias="an" >
              <filter type="and" >
                    <condition attribute="fs_expiredate" operator="this-month" />
              </filter>
         </link-entity>
        <filter type="and" >
          <condition entityname="an" attribute="fs_paidcontentid" operator="null" />
        </filter>
    </entity>
    </fetch>


    ------------------------------
    Andrew Butenko
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: Fetch XML with outer

    TOP CONTRIBUTOR
    Posted Jan 09, 2020 09:47 AM
    Hi Andrew
    thank you for this hint. I was close but the cigar is yours :-)
    That extra filter on the related entity ID on the outer linked entity made the thing work.

    ------------------------------
    Axel Girgensohn
    Dynamics CRM Specialist
    Aller Media AB
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


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