Executing Predefined Queries in Dynamics CRM using Web API through Scripts

By | May 9, 2016

Introduction:

What do you mean by Predefined Query?

System Views created in the CRM contains an inbuilt query which displays the results satisfying the Query mentioned in the view.

For e.g. “Active Contacts” an OOB system view.

Also, Users can create their views in Advance Find by specifying their criteria’s and save it giving any custom name.

These queries are known as Predefined Queries.

Using Web API we can retrieve and execute these Predefined Queries in the below way:

First in order to execute the Query we require the Query ID which we can retrieve in below manner:

There are be 2 types of Query in general:

  1. SavedQuery: Contains the SystemDefined View for an Entity. These views are stored in SavedQuery entity.
  1. UserQuery: Contains the Advanced Find Views created by the user for an Entity. These

Views are stored in UserQuery Entity.

To get the Query ID of System View the syntax is as shown below:

If it is System View then it is:

GET [Organization URI]/api/data/v8.0/savedqueries?$select=name,savedqueryid&$filter=name eq ‘Active Contacts’

If it is Custom View (User created view) then it is:

GET [Organization URI]/api/data/v8.0/userqueries?$select=name,userqueryid&$filter=name eq Custom Filter View’

Where

GET [Organization URI]: https://myorg.crm.dynamics.com [CRM Server Url]

Code to get the Query Id Variable:

Where

  • viewName = View Name For e.g. “Active Contacts”
  • queryEntity = If it is System View then it is “savedqueries”. For Custom View it is “userqueries”
  • selectOptions = Columns which you want to select For e.g. “select=name,savedqueryid” for System View & “select=name,userqueryid

This functions returns the viewId of the Query record.

Now, we will see how to execute this Query after getting the ViewId:

Syntax:

If it is System View then it is:

GET [Organization URI]/api/data/v8.0/contacts?savedQuery=00000000-0000-0000-00aa-000010001002

If it is Custom View (User created view) then it is:

GET [Organization URI]/api/data/v8.0/contacts?userQuery=77444281-0010-1300-00aa-000010001002

Code to Execute the Query:

Where

  • clientUrl = CRM Server Url
  • entityName = The Entity whose View Id we have found
  • queryType = If it is a System View then queryType = “savedQuery”, For Custom View (user created) it is “userQuery”
  • viewid: ViewId which we retrieved above using retrieveQueryId() method.

Also, we can filter a particular View records based on its parent record.

For e.g.: There is System view “Open Opportunities”. We will filter the records based on a particular Account and retrieve the Open Opportunities related to this record.

Let’s see How we can achieve this below:

  1. Get the “Open Opportunities” view Id using retrieveQueryId() method which is shown above.
  2. After that call this function:
Where:

  • filterentityName: Parent record by which we need to filter the records. For e.g. Accounts in our case.
  • filterentityId: Parent Record Id
  • relationshipName: It is the collection-valued navigation property name. In our case “opportunity_parent_account”
  • queryType = If it is a System View then queryType = “savedQuery”, For Custom View (user created) it is “userQuery”
  • viewid: ViewId which we retrieved above using retrieveQueryId() method.

For Testing purpose you may call the below method on any event of the form:

 Conclusion:

In this way we can execute Predefined Queries using Web API through Scripts.

Lets have a quick glimpse of new exciting features coming up in Maplytics May Release.

Leave a Reply