CRM 2011 OData Query Designer

This project is superceded by the new application Dynamics XRM Tools which includes improved features http://dynamicsxrmtools.codeplex.com/

 

I’ve developed the CRM 2011 OData Query Designer as a Silverlight 4 application that is packaged as a Managed CRM 2011 Solution. This tool allows you to build OData queries by selecting filter criteria, select attributes and order by attributes. The tool also allows you to Execute the query and view the ATOM and JSON data returned.  Once the managed solution is imported into CRM 2011 it is accessible from the Settings area under Customizations .

You can download the CRM 2011 OData Query Designer  from codeplex.

[UPDATE]

Latest Update 8th March 2011
1. Added dropdown list of available OptionSet values for a filter when of type OptionSet, Boolean, Status and State.
2. Attribute lists now show the UserLocalizedLabel Display Name instead of Schema Name.
3. Resolved issues with some attribute types not generating correctly when used in a filter.

Has this Tool saved you time…

If you have benefited from downloading and using this tool, please feel free to donate an amount of your choice. Thankyou

GBP – Donate via PayPal - GBP>

USD – Donate via PayPal - USD>

Euro – Donate via PayPal - Euro>

About these ads

About Rhett Clinton MVP
Dynamics CRM MVP

41 Responses to CRM 2011 OData Query Designer

  1. Pingback: CRM 2011 OData Query Designer (#CRM2011) « MSCRM Bing'd : : crm

  2. Pingback: CRM 2011 OData Query Designer (#CRM2011) « MSCRM Bing'd – _Hosted UniverseThe reference for the cloud

  3. Pingback: CRM 2011 – OData Query tester « Hosk's Dynamic CRM 2011 Blog

  4. Hosk says:

    Brilliant tool. I was using an excel spread to record and build up queries.

    pingback

    http://crmbusiness.wordpress.com/2011/03/06/crm-2011-odata-query-tester/

  5. Caleb Harrelson says:

    Thank you for the wonderfully useful tool.

  6. Pingback: CRM 2011 – The Best Weekly CRM articles – March week 2 « Hosk's Dynamic CRM 2011 Blog

  7. Pingback: Wonderful tools to learn Linq and oData Query for CRM 2011 « Nishant Rana's Weblog

  8. bruce says:

    Hey Rhett, This doesnt work for me …. i get the following error message on load:

    An exception occurred during the operation, making the result invalid. Check InnerException for exception details.

    at System.ComponentModel.AsyncCompletedEventArgs.RaiseExceptionIfNecessary()
    at CRM.Tools.ODataQueryDesigner.CrmSdk.ExecuteCompletedEventArgs.get_Result()
    at CRM.Tools.ODataQueryDesigner.Readers.MetadataReader.ExecuteRetrieveEntities_ExecuteCompleted(Object sender, ExecuteCompletedEventArgs response)
    at CRM.Tools.ODataQueryDesigner.CrmSdk.OrganizationServiceClient.OnExecuteCompleted(Object state)

    • Hi Bruce,

      There has been a couple of users experiencing a cross-domain issue when accessing the tool where by the url used to access CRM (and then the tool) does not corresspond to the url returned by the Client API Xrm.Page.context.getServerUrl(). Have you tried accessing the tool from the server and/or from the client? Also what address did you use to access the server, local host, server name, IP, domain.server.com, etc..?

      Thanks,
      Rhett

      • bruce says:

        Hey Rhett,
        THanks for the quick response..you were right..i was using localhost but as soon as i put it my server name, it worked :) Cheers for that…fantastic tool!!

      • bruce says:

        Hey Rhett,
        On a side note…do you know how you could possibly get the int value in the global OptionSet..e.g. activitypointer_activitytypecode This is the option set which keeps the values of default and custom activity entity values(e.g. phone,task, custom_ent, etc).. I can get the ActivityTypeCode from the entity e.g. new_ent but i cant get the value e.g 10,000 .. Reason i need this is for a custom action button (i.e open up custom activity from a form) , the parameter is the integer value:

        So sorry if this post is inappropriate or in the wrong place
        Cheers bruce

      • Hi,
        Very interesting tool, good job!

        For avoid this URL problem, I recommend to not use the CRM API Xrm.Page.context.getServerUrl() for getting CRM’s URL, instead use this:

        var serverUrl = document.location.protocol + “//” + document.location.host + “/” + Xrm.Page.context.getOrgUniqueName();

        Good luck!

      • Hi Christophe, thanks for your feedback. I have a new getServerUrl function that is currently being used by this tool and hopefully it has no more problems with cross domain issues. With regards your example, you will have problems with CRM Online and IFD deployment as the org name is apart of the host name not appended.

        Cheers,
        Rhett

  9. A stunning piece of work – excellent job!

  10. Ravi says:

    Getting this error after importing the solutiion and launching the designer. Please let me know what needs to be done..

    [Async_ExceptionOccurred]
    Arguments:
    Debugging resource strings are unavailable. Often the key and arguments provide sufficient information to diagnose the problem. See http://go.microsoft.com/fwlink/?linkid=106663&Version=4.0.50917.0&File=System.dll&Key=Async_ExceptionOccurred

    at System.ComponentModel.AsyncCompletedEventArgs.RaiseExceptionIfNecessary()
    at CRM.Tools.ODataQueryDesigner.CrmSdk.ExecuteCompletedEventArgs.get_Result()
    at CRM.Tools.ODataQueryDesigner.Readers.MetadataReader.ExecuteRetrieveEntities_ExecuteCompleted(Object sender, ExecuteCompletedEventArgs response)
    at CRM.Tools.ODataQueryDesigner.CrmSdk.OrganizationServiceClient.OnExecuteCompleted(Object state)

  11. I love the concept of this tool. I am trying to use it within CRM Online and am getting the following error when I press the generate button:

    ***
    [Arg_SecurityException]
    Arguments:
    Debugging resource strings are unavailable. Often the key and arguments provide sufficient information to diagnose the problem. See http://go.microsoft.com/fwlink/?linkid=106663&Version=4.0.60129.0&File=mscorlib.dll&Key=Arg_SecurityException

    at System.Net.Browser.ClientHttpWebRequest.InternalEndGetResponse(IAsyncResult asyncResult)
    at System.Net.Browser.ClientHttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
    at CRM.Tools.ODataQueryDesigner.QueryDesignerViewModel.c__DisplayClass1a.c__DisplayClass1c.b__19()
    ***

    Any suggestions?

    • Yes I’ve found an issue with executing a get request with the Silverlight client on CRM Online in that it wants to redirect to the live sign in page but then fails (A Silverlight HttpRequest problem). It shouldn’t actually do this anyhow but if you use Fiddler to check the traffic you can see what is actually happens. At the moment, you have to select Show in Browser to view the results of the query.

  12. Actually meant the “execute” button, not the “generate” button.

  13. Alan says:

    Rhett,

    So show in browser works. However, one question for you, If I choose “ContactSet” for the EntitySet and then select “Select All” from the Attributes when I view in Browser all I see is the Contact’s name. Should I also be seeing the other attributes? Sorry if this is a dumb question but I am new to Odata and CRM.

    Alan

    • Did you Generate after selecting the Select all so the * character shows in the query URL? I can test it tomorrow but you can also try selecting individual attributes and see what you get. Maybe it depends on what data is available. I’ll test it tomorrow too to see what you’re getting.

  14. Alan says:

    I double checked. Seems like the sort works like it should, but no matter what attributes I select (all or individual) in the browser all I see is the Contact full name and none of the other fields.

  15. Janet says:

    I’m using 2011 Online as well so had same issue. I found if I went to Internet Options, Content, Feeds Settings and untick the Turn on Feeds Reading view, the view in browser is not the full xml. Not very pretty but you can see all the information it is returning.

  16. Mohamed Cherif says:

    I have one question, how do i create complex OData queries that have multi level expands. I mean i need to create a query which gets data from an entity and go 5 levels down

  17. Pingback: Working With REST Results in CRM 2011 « Gareth Tucker's Microsoft CRM Blog

  18. Pingback: CRM2011, JavaScript, OData -Kevin's Mocha

  19. CRM2011: using OrganizationData.svc in visual studio 2010

    We are using a hosted crm2011 environment with using the federal service (ADFS) to authenticate with username and password.
    We want to use the following webservice:

    https://yourdomain/XRMServices/2011/OrganizationData.svc

    How to use this within visual studio 2010? Is there an example to registrate this service (with authentication) and add a record in the accoount entity of mscrm2011?

  20. Marcus Miller says:

    Dear Rhett,

    I’ve successfully installed your odata query designer in my Dynamics 2011 OnPremise. Unfortunately when trying to open the tool it always wants to download and install ms silverlight although it is already installed. What can I do to get it run?

    Regards,

    Marcus

  21. theenerd says:

    Hi Rhett,

    please where do i find the steps to know how to use the oData query Designer, i ‘ve download the zip file but on the site i found no infos about how to start the designer

    Thanks in advance for your response

  22. Pingback: CRM 2011 – Getting started with oData « Hosk's Dynamic CRM 2011 Blog

  23. Pingback: Hosk's Dynamic CRM 2011 Blog

  24. Pingback: Introduction to OData in Microsoft Dynamics CRM 2011 | Metaphorix

  25. Nikhil says:

    Hi Rhett,

    I downloaded and successfully imported the oData query designed tool. I have seen some samples where you can use the tool to query data from one or more CRM entities. But I can’t find any examples that use complex queries with multiple joins & order by, group by clause to retrieve data. Is this something that’s possible using the tool or in general using the Organization.svc services ? I would appreciate if you can shed some light on this.

    Thanks in advance for your response.

    • Hi,

      The tool allows you to choose attributes to retrieve, including from related entities but the condition selection area does not currently support choosing from related entities. You can manually write this simply by prefixing the attribute name with the associated entity schema name. You need to add the additional join section as seen when selecting related entity attributes but should work. Start by creating a query and select associated entity fields, then add a condition using the same prefix.attribute combinations.

      Cheers,
      Rhett

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: