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

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) / Is it possible to retu...
Microsoft Dynamics CRM (Archived)

Is it possible to return multiple child records for a specific parent record for a report using FetchXML

(0) ShareShare
ReportReport
Posted on by Community Member

I want to return one parent record (Quote) along with all of its child records (Quote Details + linked entity Product) for use in a report using Fetch XML. I am using the Parent Report-Sub Report structure currently, but I need to access data that is stored on the Product in the Parent report.

I have attempted to create a Fetch XML query that fits this scenario, but I have only been able to write a query that returns the pre-filtered Quote header but all Quote Details regardless of which quote it belongs to.

The current Parent Report Fetch XML:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="quote" enableprefiltering="1" prefilterparametername="CRM_FilteredQuote">
    <attribute name="name" />
    <attribute name="customerid" />
    <attribute name="statecode" />
    <attribute name="totalamount" />
    <attribute name="discountpercentage" />
    <attribute name="description" />
    <attribute name="new_productsubgroupid" />
    <attribute name="new_masterproductgroupid" />
    <attribute name="quotenumber" />
    <attribute name="ownerid" />
    <attribute name="createdon" />
    <attribute name="quoteid" />
    <attribute name="effectiveto" />
    <attribute name="effectivefrom" />
    <order attribute="quotenumber" descending="true" />
  </entity>
</fetch>

The current Sub Report Fetch XML:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="quotedetail">
    <attribute name="productid" />
    <attribute name="productdescription" />
    <attribute name="priceperunit" />
    <attribute name="quantity" />
    <attribute name="extendedamount" />
    <attribute name="quotedetailid" />
    <order attribute="productid" descending="false" />
    <filter type="and">
      <condition attribute="quoteid" operator="eq" uitype="quote" value="@QuoteId" />
    </filter>
    <link-entity name="product" alias="product" to="productid" from="productid" link-type="outer" visible="false">
      <attribute name="price" />
    </link-entity>
  </entity>
</fetch>

My attempt at a combined query (returns a single Quote, but all Quote Details, regardless of parent):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
    <entity name="quote" enableprefiltering="1" prefilterparametername="CRM_FilteredQuote" >
        <attribute name="name" />
        <attribute name="customerid" />
        <attribute name="statecode" />
        <attribute name="totalamount" />
        <attribute name="discountpercentage" />
        <attribute name="description" />
        <attribute name="new_productsubgroupid" />
        <attribute name="new_masterproductgroupid" />
        <attribute name="quotenumber" />
        <attribute name="ownerid" />
        <attribute name="createdon" />
        <attribute name="quoteid" />
        <attribute name="effectiveto" />
        <attribute name="effectivefrom" />
        <order attribute="quotenumber" descending="true" />
        <link-entity name="quotedetail" alias="quoteProduct" to="quoteid" from="quoteid" link-type="outer" enableprefiltering="1" prefilterparametername="CRM_FilteredQuote" >
            <attribute name="productid" />
            <attribute name="productdescription" />
            <attribute name="priceperunit" />
            <attribute name="quantity" />
            <attribute name="extendedamount" />
            <attribute name="quotedetailid" />
            <order attribute="productid" descending="false" />
            <link-entity name="product" alias="product" to="productid" from="productid" link-type="outer" visible="false" >
                <attribute name="price" />
                <attribute name="new_submittleurl" />
            </link-entity>
        </link-entity>
    </entity>
</fetch>

*This post is locked for comments

I have the same question (0)
  • Mick_4711 Profile Picture
    Mick_4711 552 on at

    Hi Josh,

    if I understood you right, maybe this fetchxml will get the information you want:
    (in quotedetails the description is the name of the existing product, productdescription is the name of the write-in-product)

    <fetch>
    <entity name="quote" >
    <attribute name="quotenumber" />
    <attribute name="quoteid" />
    <attribute name="totalamount" />
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="statecode" />
    <link-entity name="quotedetail" from="quoteid" to="quoteid" >
    <attribute name="description" />
    <attribute name="productid" />
    <attribute name="quotedetailid" />
    <attribute name="producttypecode" />
    <attribute name="productdescription" />
    <attribute name="transactioncurrencyidname" />
    <attribute name="priceperunit" />
    <attribute name="transactioncurrencyid" />
    <attribute name="quantity" />
    <attribute name="extendedamount" />
    <link-entity name="product" from="productid" to="productid" link-type="outer" >
    <attribute name="description" />
    </link-entity>
    </link-entity>
    </entity>
    </fetch>

    If you want more information about products in your query then you can put these information in the last link-entity (link-entity name="product") and put some more attributes in this. If not you can delete this xml-section. (<link-entity name="product" from="productid" to="productid" link-type="outer" >
    <attribute name="description" />
    </link-entity>)

  • Community Member Profile Picture
    Community Member on at

    I have heavily edited my post. I re-read it today and it didn't make much sense at all. I will try to explain it better. Say Quote 1 has 3 Quote Details, QD A, QD B, and QD C.

    With a Parent Report, I am getting the information about Quote 1. In the Sub Report, I am getting the Information about QD A, QD B, and QD C.

    I want a query that will return all of the required information from Quote 1, as well as QD A, QD B, and QD C in one single query. I currently have a query that will return the information about Quote 1, QD A, QD B, and QD C, but the issue is that I am also getting QD X, QD Y, and QD Z returned in this query, which are associated with Quote 2, 3 and 4 respectfully.

    Does this make more sense?

  • Suggested answer
    Aditya Profile Picture
    Aditya 260 on at

    You can use the sample code of retrieving orderline items from a parent order below to meet your requirement of retrieving quoteline items from a quote :-

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

     <entity name="salesorderdetail">

       <attribute name="productid" />

    <attribute name="manualdiscountamount" />

       <attribute name="productdescription" />

       <attribute name="priceperunit" />

       <attribute name="quantity" />

       <attribute name="extendedamount" />

       <attribute name="salesorderdetailid" />

       <attribute name="volumediscountamount" />

       <attribute name="tax" />

       <attribute name="productname" />

       <attribute name="priceperunit_base" />

       <attribute name="lineitemnumber" />

       <attribute name="msdyn_costpriceperunit" />

       <attribute name="msdyn_costamount" />

       <attribute name="baseamount" />

    <attribute name="uomid" />

        <order attribute="productid" descending="false" />

    <filter type="and">

         <condition attribute="salesorderid" operator="eq"  uitype="salesorder" value="@OrderId" />

       </filter>

    <link-entity name="salesorder" alias="salesorder" to="salesorderid" from="salesorderid" link-type="outer" visible="false" >

           <attribute name="name" />

           <attribute name="customerid" />

           <attribute name="statecode" />

           <attribute name="totalamount" />

           <attribute name="discountpercentage" />

           <attribute name="description" />

           <attribute name="new_productsubgroupid" />

           <attribute name="new_masterproductgroupid" />

           <attribute name="quotenumber" />

           <attribute name="ownerid" />

           <attribute name="createdon" />

           <attribute name="quoteid" />

           <attribute name="effectiveto" />

           <attribute name="effectivefrom" />

        </link-entity>

     </entity>

    </fetch>

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
JS-09031509-0 Profile Picture

JS-09031509-0 3

#2
AS-17030037-0 Profile Picture

AS-17030037-0 2

#2
Mark Eckert Profile Picture

Mark Eckert 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans