DataTables & Custom Client Side API to access data

reggie

I'm trying to use DataTables server-side processing piece but I'm having trouble wiring it up.

FYI, the custom API that I'm using is SharePoint 2010 client-side object model (ecmascript). SharePoint also has a REST API but it would not be as efficient in my case.

I have a large set of data which means that I can only pull pages at a time. I know how to pull all this information but I just can't seem to wire it up to the datatable because my JSON isn't in the best format and I have to make my own call to get the following JSON:

[code]
[
{
"SchemaVersion":"14.0.0.0","LibraryVersion":"14.0.6108.5000","ErrorInfo":null
},22,{
"IsNull":false
},24,{
"IsNull":false
},26,{
"IsNull":false
},28,{
"IsNull":false
},29,{
"_ObjectIdentity_":"740c6a0b-85e2-48a0-a494-e0f1759d4aa7:web:c0db73c7-84d7-42c3-b54a-0e664fddc6e1:list:c14a8f23-3f70-4f38-99a9-b0a07d9de3f6"
},31,{
"IsNull":false
},32,{
"_ObjectType_":"SP.List","_ObjectIdentity_":"740c6a0b-85e2-48a0-a494-e0f1759d4aa7:web:c0db73c7-84d7-42c3-b54a-0e664fddc6e1:list:c14a8f23-3f70-4f38-99a9-b0a07d9de3f6","_ObjectVersion_":"9","ParentWebUrl":"\u002fproj\u002fdits","HasExternalDataSource":false,"Created":"\/Date(1350326545000)\/","LastItemModifiedDate":"\/Date(1350329404000)\/","LastItemDeletedDate":"\/Date(1350326545000)\/","Id":"\/Guid(c14a8f23-3f70-4f38-99a9-b0a07d9de3f6)\/","Description":"","Title":"Branches","Direction":"none","BaseType":0,"ImageUrl":"\u002f_layouts\u002fimages\u002fitgen.png","ItemCount":73,"BaseTemplate":10023,"DefaultContentApprovalWorkflowId":"\/Guid(00000000-0000-0000-0000-000000000000)\/","TemplateFeatureId":"\/Guid(0212e84c-da95-4561-880e-01582434f16e)\/","DefaultViewUrl":"\u002fproj\u002fdits\u002fLists\u002fBranches\u002fAllItems.aspx","DefaultEditFormUrl":"\u002fproj\u002fdits\u002fLists\u002fBranches\u002fEditForm.aspx","DefaultNewFormUrl":"\u002fproj\u002fdits\u002fLists\u002fBranches\u002fNewForm.aspx","DefaultDisplayFormUrl":"\u002fproj\u002fdits\u002fLists\u002fBranches\u002fDispForm.aspx","EnableAttachments":true,"ServerTemplateCanCreateFolders":true,"EnableFolderCreation":false,"EnableModeration":false,"EnableVersioning":false,"ForceCheckout":false,"EnableMinorVersions":false,"DraftVersionVisibility":0,"Hidden":false,"IsApplicationList":false,"IsCatalog":false,"AllowContentTypes":true,"DocumentTemplateUrl":null,"ContentTypesEnabled":true,"MultipleDataList":false,"NoCrawl":false
},33,{
"_ObjectType_":"SP.ListItemCollection","_Child_Items_":[
{
"_ObjectType_":"SP.ListItem","_ObjectIdentity_":"740c6a0b-85e2-48a0-a494-e0f1759d4aa7:web:c0db73c7-84d7-42c3-b54a-0e664fddc6e1:list:c14a8f23-3f70-4f38-99a9-b0a07d9de3f6:item:4,1","_ObjectVersion_":"1","Id":4,"DisplayName":"branch 2","DITSDivision":{
"_ObjectType_":"SP.FieldLookupValue","LookupId":5,"LookupValue":"division 2"
}
},{
"_ObjectType_":"SP.ListItem","_ObjectIdentity_":"740c6a0b-85e2-48a0-a494-e0f1759d4aa7:web:c0db73c7-84d7-42c3-b54a-0e664fddc6e1:list:c14a8f23-3f70-4f38-99a9-b0a07d9de3f6:item:13,1","_ObjectVersion_":"1","Id":13,"DisplayName":"branch 1","DITSDivision":{
"_ObjectType_":"SP.FieldLookupValue","LookupId":6,"LookupValue":"division 1"
}
}
]
}
]
[/code]


The items that I want to display in the datatable are in the _Child_Items_ object so I can map this into an array of object which would be easier to work with.

I want to do something similar to (http://datatables.net/usage/server-side)

[quote]
fnServerData

This parameter allows you to override the default function which obtains the data from the server ($.getJSON) so something more suitable for your application. For example you could use POST data, or pull information from a Gears or AIR database.

[code]
$(document).ready( function() {

$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "xhr.php",
"fnServerData": function ( sSource, aoData, fnCallback, oSettings ) {
oSettings.jqXHR = $.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
}
} );

} );
[/code]
[/quote]

My problem with this above is that I don't have an sAjaxSource per se. I wan't to call my own methods.


So my main question is:

If I can get it into an array how do I tell datatable to make another ajax call for example to get another page?

