How do I calculate a total amount in a parent record from detail records? RRS feed

  • Question

  • All, I'm attempting to calculate a total from child records and save that total in the parent record.

    I created a QueryExpression to iterate through the child records; my goal is to simply accumulate the total payment amount.  However, I cannot seem to find the correct syntax.

    Please look at the code below and tell me what needs to change.



    QueryExpression query = new QueryExpression { EntityName = "contact", ColumnSet = new ColumnSet("firstname", "lastname", "new_paymentamount") }; query.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, contactEntityReference.Id); IOrganizationService contactService = factory.CreateOrganizationService(context.UserId); EntityCollection results2 = contactService.RetrieveMultiple(query); addrString = "Test:"; foreach (var r in results2.Entities) { try { //subMoneyTotal = subMoneyTotal.Value + (Money)r.Attributes["new_paymentamount"]; // Operator + cannot be applied to operands of type decimal and Microsoft.Xrm.Sdk.Money //subMoneyTotal = subMoneyTotal + (Money)r.Attributes["new_paymentamount"]; // Same error message Operator + cannot be applied ... } catch { //subMoneyTotal.Value = subMoneyTotal.Value; } }; addrCount = results2.TotalRecordCount; tracer.Trace("Found the following number of records: " + addrCount.ToString()); Entity newAccount = contactService.Retrieve("account", contactEntityReference.Id, new ColumnSet(true)); // This line gives me an error that The given key was not present in the dictionary tracer.Trace("Got to Entity newAccount");

    //Never got this far, is the line below the correct way to write the value into new_totalpayments?

    //newAccount.Attributes.Add("new_totalpayments", subMoneyTotal) // Never got this far contactService.Update(newAccount); tracer.Trace("Updated new account ... I think ...");

    Dan Packer

    Tuesday, May 22, 2012 8:17 AM


All replies

  • Refer below link for more help


    Thanks, Ankit Shah

    Inkey Solutions, India.
    Microsoft Certified Business Management Solutions Professionals

    Tuesday, May 22, 2012 9:51 AM
  • Dan,

    So am I correct by assuming that your parent record is a type of account and your child records are contacts?

    In that case, you might need to pass in the AccountId as the parentcustomerid instead of contact id as you are trying to retrieve a list of contacts that belong to the parent account.

    query.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, accountEntityReference.Id);

    The reason you are getting the error "The given key was not present in the dictionary" is because you are trying to query an account with the accountId = ContactId. This doesn't make sense. What you should do is to query the Account record by AccountId (assuming you have it handy)?

    To update the money field, you would need to use this

    newAccount["new_totalpayments"] = new Money(subMoneyTotal.Value);

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.

    Dimaz Pramudya - CRM Developer - CSG (Melbourne) www.xrmbits.com http://twitter.com/xrmbits

    Tuesday, May 22, 2012 10:38 AM
  • Ankit - thank you for your help! 

    The link provided exactly what I needed.


    Dan Packer

    Tuesday, May 22, 2012 1:58 PM
  • Perfect!

    Dan Packer

    Tuesday, May 22, 2012 1:59 PM