Suggestion: fnRowDataCallback to fetch the row data

DanMorin

The callback function fnRender() overwrite the original values, as described in the post "fnRender overwrites the original value" at http://www.datatables.net/forums/discussion/183/fnrender-overwrites-the-original-value/p1. The solution to have a hidden title does not solve my situation:

Some cells use the data from other cells in the same row. Let me give an example: We have affiliates having sales and commissions. The next two columns are calculated from the sales and the commission. At the moment, the server must send this data:

[code]
// Affiliate Name, Sales, Commission, % Commission, Net, Date of Last Sale
[ "Dan", 1000000, 400000, .4, 600000 ],
[ "Jim", 20000, 5000, .25, 15000 ],
[ "Smith", 80000, 40000, .5, 40000 ],
[ "Bart", 100000, 99000, .99, 1000 ][/code]

The ideal data from the server would be:

[code]
[ "Dan", 1000000, 400000 ],
[ "Jim", 20000, 5000 ],
[ "Smith", 80000, 40000 ],
[ "Bart", 100000, 99000 ][/code]

When I format a number such as 1000000 using fnRender() to be displayed as "$1,000,000", I cannot longer use fnRender() to calculate the % Commission because the the string "$1,000,000" is not a number anymore.

One solution would be to have a callback method named fnRowDataCallback(iRow, aDataOriginal, aDataRow)

The table would call this method just after processing the aaData or after receiving data from sAjaxSource. After calling fnRowDataCallback(), the content of aDataOriginal would be discarded. The callback function fnRowDataCallback() would allow the developer to perform calculations, and even re-order the columns by assigning aDataRow. This would bring a tremendous help because it is much easier to change the HTML page than changing the routine on the server fetching the data.

Example of swapping two columns:
aDataRow[0] = aDataOriginal[1];
aDataRow[1] = aDataOriginal[0];

In the example above, the code for fnRowDataCallback() would look like this:

[code]function fnRowDataCallback(iRow, aDataOriginal, aDataRow)
{
aDataRow[0] = aDataOriginal[0]; // Affiliate Name
aDataRow[1] = aDataOriginal[1]; // Sales
aDataRow[2] = aDataOriginal[2]; // Commission
aDataRow[3] = aDataOriginal[2] / aDataOriginal[1]; // % Commission
aDataRow[4] = aDataOriginal[1] - aDataOriginal[2]; // Net
}[/code]

Also, as webmasters, we want to see how displaying the data in a different way would look better. Therefore, a variant could be to display the "% Profit" rather than the "% Commission". Having fnRowDataCallback(), it the code would be as easy as replacing two lines of code:

[code]function fnRowDataCallback(iRow, aDataOriginal, aDataRow)
{
aDataRow[0] = aDataOriginal[0]; // Affiliate Name
aDataRow[1] = aDataOriginal[1]; // Sales
aDataRow[2] = aDataOriginal[2]; // Commission
aDataRow[3] = aDataOriginal[1] - aDataOriginal[2]; // Net
aDataRow[4] = aDataRow[3] / aDataOriginal[1]; // % Profit
}[/code]

Another feature request would be to specify the 'display', 'sort' and 'filter' in fnRowDataCallback()

[code]function fnRowDataCallback(iRow, aDataOriginal, aDataRow)
{
aDataRow[0] = aDataOriginal[0]; // Affiliate Name

aDataRow[1] = aDataOriginal[1]; // Sales
aDataRow[1].display = "$" + FormatAmount(aDataOriginal[1]); // Display a "$" in front of the sales and add 1000 separators
aDataRow[1].filter = aDataRow[1] + " " + aDataRow[1].display; // Filter using either the raw value or the formatted value
}[/code]

If the aDataRow[].sort is not specified, then the value from aDataRow[] is used. The same can be done for aDataRow[].display and aDataRow[].filter.

allan

What you are looking for can already be done with fnServerData :-). with fnServerData you make the Ajax call to the server (a trivial $.ajax call will do nicely) and then define the callback ("success") function to first manipulate your data from the server as desired, and finally pass it to DataTables to draw (the callback function that is provided as the third parameter to fnServerData). It isn't done on a row by row basis, but a trivial loop in the success function will do the business, and not introduce any further overhead into DataTables.

