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:
- SavedQuery: Contains the SystemDefined View for an Entity. These views are stored in SavedQuery entity.
- 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:
//Method to retrieve the Query Id
function retrieveQueryId(viewName, queryEntity, selectOptions) {
var functionName = "retrieveQueryId";
var viewId = "";
try {
//Get Client Url
var clientUrl = Xrm.Page.context.getClientUrl();
//Declare new XML HttpRequest Object
var req = new XMLHttpRequest()
//To Retrieve PreDefinedQuery Query
req.open("GET", encodeURI(clientUrl + "/api/data/v8.0/" + queryEntity + "?" + selectOptions + "&$filter=name eq '" + viewName + "'"), false);
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.send();
if (req.status == 200) {
var data = JSON.parse(req.response);
var dat = data.value;
//If record greater than 0 i.e. atleast 1 record found
if (dat.length > 0) {
//If it is System View
if (queryEntity.toLowerCase() == "savedqueries") {
//read the first record
viewId = dat[0].savedqueryid;
}//If it ia an User Query [Advanced Find]
else if (queryEntity.toLowerCase() == "userqueries") {
//Read the first record
viewId = dat[0].userqueryid;
}
}
}
else {
var error = JSON.parse(req.response).error;
}
return viewId;
} catch (e) {
alert(e.message);
}
}
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:
//Method executes the Query and returns Array of Records
function executeQuery(entityName, queryType, viewid) {
var functionName = "executeQuery";
var recordsFromView = null;
try {
//Get Client Url
var clientUrl = Xrm.Page.context.getClientUrl();
//Declare new XML HttpRequest Object
var req = new XMLHttpRequest()
if (viewid == "") {
return;
}
//Sync Operation
req.open("GET", encodeURI(clientUrl + "/api/data/v8.0/" + entityName + "?" + queryType + "=" + viewid), false);
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.send();
//If it is executed successfully
if (req.status == 200) {
var data = JSON.parse(req.response);
recordsFromView = data.value;
}
else {
var error = JSON.parse(req.response).error;
}
return recordsFromView;
} catch (e) {
alert(e.message);
}
}
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:
- Get the “Open Opportunities” view Id using retrieveQueryId() method which is shown above.
- After that call this function:
//Method to return records from View by Filtering it out based on the Parent Record
function executeFilterQuery(filterentityName, filterentityId, relationshipName,queryType, viewId) {
var functionName = "executeQuery";
var recordsFromView = null;
try {
//Get Client Url
var clientUrl = Xrm.Page.context.getClientUrl();
//Declare new XML HttpRequest Object
var req = new XMLHttpRequest()
if (viewId == "") {
return;
}
//Sync Operation
req.open("GET", encodeURI(clientUrl + "/api/data/v8.0/" + filterentityName + "(" + filterentityId + ")/" + relationshipName + "/?" + queryType + "=" + viewId), false);
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.send();
//If it is executed successfully
if (req.status == 200) {
var data = JSON.parse(req.response);
recordsFromView = data.value;
}
else {
var error = JSON.parse(req.response).error;
}
return recordsFromView;
} catch (e) {
alert(e.message);
}
}
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:
//Method called on Load event
function startOperation() {
//For System Views
//Retrieve the View Id for the system view "Active Contacts"
var systemQueryId = retrieveQueryId("Active Contacts", "savedqueries", "$select=name,savedqueryid")
var queryRecords = executeQuery("contacts", "savedQuery", systemQueryId);
//For User Created Views
//Retrieve the View Id for the user created view "User Created Custom View"
var customQueryId = retrieveQueryId("User Created Custom View", "userqueries", "$select=name,userqueryid")
var customQueryRecords = executeQuery("contacts", "userQuery", customQueryId);
//To Execute Views By filtering it based on parent record
//Retrieve the View Id for the system view "Active Contacts"
var filterviewId = retrieveQueryId("Open Opportunities", "savedqueries", "$select=name,savedqueryid");
//Retrieve the Opportunities record that belong to particular Account
var oppRecords = executeFilterQuery("accounts", "D8747EC1-A6F5-E511-80E2-C4346BAC339C", "opportunity_parent_account","savedQuery", filterviewId);
}
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.