How to display total count of related entities in Dynamics without Calculated Fields

Have you used calculated fields to show how many open opportunities that are related to an account or related closed quotes? Perhaps experienced the field is not up to date with current related entities? Or maybe just find the calculated field does not look satisfying?

Counting related entities can easily be solved by using Javascript and the Xrm.WebApi. By retrieving the id of the entity where you want to display the total and then look up the related entity, counting the records and outputting them to a field.

For instance, if you like to show total open opportunities related to an account you can get the account id and use the Xrm.WebApi and the retrieveMultipleRecords method. Link here for documentation: https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/xrm-webapi/retrievemultiplerecords.

When retrieving data from the API you can use OData filtering for retrieving records you specify in the filter. For instance with different states and status or other conditions if you would like to show the total of a more specific search.

When building the filter I found it useful to use the advanced search in Dynamics to build up the filter and then exporting the FetchXML. Then use the field names and values in the xml file in the OData filter query.

Below is how it can look with Javascript and the Xrm.WebApi.

Without name spacing:

// Count Open Opportunities
    async function calculateOpenOpportunities (executionContext) {
				const formContext = executionContext.getFormContext();
        let accountId = formContext.data.entity.getId().replace(/{|}/g, '');
        let count = 0;

        await Xrm.WebApi.retrieveMultipleRecords("opportunity", `?$select=opportunityid&$filter=_parentaccountid_value eq ${accountId} and statecode eq 0`).then(
            function success(result) {
                for (var i = 0; i < result.entities.length; i++) {
                    count++;
                }
            },
            function(error) {
                console.log(error.message);
            }
        );
        formContext.getAttribute("new_totalopenpportunities").setValue(count);
    }

With name spacing

var sdk= window.sdk|| {};

(function() {

// Count Open Opportunities
    this.calculateOpenOpportunities = async function(executionContext) {
				const formContext = executionContext.getFormContext();
        let accountId = formContext.data.entity.getId().replace(/{|}/g, '');
        let count = 0;

        await Xrm.WebApi.retrieveMultipleRecords("opportunity", `?$select=opportunityid&$filter=_parentaccountid_value eq ${accountId} and statecode eq 0`).then(
            function success(result) {
                for (var i = 0; i < result.entities.length; i++) {
                    count++;
                }
            },
            function(error) {
                console.log(error.message);
            }
        );
        formContext.getAttribute("new_totalopenpportunities").setValue(count);
    }
}).call(sdk);

Hope you found it useful!


For More Content See the Latest Posts