locked
Field updated by Script in Form is not refelected in 'Default Public View' of Opportunity RRS feed

  • Question

  • Hi Gurus,

    to fulfil an internal requirement I have created a "Revenue Projection" entity to allow sales user to populate revenue split by month and associated this entity to Opportunity. Then I wrote an script on the Opportunity Form to total the Monthly Revenue Split Columns and update the "Est. Revenue" field on the Opportunity. This works fine and the total of all the months of splitted revenue is visible for any record on Opportunity Form. BUT the sum total is not refelected when I see the same record in Default Public View i.e. "My Open Opportunities". This view still shows old values and not the updated sum totals.
    - I have tried sign-out/sign-in even reboot.
    - I have made sure that the column/field for both form and public view is exactly the same i.e. 'estimatedvalue'
    - I also noticed that the revenue totals are not reflected in any other views like Advanced Find, My Open Opportunities, Open Opportunities. The correct total is visible only in Form where it is being updated by the script.

    Thank you in advance!

    For your reference, I am attaching the script I have written on Form Load Event of the Opportunity Form. (CRM 4.0)


    //  Define Variables
        var sOptyId;
        var CRM_FORM_TYPE_UPDATE = 2; //to execute the logic only in Form Update Mode
        var sAuthenticationHeader;
        var sXML;
        var sResultXML;
        var xmlHttpRequest;
        var oXmlDoc; //xml document for parsing the result
        var businessEntities;

        var iJanRev = 0;
        var iFebRev = 0;
        var iMarRev = 0;
        var iAprRev = 0;
        var iMayRev = 0;
        var iJunRev = 0;
        var iJulRev = 0;
        var iAugRev = 0;
        var iSepRev = 0;
        var iOctRev = 0;
        var iNovRev = 0;
        var iDecRev = 0;
        var iTotalRevenue = 0;

        

     

    //  As sales people would enter monthly detail in Revenue Projection (associated entity), there will
    //  no need to populate or updated est. Total Revenue. Hence relevant fields on the form will be
    //  disabled.
        crmForm.all.estimatedvalue.Disabled = true;
        crmForm.all.isrevenuesystemcalculated.Disabled = true;


    //  The details will be processed only if the form was open in updated mode. For the first time i.e. when the form
    //  is in 'Create' mode, do nothing!

        if (crmForm.FormType == CRM_FORM_TYPE_UPDATE)
        {
          //  Store the Opportunity Id to retreive relevant "Revenue Projections"
              sOptyId = crmForm.ObjectId;

          //  Use the GenerateAuthenticationHeader() method
          //  available from the CRM form to generate the Soap header text.
              sAuthenticationHeader = window.opener.GenerateAuthenticationHeader();

          //  Define the SOAP XML to access Microsoft Dynamics CRM Web service.
              var sXML = "<?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\">" +
              sAuthenticationHeader+
              "<soap:Body>" +

          //  Specify the RetrieveMultiple message.
              "<RetrieveMultiple xmlns="+
              "\"http://schemas.microsoft.com/crm/2007/WebServices\">" +

          //  Specify that this is a QueryByAttribute query.
              "<query xmlns:q1="+
              "\"http://schemas.microsoft.com/crm/2006/Query\" "+
              "xsi:type=\"q1:QueryByAttribute\">" +

          //  Query the Revenue Projection Entity
              "<q1:EntityName>new_revenueprojection</q1:EntityName>" +

          //  Set the columns you want to return.
              "<q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
              "<q1:Attributes>" +
              "<q1:Attribute>new_janrev</q1:Attribute>" +
              "<q1:Attribute>new_febrev</q1:Attribute>" +
              "<q1:Attribute>new_marrev</q1:Attribute>" +
              "<q1:Attribute>new_aprrev</q1:Attribute>" +
              "<q1:Attribute>new_mayrev</q1:Attribute>" +
              "<q1:Attribute>new_junrev</q1:Attribute>" +
              "<q1:Attribute>new_julrev</q1:Attribute>" +
              "<q1:Attribute>new_augrev</q1:Attribute>" +
              "<q1:Attribute>new_seprev</q1:Attribute>" +
              "<q1:Attribute>new_octrev</q1:Attribute>" +
              "<q1:Attribute>new_novrev</q1:Attribute>" +
              "<q1:Attribute>new_decrev</q1:Attribute>" +
              "</q1:Attributes>" +
              "</q1:ColumnSet>" +

          //  Specify the attribute that you are querying on.
              "<q1:Attributes>" +
              "<q1:Attribute>new_revenueprojectionidid</q1:Attribute>" +
              "</q1:Attributes>" +

          //  Set the value of the attribute using the OptyId
              "<q1:Values>" +
              "<q1:Value xsi:type=\"xsd:string\">"+
              sOptyId+
              "</q1:Value>" +
              "</q1:Values>" +
              "</query>" +
              "</RetrieveMultiple>" +
              "</soap:Body>" +
              "</soap:Envelope>";

          //  Create an instance of an XMLHTTP object.
              xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

          //  Configure the XMLHttp object for the Microsoft CRM Web services.
              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", sXML.length);

          //  Send the XMLHttp request.
              xmlHttpRequest.send(sXML);

          //  Capture the XMLHttp response in XML format.
              sResultXML = xmlHttpRequest.responseXML;

          //  Create an XML document that you can parse.
              oXmlDoc = new ActiveXObject("Microsoft.XMLDOM");
              oXmlDoc.async = false;

          //  Load the document that has the results.
              oXmlDoc.loadXML(sResultXML.xml);

          //  Get only the BusinessEntity results.
              businessEntities = oXmlDoc.getElementsByTagName('BusinessEntity');

          //  Loop through the BusinessEntity elements.
              for (i=0;i < businessEntities.length;i++)
              {
                  iJanRev = businessEntities[i].selectSingleNode('./q1:new_janrev')
                  iTotalRevenue = iTotalRevenue + ((iJanRev == null)? 0 : parseInt(iJanRev.text))
                  iFebRev = businessEntities[i].selectSingleNode('./q1:new_febrev')
                  iTotalRevenue = iTotalRevenue + ((iFebRev == null)? 0 : parseInt(iFebRev.text))
                  iMarRev = businessEntities[i].selectSingleNode('./q1:new_marrev')
                  iTotalRevenue = iTotalRevenue + ((iMarRev == null)? 0 : parseInt(iMarRev.text))
                  iAprRev = businessEntities[i].selectSingleNode('./q1:new_aprrev')
                  iTotalRevenue = iTotalRevenue + ((iAprRev == null)? 0 : parseInt(iAprRev.text))
                  iMayRev = businessEntities[i].selectSingleNode('./q1:new_mayrev')
                  iTotalRevenue = iTotalRevenue + ((iMayRev == null)? 0 : parseInt(iMayRev.text))
                  iJunRev = businessEntities[i].selectSingleNode('./q1:new_junrev')
                  iTotalRevenue = iTotalRevenue + ((iJunRev == null)? 0 : parseInt(iJunRev.text))
                  iJulRev = businessEntities[i].selectSingleNode('./q1:new_julrev')
                  iTotalRevenue = iTotalRevenue + ((iJulRev == null)? 0 : parseInt(iJulRev.text))
                  iAugRev = businessEntities[i].selectSingleNode('./q1:new_augrev')
                  iTotalRevenue = iTotalRevenue + ((iAugRev == null)? 0 : parseInt(iAugRev.text))
                  iSepRev = businessEntities[i].selectSingleNode('./q1:new_seprev')
                  iTotalRevenue = iTotalRevenue + ((iSepRev == null)? 0 : parseInt(iSepRev.text))
                  iOctRev = businessEntities[i].selectSingleNode('./q1:new_octrev')
                  iTotalRevenue = iTotalRevenue + ((iOctRev == null)? 0 : parseInt(iOctRev.text))
                  iNovRev = businessEntities[i].selectSingleNode('./q1:new_novrev')
                  iTotalRevenue = iTotalRevenue + ((iNovRev == null)? 0 : parseInt(iNovRev.text))
                  iDecRev = businessEntities[i].selectSingleNode('./q1:new_decrev')
                  iTotalRevenue = iTotalRevenue + ((iDecRev == null)? 0 : parseInt(iDecRev.text))


              }
           
            //alert("Total Revenue is " + iTotalRevenue);
            crmForm.all.estimatedvalue.DataValue = iTotalRevenue;

        }

    Tuesday, April 14, 2009 11:06 AM

