If you are here, you are probably looking for a solution to solve this kind of error: „Aggregate fields cannot exist along with regular fields on query data source. Remove the regular fields before adding aggregate fields.„. Hey Visual Studio, it is certainly not true. We were able to do that kind of operation on the previous versions of Microsoft Dynamics AX, even though there were also some problems with mixing regular fields with aggregate fields.
There is a brilliant way to get through this by editing the query XML file in the model’s path. If you are here because of Microsoft Dynamics AX2012, you can the same by exporting the *.xpo, editing and importing the corrected file.
Step by step.
- Make sure that the whole query is valid, all the data sources are added and all the „group by” properties are set properly.
- Find the XML representation of your file. It should be probably placed in the path, which looks like this: C:\AOSService\PackagesLocalDirectory\[your_model]\[your_model]\AxQuery\[your_query].xml
- When you take a look at your data source’s node, which is located between <AxQuerySimpleEmbeddedDataSource> tags. All the fields are put between in its child section: <Fields> and </Fields>. Regular fields are created with <AxQuerySimpleDataSourceField> and you should be able to see them if you created them in Visual Studio. All the others have the additional attribute, which makes them an aggregate field. The „sum” field is not created with <AxQuerySimpleEmbeddedDataSource>, but with <AxQuerySimpleEmbeddedDataSource i:type=”AxQuerySimpleDataSourceFieldSum” xmlns=””>. The „max” field will be <AxQuerySimpleEmbeddedDataSource i:type=”AxQuerySimpleDataSourceFieldMax” xmlns=””> and so on with count, min or avg.
- So if you want to add an aggregate field next to the regular field, just put this code between <Fields> and </Fields>.
<AxQuerySimpleDataSourceField i:type="AxQuerySimpleDataSourceFieldSum" xmlns="">
Note: It may be necessary to copy the file outside the Dynamics’ directory, modify the field and copy the modified field into the Dynamics’ directory.
I hope it will help you! 👏
If you think that there is a better way to solve this problem, please send me an email through „Contact” form or leave a comment, I will keep this post updated!