In the blog post HSD’s Senior Consultant Kamran Dilmir outlines how you can retrieve results with a “Column Comparison Query” in Dynamics 365 Web API.

In Dynamics 365, there has been a common problem for developers that they were not able to write queries where they can retrieve results on the bases of column comparison. But the good news is that this problem is addressed in Common Data Service based queries to some extent. With CDS based queries, developers can use column comparison expressions in Fetchxml, SDK and OData queries. Although this facility got some limitations like it’s not supported in Advanced Find and it can only be used within the same entity columns but it is still a good starting point and hopefully the limitations will go away over time.

To see the conditions available for column comparisons and also to see the limitations please follow the link below:
1. https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/column-comparison
To see details of Column comparison using web API please follow the link below:
2. https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-data-web-api#column-comparison-using-the-web-api
To see details of Column comparison using SDK API follow the link below:
3. https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/org-service/use-conditionexpression-class#column-comparison-using-the-sdk-api

In this blog post, I will discuss the FetchXml query with a column comparison filter condition to retrieve results in java script code using dynamics 365 web API. Let’s say we have a field with a name of “new_totalInvoiceAmount” on Account entity which has the total amount of all invoices related to that Account. Whenever the new invoice is created for that account a plugin runs and add the invoice amount in the total amount of the field. There is another field “new_totalBudgetAmount” on Account entity which is used to add total available budget for that account. (The same functionality can also be achieved by using calculated fields but let’s take this example for this discussion.)

Now, we want to write a JavaScript function on load of a contact entity form, where we can check if the account associated to this contact has a “new_totalInvoiceAmount” value greater than or equal to the “new_totalBudgetAmount” value and then perform some action based on the result.

"<fetch version='1.0' output-format='xml-platform' mapping='logical'   distinct='false'>" +
   "<entity name='account'>" +
      "<attribute name='accountid' />" +
        "<filter type='and'>" +
"<condition attribute='new_totalInvoiceAmount' operator='ge' valueof='new_totalBudgetAmount'/>" +
        "</filter>" +
   "</entity>" +
"</fetch>";

In FetchXML query, a new node “valueof” can be added inside the condition node of filter criteria. The “valueof” node is used to identify the attribute that is being compared to the selected attribute. In the above example, the ‘new_totalInvoiceAmount ‘ column is being compared against the ‘new_totalBudgetAmount’ column and will return any records that contain greater than or same value across both attributes.

The full code example is given below:

Contact_OnLoad = function () {
    
        var fetchAccount = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
            "<entity name='account'>" +
            "<attribute name='accountid' />" +
            "<filter type='and'>" +
            "<condition attribute='new_totalInvoiceAmount' operator='ge' valueof='new_totalBudgetAmount'/>" +
            "</filter>" +
            "</entity>" +
            "</fetch>";

        var serverURL = parent.Xrm.Page.context.getClientUrl();
        var fetch = encodeURI(fetchAccount);
        var entityname = "account";
        var Query = entityname + "?fetchXml=" + fetch;
        var req = new XMLHttpRequest();
        
        req.open("GET", serverURL + "/api/data/v9.1/" + Query, 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.onreadystatechange = function () {
            if (this.readyState === 4) {
                this.onreadystatechange = null;
                if (this.status === 200) {
                    var returned = JSON.parse(this.response);
                    var results = returned.value;
                    if (results.length > 0)
			//perform an action	
                        
                }
            }
        }
        req.send();
}