Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM forum / Count number of sub-gr...
Microsoft Dynamics CRM forum
Unanswered

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

(1) ShareShare
ReportReport
Posted on by SK-29040614-0 12
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.
Categories:
Development / Customization / SDK
  • 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 12 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);
}
     
  • 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 12 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);
    }
}

  • 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

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,584 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,864 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans