Populating the total value of related records

• Question

• 

  

  0

  Sign in to vote

  Hope some-one can help me - going round in circles here. :(

  I found this code on one of the forums which I am using to sum records in a related entity. I have a custom entity called "Projects" which has a one to many relationship with Opportunities. I have put in bold my attributes and entity names. So, basically, I am using this on the onload on the Projects form to sum the "estimatedvalue" attribute of all the related Opporutnity records and put the total into an attribute called "new_totalcost" on the Project record.

  It works absolutely fine.................. until I delete or unattach all the related records. So, I add several records by linking Opportunties to the Project. I then open the project and the calculations work fine BUT if I then remove the related records when I next open the Project, the calcuations are still reading what they had last time. This only appears to break if I remove every record attached to the Project. Now, I do understand that I am using "crmForm.all.new_totalcost.ForceSubmit=true;" which is writing the total as an actual value to the field but what I don't understand is why, if it doesn't find any related records, it doesn't write a 0 to the field when it next loads. :(

  Hands up - this is what happens when you use code you don't actually understand, LOL but if some-one could point me in the right direction I would be very grateful.

  As an aside, if I wanted to total another field on the Projects form which also is based on the Opportunity entity, do I just paste all the code again and change the attribute name from estimatevalue to my other attribute which is new_suppliercost?

  Thanks. AW

   

  if (crmForm.FormType != 1) {
    var oId = crmForm.ObjectId;
    //alert(oId);
    var xml = "" +
  "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
  "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" + GenerateAuthenticationHeader() +
  " <soap:Body>" +
  " <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
  " <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
  " <q1:EntityName>opportunity</q1:EntityName>" +
  " <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
  " <q1:Attributes>" +
  " <q1:Attribute>new_projectcodeid</q1:Attribute>" +
  " <q1:Attribute>estimatedvalue</q1:Attribute>" +
  " </q1:Attributes>" +
  " </q1:ColumnSet>" +
  " <q1:Distinct>false</q1:Distinct>" +
  " <q1:Criteria>" +
  " <q1:FilterOperator>And</q1:FilterOperator>" +
  " <q1:Conditions>" +
  " <q1:Condition>" +
  " <q1:AttributeName>new_projectcodeid</q1:AttributeName>" +
  " <q1:Operator>Equal</q1:Operator>" +
  " <q1:Values>" +
  " <q1:Value xsi:type=\"xsd:string\">" + oId + "</q1:Value>" +
  " </q1:Values>" +
  " </q1:Condition>" +
  " </q1:Conditions>" +
  " </q1:Criteria>" +
  " </query>" +
  " </RetrieveMultiple>" +
  " </soap:Body>" +
  "</soap:Envelope>";
    var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
    xmlHttpRequest.send(xml);
    var resultXml = xmlHttpRequest.responseXML.xml;
    //alert(resultXml );
    var oTotal = 0;
      if (resultXml != null || resultXml != "") {
      var xmlDocument = new ActiveXObject("Microsoft.XMLDOM");
      var bLoaded = xmlDocument.loadXML(resultXml);
      if (bLoaded) {
        var businessEntities = xmlDocument.getElementsByTagName('BusinessEntity');

        for (i = 0; i < businessEntities.length; i++)
  {
          try {
            var oDiscount = businessEntities[i].selectSingleNode("./q1:estimatedvalue").text;
          
            oTotal += parseFloat(oDiscount);
            crmForm.all.new_totalcost.DataValue = oTotal;
           crmForm.all.new_totalcost.ForceSubmit=true;
          }
          catch (err) { }
        }

      }
    }
  }

  Thursday, May 12, 2011 8:13 PM