CRM 2011 OData Query Designer

43 Replies

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>

43 thoughts on “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

  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

    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)

    Reply ↓
    1. Rhett Clinton Post author

      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

      Reply ↓
      1. bruce

        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!!

      2. bruce

        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

      3. Christophe Trevisani Chavey

        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!

      4. Rhett Clinton MVP Post author

        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

  10. Ravi

    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)

    Reply ↓
    2. Rick

      Rhett,

      I have the same issue and I am using the server name. I do want to point out that I am using CRM Online. Have you tested this with CRM online? I am able to call the same OData service when I write a C# example with no problems. This seems to be a context issue.

      Here’s the exception details:

      [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.60831.0&File=mscorlib.dll&Key=Arg_SecurityException

      [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.60831.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()
      : Server Address –

      Reply ↓
  11. Alan Whitehouse

    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?

    Reply ↓
    1. Rhett Clinton Post author

      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.

      Reply ↓
  13. Alan

    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

    Reply ↓
    1. Rhett Clinton Post author

      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.

      Reply ↓
  14. Alan

    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.

    Reply ↓
  15. Janet

    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.

    Reply ↓
  16. Mohamed Cherif

    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

    Reply ↓

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

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

  19. henk herkelman

    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?

    Reply ↓
  20. Marcus Miller

    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

    Reply ↓
  21. theenerd

    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

    Reply ↓

  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

    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.

    Reply ↓
    1. Rhett Clinton MVP Post author

      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

      Reply ↓
  26. Administrator

    I have install the odata solution and this installs a link in the sitemap. The issue is this is a development environment and I need to export the Sitemap and import the development solution into our Test environment and it errors on import because of oData solution is not included.

    How can I remove or exclude odata (managed solution) form the solution sitemap but not uninstall odata solution? I can successfully export/import the solution into test environment when the profiler is installed, so why oData is causing an issue?

    Reply ↓

Leave a reply to Rhett Clinton Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.