locked
CRM 2015: Javascript Calculations (excel like) RRS feed

  • Question

  • hello all,

    i'm in need of performing excel like calculations in CRM form. Like, say i have 5 fields (VAL1, VAL2, VAL3, VAL4 and VAL5) of type decimal and a Total field (also decimal). The values i add in the VAL fields should add up and the sum should be displayed in the total field - Importantly, this should happen on OnChange of the VAL fields (all 5).

    Does anyone has any ideas?

    THX

    Wednesday, December 9, 2015 8:07 AM

Answers

  • Hi,
    This is an extension to Ricard's reply.
    You can have one function in Javascript and call it onChange of five attributes.
    function sumOfValues()
    {
       var vTotal = 0.0;
       if(Xrm.Page.getAttribute('VAL1').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL1').getValue();
       }
       if(Xrm.Page.getAttribute('VAL2').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL2').getValue();
       }
       if(Xrm.Page.getAttribute('VAL3').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL3').getValue();
       }
       if(Xrm.Page.getAttribute('VAL4').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL4').getValue();
       }
       if(Xrm.Page.getAttribute('VAL5').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL5').getValue();
       }
       Xrm.Page.getAttribute('TotalField').setValue(vTotal)
    }

    Gopinath


    My blog

    • Marked as answer by MayBu Wednesday, December 9, 2015 10:44 AM
    Wednesday, December 9, 2015 8:43 AM
  • another version of the code:

    function sumFields()
    {
    var total = 0;
    var fields = ["new_field1", "new_field2", "new_field3", "new_field4",  "new_field5"];
    var totalfield = "new_total";
    for (var i = 0; i < fields.length; i++)
    {
        if(Xrm.Page.getAttribute(fields[i]).getValue() != null)
        {
            total = total + Xrm.Page.getAttribute(fields[i]).getValue();
        }
    }
    Xrm.Page.getAttribute(totalfield).setValue(total);
    }


    My blog: www.crmanswers.net - CRM Theme Generator

    • Marked as answer by MayBu Wednesday, December 9, 2015 10:44 AM
    Wednesday, December 9, 2015 8:54 AM
  • Hi,

    Post your code and what your field names are. Considering that there is a check for null values it seems as if the field doesn't exist on the form. Just guessing now, but VAL1 and new_field1 and so on most probably doesn't exist.

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    • Marked as answer by MayBu Wednesday, December 9, 2015 10:45 AM
    Wednesday, December 9, 2015 9:48 AM
  • if you still get the same error "Unable to get property 'getValue'of undefined or null reference" the reason is still a wrong name or a missing field.

    The error will be prompted by the line if(Xrm.Page.getAttribute(fields[i]).getValue() != null) because it's the first to have the getValue and the line inside the if condition is the same.

    You need to debug the javascript, open the developer tools and put a breakpoint to see what is happening.

    Remember also that the fields need to be added to the body, fields inside header and footer are null using getAttribute


    My blog: www.crmanswers.net - CRM Theme Generator

    • Marked as answer by MayBu Wednesday, December 9, 2015 10:44 AM
    Wednesday, December 9, 2015 10:39 AM

