Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / get Sum of amount fiel...
Customer experience | Sales, Customer Insights,...
Suggested answer

get Sum of amount field from Child entity via Fetch XML

(0) ShareShare
ReportReport
Posted on by snehaln676 140

I need to fetch child entities along with the SUM of a field 'amount' from all child records (of a particular parent record). For E.g. If there are 3 child records for a parent record, so need to have SUM of all 3 different amount values of each child record. Also, the SUM needs to be calculated for the child records where it meets certain criteria. I already tried adding aggregate along with filter condition (so that my aggregate would be applicable on filtered records only), but it did not work. Please suggest.

TIA.

  • Suggested answer
    Joana Pinto Profile Picture
    Joana Pinto 740 on at
    RE: get Sum of amount field from Child entity via Fetch XML

    Hi,

    You just need to add the filter condition that you want on the link entity. In my case, my related record is Lead records and I put a condition saying that I only want the sum of Leads that are Open (status field). Like this (line 10):

    If you still have doubts please, provide your fetchxml so we can help you.

    Thanks.

  • snehaln676 Profile Picture
    snehaln676 140 on at
    RE: get Sum of amount field from Child entity via Fetch XML

    I have a scenario where I have to retrieve data from child entity using fetchxml. Example: I have few child records having an attribute 'Role' with values RoleA, RoleB, RoleC (which is an Optionset) and another attribute as Amount(textfield). I want to get sum of amount for those records having value 'RoleB'. Please help me with the filter condition.

  • Suggested answer
    Joana Pinto Profile Picture
    Joana Pinto 740 on at
    RE: get Sum of amount field from Child entity via Fetch XML

    Hi,

    So I've made an example on my own, with the following fetchxml:

    And I think this is you scenario. I have an Account that has a 1:N relationship with Lead entity. So I've done a fetch that makes the sum of the "estimatedamount" field on every lead related to that Account. My result was the following:

    <resultset morerecords="0">
    <result>
    <name>Joana Pinto</name>
    <amount formattedvalue="€4,654.01">4654.01</amount>
    </result>
    </resultset>

    You have to put on the beginning the aggregate="true" and then put the aggregate="sum" on the field you want to sum.

    Regards,

    Joana

  • David Jennaway Profile Picture
    David Jennaway 14,063 on at
    RE: get Sum of amount field from Child entity via Fetch XML

    This should be possible. Can you post the fetchXml that you have so far, and an example of the results ?

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans