“Ordinary people think merely how they will spend their time, a man of intellect tries to use it.” – Schopenhauer
I had a tricky problem where I need to select a lot of fields from an entity which was riddled with duplicates.
The oData query was bringing 1525 rows but after getting rid of the duplicates I was left with 25 rows!
OData has no distinct
So why was the OData query bringing back so many rows because there is no distinct. So the code was retrieving lots of data, it was then removing the duplicates. It was a lose/lose scenerio
- longer retrieve with more data
- more time spent on getting rid of the extra data we didn’t need
I needed to clarify is there is no way to retrieve a distinct set using OData. I looked on the internet and SDK but there is no distinct function with OData
A Developer mentioned FetchXML has a distinct field, I could change the query from OData to FetchXML and be able to filter the records returned.
I checked with Advanced find and you can’t set the distinct field attribute.
FetchXML has a number of aggregrate functions which are listed on this page – Use FetchXML aggregation
The following aggregate functions are supported:
- count(attribute name)
How to Generate the FetchXML
So I did an Advanced find, copied the xml and found in Tanguy Toolbox XRMToolBox there is a fetchXML Tester.
I recommend the FetchXML Builder, which you can download and then will work with the XRMToolBox
FetchXMLBuilder can be downloaded here
The FetchXML Tester will run any fetchXML you have generated from an Advanced find, once you have the core FetchXML you can then edit and change it but it’s quicker to use Advanced Find to get something to start with.
The FetchXMLBuilder is better for creating new FetchXML queries because it has a great UI to allow you to build up a query.
It’s a bit like the Dynamics CRM OData Query Designer but for FetchXML. I used it recently to query when a certain Plugin assemblies had been uploaded
This is an example of the FetchXML tester with the Distinct set to true
<fetch distinct='true' mapping='logical' aggregate='false'> <entity name='account'> <attribute name='attribute name' alias='alias name'/> </entity> </fetch>"
Here is the example I created
Tools can help
I was lecturing/Advising a junior developer who had a similar requirement. He was about to manually create a big soap command to run the fetchXML.
Instead of doing it yourself, see if there is a framework or tool to help you. Find a framework first, which will do all the nasty donkey work for you of creating the soap messages.
Microsoft Dynamics CRM has lots of tools, solution and frameworks created by the CRM community, it’s often worth looking for a tool/framework to see if someone has resolved the problem.
I have reviewed lot of tools here
For this problem I needed a framework instead of a tool, I narrowed it down to two
Both could have done the job but I chose XRMServiceToolkit because it had more followers on CodePlex and seemed easier to use. I had experience using it before, it’s always easier to do something when you have got it working before.
The XRMServiceToolkit looked a more complete solution.
There are lots of alternatives in Microsoft Dynamics CRM and the skill of a good CRM Developer is choosing the right customization for the job. Choosing the right customizations is often understanding the limitations of each type of customization.