OData Query Designer, a free managed solution written by Rhett Clinton, provides a more technical alternative to CRM's Advanced Find feature. It has proven invaluable in my Microsoft Dynamics CRM work, especially when creating form event handlers, so I wanted to introduce it to you via this blog entry.
Installation is very straightforward... just import it as you would any other managed solution. Refresh CRM by pressing F5, and click on Settings. You should now see a section entitled "CRM Tools" containing the entry "OData Query Designer." Click on that to open the main screen.
First, select the Entity which you wish to query. You have full access to all CRM entities, including any custom/third party entities. By default, queries will return all fields of all records in your selected entity. However, it is very easy to create filter criteria. As with the entity list, you will have full access to all fields in your selected entity, along with the standard comparison operators. When entering string/text data values, you do not need to specify the external quotes...those will be added automatically for you.
After adding your filter criteria, click on the "Generate" button near the top of the screen. The following screen shot shows a generated query that will display Contact records located in the cities of Issaquah or Monroe.
By default, generated queries return all fields in the entity. You can easily restrict that to only return specific fields by scrolling down and clicking upon the "Entity Attributes" tab. The fewer fields returned by the query, the better the performance will be, so I recommend specifying only those fields whose values you actually need. The following screen shot demonstrates this by returning only the First Name, Last Name, City, State, Email, and Telephone fields.
(You can also set up relationships using the "One to Many,""Many to One,"and "Many to Many"tabs, although that is beyond the scope of this blog entry.)
In addition to the basic AND/OR, you can group multiple filter criteria together for more complex boolean operations. To do this, click on the leftmost button (with the down arrow) and select the desired filter criteria, then click on the "Group AND" or "Group OR"buttons. The following screenshot demonstrates this technique by grouping together the two city-related filter criteria and adding a Back Office Customer filter criteria.
You can also use this button to delete unwanted filter criteria. The query URLs generated by OData Query Designer can be used in CRM form event handlers. Even in situations where the data will be dynamic rather than hard-coded, the generated URLs can serve as a template. For example, suppose that you want to create an event handler that rolls up information from Opportunity Product records to the parent Opportunity record. This would be attached to the Opportunity Product form's onSave event. The following screen shot shows a query against the Opportunity Product entity, with a filter criteria based upon the (parent) Opportunity field.
In a query against the Opportunity Product entity with a filter criteria based upon the (parent) Opportunity field, the only field that I needed was the Extended Amount, so I specified that upon the Entity Attributes tab. As you can see, OData Query Designer is intelligent enough to detect GUID-type fields and insert the appropriate conversion logic in the query. "opportunity-guid-goes-here" is a placeholder for the actual Opportunity GUID which will be available in the CRM form event handler. Also, note that unlike the earlier filter criteria which involved string and boolean fields, the Opportunity field is an Entity Reference. OData Query Designer recognizes this, and tacks on the "/Id" suffix in the generated query.
I hope that this brief introduction has encouraged you to download and install OData Query Designer, and I am confident that you will find it as useful as I have!
