Dynamics Community Forum Thread Details

Need to retrieve the lookup counts through web api

Team , I need to get the lookup count through web api.

For ex : we have a case entity.In case entity owner A having 20 cases and Owner B having 30 cases.

Now I need to get the count of cases based on the owner lookup.

expected Json

"value":[{"owner":Owner A,"COUNT":20},{"owner":Owner B,"COUNT":30}]

If I try this for getting the status(optionset) count by using /api/data/v9.1/incidents?$apply=groupby((statuscode),aggregate($count%20as%20COUNT)).It will return the count.

"value":[{"statuscode":19090909,"COUNT":45},{"statuscode":2121232,"COUNT":8}]

Hi partner,

1.You could use /api/data/v9.1/incidents?$apply=groupby((ownerid),aggregate($count%20as%20COUNT).

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-data-web-api

2.You can search through Web api with Owner as the search criteria, and the count of results is what you want. (But you could only get only one result of an owner each time).

function GeteCount (executionContext) {
    var formContext = executionContext.getFormContext();
    var currentUserRoleIds = Xrm.Utility.getGlobalContext().userSettings.securityRoles;
    for (i=0; i<currentUserRoleIds.length; i++) {
      var currentRoleUniqueId = currentUserRoleIds[i];
      Xrm.WebApi.retrieveRecord("incident","?$select=subject&$filter=ownerid eq '"+"ownerid"+"'").then(
        function success(result) {
            var count=result.count;
            //..todo set the value to lookup field
  },
        function error (error) {
          Xrm.Navigation.openAlertDialog({ text: error.message });
        }
  );}
    
}