Answers

  • What event is your script on?  OnLoad or OnSave?  If it is OnLoad, you need to make sure that you are saving the opportunity record (not just the revenue projection record).  Just a thought, but maybe users aren't saving the changed opportunity?  One thing to try is moving the rollup to onsave to force the user to save the opportunity before the rollup shows up.

    Also, if you open an opportuntiy from a view, such as active opps, you may need to refresh the view after the opportunity is saved to get it to show up.
    • Marked as answer by Jim Glass Jr Tuesday, April 14, 2009 4:41 PM
    Tuesday, April 14, 2009 12:26 PM
    Moderator

All replies

  • If you use a on form script, it won't calculate until the record is opened, and that value won't be retained unless the record is saved.  Javascript is not optimal for calculating rollups.

    You can do this with a workflow.  do an oncreate workflow for revenue projection to increment the revenue split columns on the opportunity with the value of the revenue projection.  Also have it subtract the values of revenue split if the revenue split record is deactivated.

    Or if you are the programmer type, you can do this with a custom plugin.

    Either of these approaches are better than javascript because they are not dependent on somebody opening and saving the opportunity record for the rollup calculation to happen.
    Tuesday, April 14, 2009 11:52 AM
    Moderator
  • Hi Joel,

    Thanks a lot for the reply. I would give Workflow a go but I want to understand, why the totals are not reflected everywhere even after explicit manual save of the Opportunity record?

    THanks again.

    Tuesday, April 14, 2009 12:03 PM
  • What event is your script on?  OnLoad or OnSave?  If it is OnLoad, you need to make sure that you are saving the opportunity record (not just the revenue projection record).  Just a thought, but maybe users aren't saving the changed opportunity?  One thing to try is moving the rollup to onsave to force the user to save the opportunity before the rollup shows up.

    Also, if you open an opportuntiy from a view, such as active opps, you may need to refresh the view after the opportunity is saved to get it to show up.
    • Marked as answer by Jim Glass Jr Tuesday, April 14, 2009 4:41 PM
    Tuesday, April 14, 2009 12:26 PM
    Moderator
  • Hi Joel,

    Thanks for continuing the discussion.


    The script is on OnLoad and I am just in test phase explicitly saving Revenue Projection, Opportunity etc. Once I save Opportunity the Opportunity form shows the updated total (as it runs the script written on Opportunity Form). I then save and close opportunity and view the same record in the Open Opportunity View but the Est. Revenue field is not updated there. I even tried refreshing and re-quering the record but still the view shows the old Est. Total.

    Still not sure what is stopping views to display values updated in forms.

    Awaiting comments.

    Prafulla
    Tuesday, April 14, 2009 12:39 PM
  • Hello Prafulla,

    I know this is an old post but I'm having the same problem with a calculated field showing the correct value on a form but an old value in a view.  Did you get an answer to this?  Or does anyone else out there know?

    Ruth


    Ruth
    Thursday, December 15, 2011 4:27 PM