All replies

  • Hi,

    You can use calculated fields for that, can't you? If not, you need to have a sum function that is called onchange of all five fields, no biggie when you learn to write js for CRM, you should have some examples in the SDK. Holler if you need more help.

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, December 9, 2015 8:24 AM
  • Hi,
    This is an extension to Ricard's reply.
    You can have one function in Javascript and call it onChange of five attributes.
    function sumOfValues()
    {
       var vTotal = 0.0;
       if(Xrm.Page.getAttribute('VAL1').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL1').getValue();
       }
       if(Xrm.Page.getAttribute('VAL2').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL2').getValue();
       }
       if(Xrm.Page.getAttribute('VAL3').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL3').getValue();
       }
       if(Xrm.Page.getAttribute('VAL4').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL4').getValue();
       }
       if(Xrm.Page.getAttribute('VAL5').getValue() != null)
       {
           vTotal = vTotal + Xrm.Page.getAttribute('VAL5').getValue();
       }
       Xrm.Page.getAttribute('TotalField').setValue(vTotal)
    }

    Gopinath


    My blog

    • Marked as answer by MayBu Wednesday, December 9, 2015 10:44 AM
    Wednesday, December 9, 2015 8:43 AM
  • another version of the code:

    function sumFields()
    {
    var total = 0;
    var fields = ["new_field1", "new_field2", "new_field3", "new_field4",  "new_field5"];
    var totalfield = "new_total";
    for (var i = 0; i < fields.length; i++)
    {
        if(Xrm.Page.getAttribute(fields[i]).getValue() != null)
        {
            total = total + Xrm.Page.getAttribute(fields[i]).getValue();
        }
    }
    Xrm.Page.getAttribute(totalfield).setValue(total);
    }


    My blog: www.crmanswers.net - CRM Theme Generator

    • Marked as answer by MayBu Wednesday, December 9, 2015 10:44 AM
    Wednesday, December 9, 2015 8:54 AM
  • Hi Rickard,

    Calculated fields works on save of the form. Isn't it. And yes, i will give it a try with JavaScript examples given by Gopinath & Guido. I will keep you posted. Thanks a lot for your help.

    Wednesday, December 9, 2015 9:05 AM
  • Right the calculated fields work on retrieve, you'll have to go with js.

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, December 9, 2015 9:32 AM
  • Gopinath,

    Thanks, but it looks like - it is not able to retreive the field value (OnChange) Event. This is the error thrown,

    "Unable to get property 'getValue'of undefined or null reference"

    Any clues?

    Thanks!

    Wednesday, December 9, 2015 9:40 AM
  • Guido,

    Same error when i tried with your code too.

    "Unable to get property 'getValue'of undefined or null reference"

    Any ideas? Guess, this has something to do with the execution context

    Thanks!

    Wednesday, December 9, 2015 9:42 AM
  • you wrote the wrong field name or the field is not added to the form

    My blog: www.crmanswers.net - CRM Theme Generator

    Wednesday, December 9, 2015 9:47 AM
  • Hi,

    Post your code and what your field names are. Considering that there is a check for null values it seems as if the field doesn't exist on the form. Just guessing now, but VAL1 and new_field1 and so on most probably doesn't exist.

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    • Marked as answer by MayBu Wednesday, December 9, 2015 10:45 AM
    Wednesday, December 9, 2015 9:48 AM
  • Oops, I'm sure the fields are available in the form and verified the field schema names too. Here's guido's code which i altered just the field schema names,

    function sumCalculate(){
    var total = 0;
    var fields = ["new_corporate", "new_primary", "new_major", "new_linked",  "new_extended", "new_others"];
    var totalfield = "new_total";
    for (var i = 0; i < fields.length; i++)
    {
        if(Xrm.Page.getAttribute(fields[i]).getValue() != null)
      
        {
            total = total + Xrm.Page.getAttribute(fields[i]).getValue();
        }
    }
    Xrm.Page.getAttribute(totalfield).setValue(total);
    
    }
    
    Thanks!

    Wednesday, December 9, 2015 10:10 AM
  • if you still get the same error "Unable to get property 'getValue'of undefined or null reference" the reason is still a wrong name or a missing field.

    The error will be prompted by the line if(Xrm.Page.getAttribute(fields[i]).getValue() != null) because it's the first to have the getValue and the line inside the if condition is the same.

    You need to debug the javascript, open the developer tools and put a breakpoint to see what is happening.

    Remember also that the fields need to be added to the body, fields inside header and footer are null using getAttribute


    My blog: www.crmanswers.net - CRM Theme Generator

    • Marked as answer by MayBu Wednesday, December 9, 2015 10:44 AM
    Wednesday, December 9, 2015 10:39 AM
  • It was my mistake. Mispelled. Apologies. It works awesome now. Thanks Guido & Rickard.
    Wednesday, December 9, 2015 10:43 AM