The alternative is to use mDataProp as a function, as we have discussed before. Basically rather than intercepting the data from the server you would have mDataProp calculate your data for you - that way you can apply the required formatting for display and keep numeric sorting based not he calculated data.

Regards,
Allan

DanMorin

Thank you for your reply. I saw the documentation of fnServerData, however it never occurred to me that this function could be used to manipulate aaData. How would you write the code from the following data:

[code]"aaData":
[
// Affiliate Name, Sales, Commission
[ "Dan", 1000000, 400000 ],
[ "Jim", 20000, 5000 ],
[ "Smith", 80000, 40000 ],
[ "Bart", 100000, 99000 ]
][/code]

To display the following values in a data table:

// Affiliate Name, Sales, Commission, % Commission, Net
"Dan", "$1000000", "$400000", "40%", "$600000"
"Jim", "$20000", "$5000", "25%", "$15000"
"Smith", "$80000", "$40000", "50%", "$40000"
"Bart", "$100000", "$99000", "99%", "$1000"

where the amounts may be sorted, and the user may filter using the display data.

allan

You could do it like this: http://live.datatables.net/ixapup/edit . Note that I've included two sorting functions that will deal with the rendered percentage and currency columns (they effectively get converted back into numbers for the sort).

mDataProp can be used to create exactly the same effect (without the need for the sorting plug-ins), and I can create such an example for you, if you are interested in seeing how that would work?

Allan

DanMorin

Thank you for the solution. I would probably implement my own version of fnRowDataCallback() so I do not have to repeat the core logic of fnServerData for each table (we may use your data table in about one hundred different HTML pages).

[code]"fnServerData": function (sSource, aoData, fnCallback ) {
      $.ajax( {
        "dataType": 'json',
        "type": "POST",
        "url": sSource,
        "data": aoData,
        "success": function (json) {
for ( var i=0, iLen=json.aaData.length ; i

allan

> so I do not have to repeat the core logic of fnServerData for each table

You could wrap up the Ajax call into a function and give that to fnServerData:

[code]
"fnServerData": myFunction
[/code]

if you needed different processing for each table then you would just do:

[code]
"fnServerData": myFunction( function (json) {
// process json
} );
[/code]

and have myFunction return a suitable function that will do the Ajax call.

> Overall, the code is working, except when I set aaData[] for testing purpose.

For this would would just call a function to process the data before passing it to DataTables as the aaData property :-)

> This is very cool for real-time testing :)

I really like JSBin :-). Fantastic that it is open source so I can use it on this site!

Allan

DanMorin

[quote]For this would would just call a function to process the data before passing it to DataTables as the aaData property :-)[/quote]
True, however it is nice to have an abstraction between the testing data and live data. This is what I really like about aaData[]. It allows me to test EVERYTHING first, and then the guy writing the code on the server just need to mimic the aaData[]. If there are two functions, then there is a risk of errors and bugs, and those bugs take time to find and are costly. In other words, I would like to ensure that if my code works fine with aaData[], then when when the live data arrives, I am confident it will work. In my 20+ years of experience as a developer, if two functions do the same work, they should be consolidated. For instance, in my sort routine, there is only one callback for comparing elements. If an array need to be sorted in descending order, then the sort routine will sort using the ascending order, then reverse the array before returning.

allan

Actually, given the aaData and sAjaxSource take two significantly different roots through DataTables (in terms of the asynchronicity of the Ajax call, if your goal is to use aaData for testing, I would suggest a slightly different approach: http://live.datatables.net/ixapup/4/edit

Using fnServerData doesn't mean that the data needs to come from the server, it can come from anywhere! In that example it is your data array. I've added a setTimeout to simulate the asynchronous behaviour of Ajax, but it isn't strictly needed.

Allan

DanMorin

For those who are looking for a way to modify the raw data, use mDataProp as I have written at http://datatables.net/forums/discussion/8249/filtering-using-the-rendered-text-however-sorting-using-the-original-value.#Item_19

In a nutshell, always use mDataProp to fetch data and don't write any code for the condition (type === 'set'). This way, your original raw data will remain intact, and mDataProp will give you whatever value you need. No need to use fnRender() anymore, and you get the best of both worlds.