FetchXML Aggregate Function

[dantedantas]

Hello,

Please, could someone help me with a query with aggregate function?

The query returns the error below:

Caught exception: 
RetrieveMultiple (FetchXML) request failed: Client error: `GET https://smartstream.crm4.dynamics.com/api/data/v9.0/incidents?fetchXml=%3Cfetch+mapping%3D%27logical%27+distinct%3D%27false%27+aggregate%3D%27true%27+%3E%0A++++%3Centity+name%3D%27incident%27+%3E%0A++++++++%3Cattribute+name%3D%27ownerid%27+alias%3D%27ownerid%27+groupby%3D%27true%27+%2F%3E%0A++++++++%3Cattribute+name%3D%27prioritycode%27+alias%3D%27prioritycode%27+groupby%3D%27true%27+%2F%3E%0A++++++++%3Cattribute+name%3D%27statuscode%27+alias%3D%27statuscd%27+groupby%3D%27true%27+%2F%3E%0A++++++++%3Cattribute+name%3D%27ownerid%27+alias%3D%27ownerid_count%27+aggregate%3D%27countcolumn%27+%2F%3E%0A++++++++%3Cfilter+type%3D%27and%27+%3E%3Ccondition+attribute%3D%27createdon%27+operator%3D%27this-week%27+%2F%3E%3C%2Ffilter%3E%0A++++++++%3Cfilter+type%3D%27and%27+%3E%0A+++++++++++%3Ccondition+attribute%3D%27ownerid%27+operator%3D%27eq%27+value%3D%214258157AC-225-E478-A254-09589AB81647%27+%2F%3E%0A++++++++%3C%2Ffilter%3E%0A++++++++%3C%2Fentity%3E%0A++++%3C%2Ffetch%3E` 
resulted in a `400 Bad Request` response with the following message: `The property provided was of type **System.Int32**, when the expected was of type **Microsoft.Xrm.Sdk.EntityReference**` 

{"code":8,"message":"Undefined variable: sysaccounts","file":"E:\\Tools\\xampp\\htdocs\\incidentreportDEV\\CRMIncidentStat.php","line":288}
{"code":8,"message":"Trying to get property 'Entities' of non-object","file":"E:\\Tools\\xampp\\htdocs\\incidentreportDEV\\CRMIncidentStat.php","line":288}
{"code":2,"message":"Invalid argument supplied for foreach()","file":"E:\\Tools\\xampp\\htdocs\\incidentreportDEV\\CRMIncidentStat.php","line":288} 

If I run the query diretly in the XrmToolBox it works fine and give me the exepected result.

Please, could someone help me to find out what is happening and the solution?

Thank you/Gracias/Danke/Obrigado/Dziękuję

Dante Dantas

[georged]

Unfortunately, we have the same issue here as Power Automate connector for Dataverse does. We expect fetchxml to return collection of entities but for aggregates it's not the case. Try building fetchxml starting from systemuser table and aggregating incidents using linked-entity element to link the two. That way it would return systemuser records plus aggregate of cases per user.

The other option is to use raw web api client and craft OData instead of using fetchxml.

[dantedantas]

Hi @georged,
thank you for the feedback.

Maybe could you share with me an example of the last option that you wrote above?
"The other option is to use raw web api client and craft OData instead of using fetchxml."

Thank you very much.

[georged]

@dantedantas

we do not have any ready to go samples yet but our OData client uses the same constructs as Dataverse Web API and they have plenty of examples.