I ran into an interesting requirement this morning that letâ€™s me show off one of my favorite CRM add-on utilities: Stunnware Tools. Letâ€™s cover both.
I needed to create a list of users that were members of a specific security role. Rather than writing an application or designing a SQL query, I opened Stunnware Tools for Microsoft Dynamics CRM so that I could create a query in the FetchXML Wizard.
Using the FetchXML Wizard
The FetchXML Wizard allows you to query CRM by building a FetchXML query. Here is a screen shot of the Designer:
Designing the Query
The following steps were required to created the desired query:
Query the SystemUser entity and return the Full Name of the user.
Include a Linked Entity that links the SystemUser Entity to the SystemUserRoles Entity
Add another Linked Entity from SystemUserRoles to Role. This link will have a filter applied where the Name of the Role is equal to salesperson.
Here is the resulting FetchXML query:
<fetch mapping="logical" count="50" version="1.0"> <entity name="systemuser"> <attribute name="fullname" /> <link-entity name="systemuserroles" from="systemuserid" to="systemuserid"> <link-entity name="role" from="roleid" to="roleid"> <filter> <condition attribute="name" operator="eq" value="salesperson" /> </filter> </link-entity> </link-entity> </entity> </fetch>
Which produces the following result set:
This whole process took me less than 5 minutes because the FetchXML Wizard Query Designer understands the links between CRM Entities and allowed me to quickly select those links and specify the necessary filter to produce the dataset I needed.
The Export to Excel module included in Stunnware Tools allows me to export the above result set to an Excel worksheet.
More About Stunnware Tools
There are two editions:
- The Community Edition: It’s free and contains the Metadata Viewer, FetchXml Wizard and Excel Export.
- The Professional Edition: A subscription-based version with access to all tools of the Community Edition plus the Code Generator for C# and VB.NET, the CRM Help File Generator, additional features of the FetchXml wizard and the Customization Comparer.
Stunnware Tools is probably the most valuable Dynamics CRM add-on that I have. I use it on a weekly, if not daily basis and which makes me a much more productive developer.