Skip to main content

Notifications

Microsoft Dynamics CRM forum
Suggested answer

Count number of sub-grid records, where status is completed and set count in a field

Posted on by 10
I was wondering if anyone could help me out with creating a java script for a customization that I am trying to put together.
 
I have the case entity, where it has a sub-grid that points to a custom entity named /Reviews/. When a specific type of case is created, the sub-grid will be auto-populated with a set number of records (will always have 7 records).  
 
I am trying to create a java script that would count the number sub-grid records that have a /completed/ status and set that count in a custom field in the case entity. This script would be initiated on the onLoad, so that the count will be shown in the field.
 
Any help that you can provide with the creation of the java script would be much appreciated.
  • Suggested answer
    Leah Ju Profile Picture
    Leah Ju Microsoft Employee on at
    Count number of sub-grid records, where status is completed and set count in a field
    Hi,
    It's mainly a matter of these two parts:
    1.There is no addCustomFilter() function in grid web api.
    2.You should use value of 'completed' status as condition, and total should '+1' instead of '+value' if the condition is met.
    My example:
    function countCompletedReviews(executionContext) {
    
        var formContext = executionContext.getFormContext();
    
        // Get the subgrid control by its name
        var subgrid = formContext.getControl("Subgrid_new_1");
    
        // Check if the subgrid is loaded
        if (subgrid && subgrid.getGrid) {
    
            // Get the rows of the subgrid
            var rows = subgrid.getGrid().getRows();
    
            // Initialize a variable to store the total count
            var total = 0;
    
            // Loop through each row
            for (var index = 0; index < rows.getLength(); index++) {
                // Get the data of the row
                var data = rows.get(index).getData();
    
                // Get the entity of the row
                var entity = data.getEntity();
    
                // Get the status attribute of the entity
                var status = entity.attributes.get("statuscode");
    
                // Check if the attribute value is completed , 565930001 is value of completed status
                if (status && status.getValue() == 565930001) {
    
                    // Add the number to the total
                    total = total + 1;
                }
            }
            // populate filed
            formContext.getAttribute("cre80_completedreview").setValue(total);
        }
    }
     
  • SK-29040614-0 Profile Picture
    SK-29040614-0 10 on at
    Count number of sub-grid records, where status is completed and set count in a field
    Thanks.
     
    Here is the script that I had created, using the link below, but I can't seem to get the total count populated in the "total count field". Can you please take a look at the code and let me know if you see the issue?
     
    function CompletedObligationReviewCount (executionContext) {
    
        var formContext = executionContext.getFormContext();
        
        // Get the subgrid control by its name
        var subgrid = formContext.getControl("Obligation_Review_Record_Subgrid");
      
        // Check if the subgrid is loaded
        if (subgrid && subgrid.getGrid) {  
          
          // Get the rows of the subgrid
          var rows = subgrid.getGrid().getRows();
          var completedRecordsFilter = "<filter type='and'><condition attribute='sksysdev_reviewstatus' operator='eq' value='2'/></filter>";
    
          formContext.getControl(subgrid).getGrid().addCustomFilter(completedRecordsFilter, entity);
          
          // Initialize a variable to store the total completed record counts
          var totalcount = 0;
          
          // Loop through each row
          for(var index=0; index<rows.getLength(); index++)
            
            // Get the data of the row
            var data = rows.get(index).getData();
            
            // Get the entity of the row
            var entity = data.getEntity();
            
            // Get the review status attribute of the entity
              var statuscounts = entity.attributes.get("sksysdev_reviewstatus");
            
            // Check if the attribute has a value      
            if (statuscounts && statuscounts.getValue()) {
              
              // Add the value to the total
              totalcount += statuscounts.getValue();
            }
          };
          
          // Display the total in the Obligation Review Record Count field (under Case)
          formContext.getAttribute("sksysdev_obligationreviewrecordcount").setValue(totalcount);
    }
     
  • Suggested answer
    Leah Ju Profile Picture
    Leah Ju Microsoft Employee on at
    Count number of sub-grid records, where status is completed and set count in a field
  • SK-29040614-0 Profile Picture
    SK-29040614-0 10 on at
    Count number of sub-grid records, where status is completed and set count in a field
    Hi Leah.
     
    Thank you for your response. The info that you had provided, allowed me to get the total number records in the subgrid. How would I get the record count of those records that have a status as "Completed"? Right now, I have about 3 records that are completed out of the 7 that are listed.

    I created the following code for this action, but I was unable to get it to work. Basically, I wanted the code to run and enter the number of completed records in the sksysdev_obligationreviewrecordcount field.


    function CompletedRecordCount (executionContext) {
    
        var formContext = executionContext.getFormContext();
        var completedRecordsFilter = "<filter type='and'><condition attribute='sksysdev_reviewstatus' operator='eq' value='2'/></filter>";
    
        try {
            setTimeout(function () {
    
                if (formContext != null && formContext != undefined && formContext.getControl("Obligation_Review_Record_Subgrid") != null && formContext.getControl("Obligation_Review_Record_Subgrid") != undefined) {
    
    
                    var totalcount = formContext.getControl("Obligation_Review_Record_Subgrid").getGrid().addCustomFilter(completedRecordsFilter, "sksysdev_obligationreview").getTotalRecordCount();                        
    
                    
                    formContext.getAttribute("sksysdev_obligationreviewrecordcount").setValue(totalcount);
    
                }
    
            }, 3500);  // You can increase some more time if its not works then
        }
    
        catch (e) {
            Xrm.Utility.alertDialog(functionName + "Error: " + e.message || e.description);
        }
    }

  • Suggested answer
    Leah Ju Profile Picture
    Leah Ju Microsoft Employee on at
    Count number of sub-grid records, where status is completed and set count in a field
    Hi,
    You need to use getTotalRecordCount() function:
    The following links provide details:

    I hope you can mark my answer verified if it answer your question! If you have any questions, please feel free to contact me.
    Regards,
    Leah
     

Helpful resources

Quick Links

Can you answer this forum question?

You could make someone's day!

Community Newsletter - May 2024

Kudos to our community stars!

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 283,632 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 224,528 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Featured topics

Product updates

Dynamics 365 release plans