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

(1) Share

Report

Posted on by SK-29040614-0

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.

All responses (5)

Answers (1)

Verified answer

Leah Ju Microsoft Employee on at

Like (0)

Report
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 18 on at

Like (0)

Report
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 Microsoft Employee on at

Like (0)

Report

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

Hi，

If so, you need retrieve data in the sub-grid.
--Counting based on acquired status values.

https://community.dynamics.com/blogs/post/?postid=5987162e-1d79-46c1-97b3-3dc5ec4ff4a1

https://medium.com/@furkankaracan/dynamics-365-how-to-get-row-data-from-sub-grids-ccdfbd923426#:~:text=One%20way%20to%20get%20row,a%20collection%20of%20GridRow%20objects.
SK-29040614-0 18 on at

Like (0)

Report
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 Microsoft Employee on at

Like (0)

Report

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:

https://community.dynamics.com/blogs/post/?postid=d755ee5f-e464-4b71-9bbd-172109a6d54a

https://www.crmcrate.com/javascript/how-to-get-the-total-record-count-of-a-grid-in-dynamics-365/

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