Home > Blog > How to Update Calculated Fields in Dynamics 365 Using JavaScript On a Form

How to Update Calculated Fields in Dynamics 365 Using JavaScript On a Form

Calculated fields in Dynamics 365 allow users to create fields whose values are automatically determined based on other data in the system. They are a powerful tool for automating calculations, simplifying data entry, and providing real-time insights without needing custom code.

In this blog, we will pinpoint some key functionalities of calculated fields and detail how they can help your business operate more efficiently. Additionally, we will walk through the steps on how to update calculated fields using Javascript on a form.

What are Calculated Fields in Dynamics 365?

A calculated field is a special type of field where the value is dynamically computed using a formula or expression based on other fields in the same record or related records. These fields are easy to create and can perform a variety of mathematical, logical, and string operations in your Dynamics 365 solutions.

Key Functions of Calculated Fields:

1. Perform Real-Time Calculations: Calculated fields allow you to compute values like sums, averages, and percentages automatically.
2. Use Conditional Logic: You can build logic into a calculated field using "if-else" conditions.
3. Referencing Other Fields: Calculated fields can reference multiple fields within the same entity or related entities, allowing for greater flexibility in data processing.
4. Date Calculations: You can use calculated fields to manipulate dates—adding or subtracting days, months, or years. For example, calculate a customer’s next renewal date or track how many days a case has been open.

How to Update Calculated Fields Using JavaScript on a Form

1. Create a scenario where you have a table with a relationship to another table.
2. In my example, I am using the Account table with a sub-grid that shows all related Market Share Analysis records.
3. The Market Share Analysis table captures the Estimated Revenue and Market Share Percentage of a competitor to the Account.
4. On the Account table, there is a rollup field called Total Competitor Estimate Revenue that sums the Estimate Revenue of all related records.
5. In addition, there is a rollup field called Total Competitor Market Share Percentage that sums the Market Share Percentage of all related records.
6. Create a Web Resource that will calculate the two rollup fields automatically when a value is updated.
7. Create the JavaScript code using the following:

   function accountsMainFormOnLoad(executionContext) {
       var formContext = executionContext.getFormContext();
       // get the grid control
       var marketshareSubGrid = formContext.getControl("NAME OF THE SUB_GRID");
       var entityId = formContext.data.entity.getId().replace('{', '').replace('}', '');
       // call the function on grid on-load
       marketshareSubGrid.addOnLoad(function () {
           recalculateRollup(executionContext, "accounts", entityId, 'schema name of the calculated field');
       });
   }
   function recalculateRollup(executionContext, entity, entityId, fieldName) {
       debugger;
       var formContext = executionContext.getFormContext();
   
       var clientUrl = Xrm.Utility.getGlobalContext() ? Xrm.Utility.getGlobalContext().getClientUrl() : null;
       var i = 0;
       // recalculate the rollup field
       fetch(clientUrl + "/api/data/v9.2/" +
           "CalculateRollupField(Target=@p1,FieldName=@p2)?" +
           "@p1={'@odata.id':'" + entity + "(" + entityId + ")'}&" +
           "@p2='" + fieldName + "'").then(
               response => {
                   response.json().then(data => {
                       if (data.error) {
                           console.log(data.error.message);
                       } else {
                           i = 1;
                           var jsonResult = JSON.stringify(data);
                           // refresh the form, if the rollup field is recalculated
                           if (i === 1) {
                               formContext.data.entity.refresh();
                           }
                       }
                   });
               }
           );
   } 
   

8. Replace the green text in the javascript with the following:
   1. NAME OF THE SUB-GRID
      
   2. Schema name of the calculated field = get the schema name of the calculated field on the primary table and replace this text in the javascript code.
      
9. Save the file as a .JS file
10. In a solution, create a web resource and choose the JS file created earlier.
    
11. In your solution, open the Account form from Components > Entities > Forms
12. Click Form Properties
13. On the Events tab, click add
14. Add the new web resource created earlier
15. Under Event Handler, click Add
16. Choose the new library from the list
17. Enter the Function name as show in the image below
18. Click the Pass execution context as first parameter
    
19. Click OK
    

Want to Learn More? Get in touch with Stoneridge!

Our experts can help you find ways to optimize your business technology solutions and streamline processes so you and your team can work smarter and save time. Get in touch with us for more information. 

Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.