Retrieve 100K+ records from Dataverse in XRM Web API using JavaScript

Photo by Igor Dresjan A.P. on Unsplash

Struggling to fetch large data sets via the XRM Web API? No sweat! I’ve authored an article demonstrating how to efficiently fetch large datasets from the Dataverse XRM Web API using JavaScript. In the article, I cover both querying techniques, including FetchXML and OData Query, providing comprehensive guidance on handling substantial amounts of data.

To demonstrate, I imported a sample dataset with over 100,000 records into the contact table. This allows us to experiment and interact with a large dataset.

I’ve added a button to the contact view using the command editor. After adding your web resource library, simply add the function name below the button configuration in the command editor.

As mentioned earlier, we have two methods available. We can demonstrate both techniques within the same web resource by overwriting it as needed. This approach allows for a streamlined and cohesive demonstration of fetching large datasets using FetchXML and OData Query within a single resource.

Using Next Link(OData Query)

OData query is one of the easiest methods to fetch records from the XRM Web API, particularly for simple queries. However, it’s important to note that the Dataverse API retrieves a maximum of 5000 records per request. If the number of records exceeds this limit(of 5000), the API will provide a ‘next link’ field in the response. Utilizing this ‘next link’ value, subsequent requests can be made to fetch the next set of 5000 records. This process continues until all records are fetched. The provided code exemplifies how to leverage OData queries in the XRM Web API to efficiently retrieve large datasets, exceeding 100,000 records.

function paginationScript() {
    // set the OData Query to fetch only fullname(to make it simplified)
    var query = "?$select=fullname";
    // Initialize an array to push all fetched records into it.
    var allaccounts = [];
    return retrieveMultipleAccounts(query, allaccounts);
}

function retrieveMultipleAccounts(query, allaccounts) {
    var count = 0;
    // get the client url
    var url = Xrm.Utility.getGlobalContext().getClientUrl() + "/api/data/v9.0/contacts";
    return Xrm.WebApi.retrieveMultipleRecords("contact", query).then(
        function success(result) {
            allaccounts = allaccounts.concat(result.entities);
            count = allaccounts.length;
            if (result.nextLink != undefined) {
                console.log("Next page link: " + result.nextLink);
                query = result.nextLink;
                //result.nextLink contains client url, we need to remove the url and has to pass only query.
                var splitValue = query.split(url);
                // as the split returns array, we need to take the index 1(because index 0 has client url)
                query = splitValue[1];
                retrieveMultipleAccounts(query, allaccounts);
            }else{
                Xrm.Navigation.openAlertDialog(count.toString());
            }
        },
        function (error) {
            console.log(error.message);
        }
    );
}

Using Paging Cookie(Fetch XML)

Of course! We can indeed use FetchXML to fetch records with pagination. To achieve this, we need to update the paging cookie for each fetch operation until we reach the end of the records. The JavaScript code provided facilitates this process by managing the pagination seamlessly.

function CreateXml(fetchXml, pagingCookie, page, count) {
    // initialize DOMParser and XMLSerializer
    var domParser = new DOMParser();
    var xmlSerializer = new XMLSerializer();

    // Parse the string to XML
    var fetchXmlDocument = domParser.parseFromString(fetchXml, "text/xml");

    if (page) {
        // Get the fetch element in XML and set the page attribute
        fetchXmlDocument
            .getElementsByTagName("fetch")[0]
            .setAttribute("page", page.toString());
    }

    if (count) {
        // Get the fetch element in XML and set the count attribute
        fetchXmlDocument
            .getElementsByTagName("fetch")[0]
            .setAttribute("count", count.toString());
    }
    if (pagingCookie) {
        // Parse the Paging Cookie from string to XML
        var cookieDoc = domParser.parseFromString(pagingCookie, "text/xml");

        var innerPagingCookie = domParser.parseFromString(
            decodeURIComponent(
                decodeURIComponent(
                    cookieDoc
                        .getElementsByTagName("cookie")[0]
                        .getAttribute("pagingcookie")
                )
            ),
            "text/xml"
        );
        // set the Paging Cookie using inner Paging Cookie
        fetchXmlDocument
            .getElementsByTagName("fetch")[0]
            .setAttribute(
                "paging-cookie",
                xmlSerializer.serializeToString(innerPagingCookie)
            );
    }

    return xmlSerializer.serializeToString(fetchXmlDocument);
}

function retrievePage(entityName, fetchXml, pageNumber, count, pagingCookie) {
    var fetchXml =
        "?fetchXml=" + CreateXml(fetchXml, pagingCookie, pageNumber, count);

    // return result of the XRM Web API for the single page of records 
    return Xrm.WebApi.online.retrieveMultipleRecords(entityName, fetchXml).then(
        function success(result) {
            return result;
        },
        function error(e) {
            throw e;
        }
    );
}

function retrieveAllRecords(entityName, fetchXml, page, count, pagingCookie) {
    // set page to 0 when page doesn't have value 
    if (!page) {
        page = 0;
    }

    // return records until all records are fetched
    return retrievePage(entityName, fetchXml, page + 1, count, pagingCookie).then(
        function success(pageResults) {
            if (pageResults.fetchXmlPagingCookie) {
                return retrieveAllRecords(
                    entityName,
                    fetchXml,
                    page + 1,
                    count,
                    pageResults.fetchXmlPagingCookie
                ).then(
                    function success(results) {
                        if (results) {
                            return pageResults.entities.concat(results);
                        }
                    },
                    function error(e) {
                        throw e;
                    }
                );
            } else {
                return pageResults.entities;
            }
        },
        function error(e) {
            throw e;
        }
    );
}

function paginationScript() {
    // set the count to 5000(it is the max length by default)
    var count = 5000;
    var fetchXml =
        "<fetch mapping='logical'>" +
        "    <entity name='contact'>" +
        "      <attribute name='contactid' />" +
        "      <attribute name='fullname' />" +
        "    </entity>" +
        "  </fetch>";

    retrieveAllRecords("contact", fetchXml, null, count, null).then(
        function success(result) {
            console.log(result.length);
            Xrm.Navigation.openAlertDialog(result.length.toString());
        },
        function error(error) {
            console.log(error.message);
        }
    );
}

In the above code, I have four functions and here’s a simplified breakdown:

1. CreateXml Function: Constructs a FetchXML query with optional pagination parameters.
2. retrievePage Function: Retrieves a single page of records using the FetchXML query.
3. retrieveAllRecords Function: Recursively retrieves all records by fetching pages until no more are available.
4. paginationScript Function: An example demonstrating how to use the pagination feature to retrieve all records from contacts table.

You can see the result of the above demonstration in the below GIF

Reference: You can refer the Microsoft learn document by clicking here

Have a great day!

Originally published at https://www.tamilarasu.me on June 9, 2024.