Basically I need a hook for the following:

1. initial getting of data (ajax call)
2. display data when ajax suceeds
3. filtering (another ajax call)
4. paging (another ajax call)

reggie

ok so I'm am able to generate the following json after my ajax call

and then I pass this to fnCallback in my fnServerData

[code]
{"sEcho":0,"iTotalRecords":5008,"iTotalDisplayRecords":19,"aaData":[{"Title":"service team"},{"Title":"policy branch"},{"Title":"programs branch"},{"Title":"market policy branch"},{"Title":"delivery branch"},{"Title":"analytics branch"},{"Title":"capital investment branch"},{"Title":"data branch"},{"Title":"funding policy"},{"Title":"quality branch"},{"Title":"services branch"},{"Title":"boards secretariat"},{"Title":"quality"},{"Title":"force"},{"Title":"Branch 3"},{"Title":"Branch 1"},{"Title":"Branch 0"},{"Title":"Branch 2"},{"Title":"Branch 4"}]}
[/code]

Problem 1: sEcho... I making this up... where do I get this since I'm not actually sending it to the server?
Problem 2: data is not populating table

[code]
.dataTable({
"bProcessing": true,
"bServerSide": true,
// "sAjaxSource": "./",
"bDestroy": true,
"fnServerData":BranchServerData, // here I make my own ajax call and send my JSON to fnCallback
"aoColumns": [{"mData":"Title"}]
});

[/code]

any help would greatly be appreciated.

allan

> Problem 1: sEcho... I making this up... where do I get this since I'm not actually sending it to the server?

sEcho is fundamental to server-side processing - it must be returned or DataTables just won't draw. DataTables gives you that parameter in the data given to fnServerData, so if you are overriding fnServerData you need to pull that value out and return it. What does BranchServerData look like?

Allan

reggie

figured out that the sEcho is with the aoData

[code]aoData: [{"name":"sEcho","value":2},{"name":"iColumns","value":1},{"name":"sColumns","value":""},{"name":"iDisplayStart","value":10},{"name":"iDisplayLength","value":10},{"name":"mDataProp_0","value":"Title"},{"name":"sSearch","value":""},{"name":"bRegex","value":false},{"name":"sSearch_0","value":""},{"name":"bRegex_0","value":false},{"name":"bSearchable_0","value":true},{"name":"iSortCol_0","value":0},{"name":"sSortDir_0","value":"asc"},{"name":"iSortingCols","value":1},{"name":"bSortable_0","value":true}]
[/code]

reggie

Thanks for the help... looks like I should have checked here earlier :P

So I'm getting data populated in the table now. It was due to the sEcho.

Now I'm trying to put in more data (i.e. more columns)

HTML:

[code]
Title
Branch Sort Name
ID
Loading data from server
[/code]

My JSON looks like:

[code]
{"sEcho":1,"iTotalRecords":5008,"iTotalDisplayRecords":10,"aaData":[{"Title":"HZ Branch 2201","BranchShortName":"HZ2201","ID":2688},{"Title":"HZ Branch 2200","BranchShortName":"HZ2200","ID":2691},{"Title":"HZ Branch 2202","BranchShortName":"HZ2202","ID":2692},{"Title":"HZ Branch 2206","BranchShortName":"HZ2206","ID":2693},{"Title":"HZ Branch 2204","BranchShortName":"HZ2204","ID":2694},{"Title":"HZ Branch 2205","BranchShortName":"HZ2205","ID":2695},{"Title":"HZ Branch 2203","BranchShortName":"HZ2203","ID":2696},{"Title":"HZ Branch 2210","BranchShortName":"HZ2210","ID":2697},{"Title":"HZ Branch 2207","BranchShortName":"HZ2207","ID":2698},{"Title":"HZ Branch 2211","BranchShortName":"HZ2211","ID":2699}]}
[/code]

[code]
.dataTable({
"bProcessing": true,
"bServerSide": true,
"fnServerData":BranchServerData,
"aoColumns": [
{"mData":"Title"}
,{"mData":"BranchShortName"}
/*,{"mData":"ID"}*/
]
});
[/code]

With this I get the following error:

[quote]
DataTables waring(table id='example'): Requested unknwon parameter 'BranchShortName' from the data source for row 0
[/quote]

Any thoughts?

allan

That looks like it should work. Could you run your table through the DataTables debugger and give us the debug code please.

Allan

reggie

I ran the debuger.

code: agahix

http://debug.datatables.net/agahix

Any help would greatly be appreciated.
Thanks

allan

The debugger suggests that no data has been loaded into the table - so its not even made it that far. I'm afraid we'd need to see a test page showing the issue to be able to offer any further help.

Allan

reggie

Ya I saw that. You mean the spot where it said that No XHR request made?

I finally got it to go.

I wasn't using sAjaxSource parameter since I'm making my own calls in the Server Data function (where I am creating a json variable and sending that to the fnCallback). I didn't think it was required.

So just for fun I added: [code]"sAjaxSource": "./"[/code]

... and I got my two other columns.



Now my only concern is whether there is a call to the ether that is being made.

I'll have to verify with fiddler.

Thanks