Using Dynamics CRM FetchXml

pingcrosby

I am trying to use Datatables and dynamics CRM with FetchXML.

CRM-FetchXML sends and receives SOAP responses and has its own format for paging etc. I am trying to take the paging info etc generated by the datatable serverside:true setting, intercept the datatable:ajax call , take the serverside paging information, create a SOAP request and send to CRM, grab the CRM respose XML and reformat to JSON and give back to the table.

Can anybody provide any ideas how this can be achieved?

How do other people hook datatables into existing services where the response/request format of the server cannot be modified and client side JavaScript is needed to reformat for Datatables?

Any help is gratefully received.

This is an example of what i am trying to do :-

var accountid = 'd04be12e-9bf5-e411-80d5-00155d07fa1f'; // crm acct id
var columns = ['name', 'donotfax', 'donotemail', 'createdon']; // crm cols we are wanting

var fetch = CrmFetchKit.GetById('account', accountid, columns, PAGE).then(function (account) {
    // CRMFetchKit is a wrapper - creating SOAP calls for the CRM server
    // this fn() makes a server side call and on success ends up in this promise..
    // the CRM server expects SOAP request and has its own custom response that i will reformat
    // and pass onto the Datatable

    // success --> return data to datatable
    var ajax = {}
    ajax.name = account.getValue('name');
    ajax.donotfax = account.getValue('donotfax');
    ajax.donotemail = account.getValue('donotemail');
    ajax.createdon = account.getValue('createdon');

    return ajax;  // the data i want in the table
});     


window.onload = () => {

  $('#example').DataTable({

      serverSide: true,
      ajax: fetch,  // this is my fn() that calls CRM XmlFetch - 
                         // i dont want it to make the ajax call directly i want to do it indirect

      columns: [
          { data: 'name' },
          { data: 'donotfax' },
          { data: 'donotemail' },
          { data: 'createdon' }
      ]
    });

};

allan

Your method basically looks good to me. I presume that you've simplified the fetch function a little for this post (as there is no loop that I can see - you need an array of data, one element for each row). Also, I don't see the server-side processing parameters being set, even although you have serverSide enabled.

However, the biggest issue I see is that the fetch function does not match the function signature required by ajax and therefore cannot call the success callback to tell DataTables that the request is done. You will need a wrapper function:

ajax: function ( data, callback ) {
  fetch( callback );
}

Note that the callback option does not support promises, so you would need to support a callback in your Ajax fetch code.

Allan

pingcrosby

Allan
Thanks for the heads up - I somehow missed the very clear documentation on ajax:DT

For others who are wanting something similar, this is code I used.

I had to use a wrapper object to put the data inside a 'data' object.

I shall update this discussion as i add paging / serverside support for others to use.

data: [
    [
      "name"
      "donotfax",
      "donotemail",
      "createdon",    
    ]

ajax: function (data, callback, settings) {
    CrmFetchKit.Fetch(fetchxml).then(function (entities) {
        var obj = [];
        for (var i = 0, max = entities.length; i < max; i++) {
            var account = entities[i];
            var ajax = new ajaxResponse();
            ajax.name = account.getValue('name');
            ajax.image = 'content/datatables/images/pdf.png';
            ajax.donotfax = account.getValue('donotfax');
            ajax.donotemail = account.getValue('donotemail');
            ajax.createdon = account.getValue('createdon');
            //table.row.add(ajax);
            obj.push(ajax);
        }
        var r = new jsonResponse();
        r.data = obj;
        callback(r); // finished loading invoke the datatable callback    
    });
},

pingcrosby

A more complete example showing FetchXML serverside paging with datatables

In a nutshell - Inside the Ajax method , modify the XML updating the fetch count and insert the page cookie if necessary. Call CRM and setup a server side object with the returned data.

I am using the CrmFetchKit library from Github with a couple of minor changes to allow me to use the CrmFetchKit internal soap methods to modify the fetchxml.

Its important to clear the paging cookie if we change the fetch count - in this example i just hook into the datatables length (no of records in table) event and set the pageing cookie to null which stops the paging cookie getting injected into the fetchXml string.

Hope this helps..

// fetch count will be overridden with DT length value
var fetchxml = [
    '<fetch count="5" returntotalrecordcount= "true" >',
    '<entity name="customeraddress" >',
    '<attribute name="line1" />',
    '<attribute name="city" />',
    '<attribute name="stateorprovince" />',
    '<attribute name="county" />',
    '<order attribute="city" descending="true" />',
    '</entity>',
    '</fetch>'
].join('');
window.onload = function () {
    var pagingCookie = null;
    var table = $('#example').DataTable({
        processing: true,
        serverSide: true,
        ajax: function (data, callback, settings) {
            var sp = CrmFetchKit.GetSoapParser();

            // inject the 'many do records do we want' the fectxml the datatable page count
            fetchxml = sp.setPagingCount(fetchxml, data.length);

            if (pagingCookie != null) {
                var pageNumber = 1 + (data.start / data.length);
                // first time around its null after that its set and we need to pass
                fetchxml = sp.setPagingDetails(fetchxml, pageNumber, pagingCookie);
            }
            CrmFetchKit.FetchMore(fetchxml).then(function (response) {
                pagingCookie = response.pagingCookie;
                var obj = [];
                for (var i = 0; i < response.entities.length; i++) {
                    var account = response.entities[i];
                    var ajax = {};
                    ajax.line1 = account.getValue('line1');                  
                    ajax.city = account.getValue('city');
                    ajax.stateorprovince = account.getValue('stateorprovince');
                    ajax.county = account.getValue('county');
                    obj.push(ajax);
                }
                var r = new jsonResponse();
                r.draw = data.draw;
                r.data = obj;
                r.recordsTotal = response.totalRecordCount;
                r.recordsFiltered = r.recordsTotal;
                callback(r);
            });
        },
        columns: [          
            { data: 'line1' },
            { data: 'city' },
            { data: 'stateorprovince' },
            { data: 'county' }
        ]
    });
    $('#example').on('length.dt', function (e, settings, len) {
        // we need to clear down the paging cookie if we change the no. of requested for items
        pagingCookie = null;
    });
};

allan

Fantastic - thanks for sharing your solution with us.

Allan