Build queries with QueryExpression || Use the QueryExpression class in Dynamics 365

Build queries with QueryExpression:

1. A query expression is used for single-object searches. For example, you can create a search to return all accounts that match certain search criteria. 

2. The QueryBase class is the base class for query expressions. There are three derived classes: QueryExpression, QueryByAttribute, and FetchExpression. 

• The QueryExpression the class supports complex queries. 
• The QueryByAttribute class is a simple means to search for entities where attributes match specified values.
• The third derived class, FetchExpression is used with FetchXML, the proprietary Dataverse query language, can be used to perform some queries by using XML-based queries.

Query expressions are used in methods that retrieve more than one record, such as the IOrganizationService.RetrieveMultiple method.

Generate FetchXML queries Using Advanced Find.

1. Advanced Find is a very rich feature of Dynamics 365, if you are already familiar with then just go ahead and start querying else you can access it by clicking the filter symbol in the top right navigation bar of your Dynamics 365 instance.

2. A window will pop up that will allow you to build a query using a WYSIWYG editor. Once you have defined a query you can click the Download Fetch XML button in the ribbon to get a file containing the query that can be used in your code.

3. Open the downloaded file with notepad and the query will look like the one below.
 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="account">
    <attribute name="name" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="startedon" descending="true" />
    <filter type="and">
      <condition attribute="name" operator="like" value="C%" />
    </filter>
  </entity>
</fetch>

4. Now that we have a FetchXML query you can begin working.

That's it... Thank You...

Use FetchXml queries with Web API - JavaScript

1. FetchXML is a proprietary query language that provides capabilities to perform aggregation. 

2. You can pass URL encoded FetchXML as a query to the entity set corresponding to the root entity of the query using the fetchXml query string parameter to return the results from the Web API. 

3. Generate FetchXML queries through Advanced Find.

In the below example, FetchXML is formatted to be used by the Web API service endpoint. You can either do it by storing the FetchXML in a variable and encoding the string with the encodeURI function native to JavaScript or do it directly as shown below. 

Click here  to use FetchXML to query data in the plugin.

var parameter = {};

parameter["new_opportunityid"] = id.toString().replace("{", "").replace("}", "");

var fetchxml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +

            "<entity name='new_academiccontent'>" +

            "<attribute name='new_academiccontentid' />" +

            "<attribute name='new_name' />" +

            "<attribute name='statecode' />" +

            "<order attribute='new_name' descending='false' />" +

            "<filter type='and'>" +

            "<condition attribute='new_opportunityid' operator='eq' uitype='opportunity' value='{" + parameter["new_opportunityid"] + "}' />" +

            "</filter>" +

            "</entity>" +

            "</fetch>";

 

        var query = "new_academiccontents?fetchXml=" + encodeURI(fetchxml);

Xrm.WebApi.retrieveMultipleRecords("new_academiccontent", query).then( function success(result) { for (var i = 0; i < result.entities.length; i++) { console.log(result.entities[i]); } // perform additional operations on retrieved records }, function (error) { console.log(error.message); // handle error conditions } );

References: MS Docs: https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/xrm-webapi/retrievemultiplerecords

Thank you ...

Keep Learning...

Happy CRMing...

FetchXML query to a query expression

Convert a FetchXML query to a query expression with the FetchXmlToQueryExpressionRequest message.

// Create a Fetch query that we will convert into a query expression.

var fetchXml = @"<fetch mapping='logical' version='1.0'> <entity name='opportunity'> <attribute name='name' /> <filter> <condition attribute='estimatedclosedate' operator='next-x-fiscal-years' value='3' /> </filter> <link-entity name='account' from='accountid' to='customerid'> <link-entity name='contact' from='parentcustomerid' to='accountid'> <attribute name='fullname' /> <filter> <condition attribute='address1_city' operator='eq' value='Hyderabad' /> <condition attribute='address1_stateorprovince' operator='eq' value='Telangana' /> </filter> </link-entity> </link-entity> </entity> </fetch>";

// Run the query with the FetchXML. var fetchExpression = new FetchExpression(fetchXml); EntityCollection fetchResult = _serviceProxy.RetrieveMultiple(fetchExpression); Console.WriteLine("\nOutput for query as FetchXML:"); DisplayOpportunityQueryResults(fetchResult); // Convert the FetchXML into a query expression. var conversionRequest = new FetchXmlToQueryExpressionRequest { FetchXml = fetchXml };

var conversionResponse = (FetchXmlToQueryExpressionResponse)_serviceProxy.Execute(conversionRequest); // Use the newly converted query expression to make retrieve multiple // request to Microsoft Dynamics CRM. QueryExpression queryExpression = conversionResponse.Query; EntityCollection result = _serviceProxy.RetrieveMultiple(queryExpression); // Display the results. Console.WriteLine("\nOutput for query after conversion to QueryExpression:"); DisplayOpportunityQueryResults(result);

Note: To work, You must be connected to the server to get an IOrganizationService interface.

Thank you...

Keep Learning...

Happy CRMing !!!

Dynamics 365 Use FetchXML to query data

 Use FetchXML to construct a query in Plugin, Console App, C#, and Workflow

1. FetchXML is a proprietary query language that is used in Microsoft Dataverse.

2. A FetchXML query can be executed by using either the Web API or the Organization service.

3. To execute a FetchXML query, you must first build the XML query string. After you create the query string, use the IOrganizationService.RetrieveMultiple method to execute the query string.
4. This FetchXML query can be used in C# code, Plugin, Custom Workflow, and Console App.

5. For this to work correctly, you must be connected to the server to get an IOrganizationService interface.

Here in this blog, I'm going to show using organization service 

Click here to convert fetchxml query to the query expression

Use the IOrganizationService.RetrieveMultiple method passing a FetchExpression where the Query property contains the FetchXml query.

The below code shows how to execute a FetchXML query using the Organizations Service:

// Retrieve all accounts owned by the user with read access rights to the accounts and // where the last name of the user is not Kumar.

string fetchxml = @"<fetch mapping='logical'> 

                      <entity name='account'>  

                        <attribute name='accountid'/>  

                        <attribute name='name'/>  

                          <link-entity name='systemuser' to='owninguser'>  

                            <filter type='and'>  

                            <condition attribute='lastname' operator='ne' value='Kumar' />  

                            </filter>  

                          </link-entity>  

                       </entity>  
                     </fetch> ";

EntityCollection result = _service.RetrieveMultiple(new FetchExpression(fetchxml));

foreach (var c in result.Entities) { System.Console.WriteLine(c.Attributes["name"]); }

Refer to MS Docs for more details.

Thank you ...
Happy CRMing...

Keep Learning!!!

Dynamics 365 list of entities that do not use the default Status and Status Reason values

List of entities that do not use the default Status and Status Reason values

Click here to find more entities' status and status reason list and their values. 

Some more references:

Status and Status Reason   Dynamics 365 Javascript Useful Codes  Start with basic plugin writing, debugging  Learn how to create console app step by step    Most frequently used code to hide/show button on the ribbon

The below table shows default status and status reason values:

Entity	Status	Status Reason
Activity	0:Open	1:Open
	1:Completed	2:Completed
	2:Canceled	3:Canceled
	3:Scheduled	4:Scheduled
Appointment	0:Open	1:Free
		2:Tentative
	1:Completed	3:Completed
	2:Canceled	4:Canceled
	3:Scheduled	5:Busy
		6:Out of Office
Article	1:Draft	1:Draft
	2:Unapproved	2:Unapproved
	3: Published	3: Published
Authorization Server	0:Active	1:Enabled
	1:Inactive	2:Disabled
Bulk Delete Operation	0:Ready	0:Waiting For Resources
	1:Suspended	10:Waiting
		11:Retrying
		12:Paused
	2:Locked	20:In Progress
		21:Pausing
		22:Canceling
	3:Completed	30:Succeeded
		31:Failed
		32:Canceled
Campaign	0:Active	0:Proposed
		1:Ready To Launch
		2:Launched
		3:Completed
		4:Canceled
		5:Suspended
	1:Inactive	6:Inactive
Campaign Activity	0:Open	1:Proposed
		0:In Progress
		4:Pending
		5:System Aborted
		6:Completed
	1:Closed	2:Closed
	2:Canceled	3:Canceled
Campaign Response	0:Open	1:Open
	1:Closed	2:Closed
	2:Canceled	3:Canceled
Case	0:Active	1:In Progress
		2:On Hold
		3:Waiting for Details
		4:Researching
	1:Resolved	5:Problem Solved
		1000:Information Provided
	2:Canceled	6:Canceled
Case Resolution	0:Open	1:Open
	1:Completed	2:Closed
	2:Canceled	3:Canceled
Column Mapping	0:Active	1:Active
Contract	0:Draft	1:Draft
	1:Invoiced	2:Invoiced
	2:Active	3:Active
	3:On Hold	4:On Hold
	4:Canceled	5:Canceled
	5:Expired	6:Expired
Contract Line	0:Existing	1:New
	1:Renewed	2:Renewed
	2:Canceled	3:Canceled
	3:Expired	4:Expired
Data Import	0:Active	0:Submitted
		1:Parsing
		2:Transforming
		3:Importing
		4:Completed
		5:Failed
Discount List	0:Active	100001:Active
	1:Inactive	100002:Inactive
Duplicate Detection Rule	0:Inactive	0:Unpublished
		1: Publishing
	1:Active	2:Published
Email	0:Open	1:Draft
		8:Failed
	1:Completed	2:Completed
		3:Sent
		4:Received
		6:Pending Send
		7:Sending
	2:Canceled	5:Canceled
Fax	0:Open	1:Open
	1:Completed	2:Completed
		3:Sent
		4:Received
	2:Canceled	5:Canceled
Goal	0:Active	0:Open
	1:Inactive	1:Closed
		2:Discarded
Goal Metric	0:Active	0:Open
	1: Inactive	1:Closed
Import Data	0:Active	0:Active
Import Entity Mapping	0:Active	1:Active
Import Log	0:Active	0:Active
Import Source File	0:Active	0:Submitted
		1:Parsing
		2:Transforming
		3:Importing
		4:Completed
		5:Failed
Invoice	0:Active	1:New
		2:Partially Shipped
		4:Billed
		5:Booked(applies to services)
		6:Installed(applies to services)
	1:Closed(deprecated)	3:Canceled(deprecated)
		7:Paid in Full (deprecated)
	2:Paid	100001:Complete
		100002:Partial
	3:Canceled	100003:Canceled
Lead	0:Open	1:New
		2:Contacted
	1:Qualified	3:Qualified
	2:Disqualified	4:Lost
		5:Cannot Contact
		6:No Longer Interested
		7:Canceled
Letter	0:Open	1:Open
		2:Draft
	1:Completed	3:Received
		4:Sent
	2:Canceled	5:Canceled
List Value Mapping	0:Active	0:Active
Lookup Mapping	0:Active	0:Active
Marketing List	0:Active	0:Active
	1:Inactive	1:Inactive
Opportunity	0:Open	1:In Progress
		2:On Hold
	1:Won	3:Won
	2:Lost	4:Canceled
		5:Out-Sold
Opportunity Close	0:Open	1:Open
	1: Completed	2: Completed
	2: Canceled	3: Canceled
Order	0:Active	1:New
		2:Pending
	1:Submitted	3:In Progress
	2:Canceled	4:No Money
	3:Fulfilled	100001:Complete
		100002:Partial
	4:Invoiced	100003:Invoiced
Order Close	0:Open	1:Open
	1:Completed	2:Completed
	2:Canceled	3:Canceled
Owner Mapping	0:Active	0:Active
Partner Application	0:Active	1:Enabled
	1:Inactive	2:Disabled
Phone Call	0:Open	1:Open
	1:Completed	2:Made
		4:Received
	2:Canceled	3:Canceled
Price List	0:Active	100001:Active
	1:Inactive	100002:Inactive
Process	0:Draft	1:Draft
	1:Activated	2:Activated
Process Session	0:Incomplete	1:Not Started
		2:In Progress
		3:Paused
	1:Complete	4:Completed
		5:Canceled
		6:Failed
Queue	0:Active	1:Active
	1:Inactive	2:Inactive
Queue Item	0:Active	1:Active
	1:Inactive	2:Inactive
Quick Campaign	0:Open	1:Pending
		2:In Progress
	1:Closed	3:Aborted
		4: Completed
	2:Canceled	5:Canceled
Quote entity	0:Draft	1:In Progress
	1:Active	2:In Progress
		3:Open
	2:Won	4:Won
	3:Closed	5:Lost
		6:Canceled
		7:Revised
Quote Close	0:Open	1:Open
	1:Completed	2:Completed
	2:Canceled	3:Canceled
Recurring Appointment	0:Open	1:Free
		2:Tentative
	1:Completed	3:Completed
	2:Canceled	4:Canceled
	3:Scheduled	5:Busy
		6:Out of Office
Rollup Query	0:Active	0:Open
	1:Inactive	1:Closed
Saved View	0:Active	1:Active
		3:All
	1:Inactive	2:Inactive
Sdk Message Processing Step	0:Enabled	1:Enabled
	1:Disabled	2:Disabled
Service Activity	0:Open	1:Requested
		2: Tentative
	1:Closed	8:Completed
	2:Canceled	9:Canceled
		10:No Show
	3:Scheduled	3:Pending
		4:Reserved
		6:In Progress
		7:Arrived
System Job	0:Ready	0:Waiting For Resources
	1:Suspended	10:Waiting
	2:Locked	20:In Progress
		21: Pausing
		22: Canceling
	3:Completed	30:Succeeded
		31:Failed
		32:Canceled
Task	0:Open	2:Not Started
		3:In Progress
		4:Waiting on someone else
		7:Deferred
	1:Completed	5:Completed
	2:Canceled	6:Canceled
Transformation Mapping	0:Active	0:Active

Some more references:

Status and Status Reason   Dynamics 365 Javascript Useful Codes  Start with basic plugin writing, debugging  Learn how to create console app step by step    Most frequently used code to hide/show button on the ribbon

Thank you! 

Happy CRMing...

Keep Learning...