Fetching Data Based On Date Field Using WebAPI

Introduction

 

Are you looking for sample code to fetch data from Dynamics 365 CE using WebAPI? If yes, this post will help. We are discussing how to fetch the data based on the datetime field from the account entity.

 

Requirement

Retrieve account records which were created on or before today’s date.

Solution

 

While writing a Web API request, the first thing to make sure of is to check all the available filters that we can use in our WebAPI request. You can get full details of supported filters here.

 

Now, our requirement is to compare the data based on the current date, so the first thing we need is to get the current data and format it correctly using libraries, like Moment.js, or you can manually format it using the following.

function FormatDate() {
    var today = new Date();
    var month = today.getMonth() + 1; //to use correct month, it will return 0 for jan
    var day = today.getDate();
    var year = today.getFullYear();
    return month + "-" + day + "-" + year;
}

The above function will get the current date and will format it with what we need to query the Dynamics 365 CE. Now, we can use it in our WebAPI request.

 

In the following example, I am going to query the account entity record which is created today or before.

function retrieveAccounts() {

    var currentDate = FormatDate();
    var entity = "accounts";
    var columnSet = "?$select=name&$filter=createdon le '" + currentDate + "'&$count=true";
    var serverURL = Xrm.Page.context.getClientUrl();
    var Query = entity + columnSet;
    var req = new XMLHttpRequest();
    req.open("GET", serverURL + "/api/data/v9.0/" + Query, true);
    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 /* complete */ ) {
            req.onreadystatechange = null;
            if (this.status == 200) {
                var data = JSON.parse(this.response);
                if (data['@odata.count'] != null)
                    alert("Total Records:"+ data['@odata.count']);
            } else {
                var error = JSON.parse(this.response).error;
                alert(error.message);
            }
        }
    };
    req.send();
}

I hope this will help someone!