Working with Currencies and JSON Data

shasan

Hello All,

I've been using Datatables for the past few weeks and thus far everything has worked well. However, I have run into an issue I can't solve with google searches and forum crawling...

I have a Datatable set up using JSON data which displays correctly but contains an "amount" column, which I wish to have displayed as a currency column. I tried to use the Orthogonal data tutorial and many different variations/solutions but I can't get it to append the $ sign. I know it may seem trivial but I'll add sorting to this sometime down the line.

The table is currently defined as follows:

[code]
var oTable = $("#report").dataTable({
"bProcessing": true,
"bServerSide": true,
"sPaginationType": "bootstrap",
"sAjaxSource": "rest/reporting/payment",
"aoColumnDefs": [ {
"aTargets": [3],
"mData": function ( data, type, val ) {
if (type === 'set') {
// Store the base value
data.price = val;

// Display is formatted with a dollar sign and number formatting
data.price_display = val==="" ? "" : "$"+numberFormat(val);

// Filtering can occur on the formatted number, or the value alone
data.price_filter = val==="" ? "" : data.price_display+" "+val;
return;
}
else if (type === 'display') {
return data.price_display;
}
else if (type === 'filter') {
return data.price_filter;
}
// 'sort', 'type' and undefined all just use the integer
return data.price;
}
} ],
"aoColumns": [
{ "mDataProp": "transactionId" },
{ "mDataProp": "processedTime" },
{ "mDataProp": "orderId" },
{ "mDataProp": "amount" },
[/code]
... and so on for sdom and the rest of the columns, as well as the fnServerData call - left out for brevity.

There are no errors and I can't see any reason this shouldn't work, unless I can't use both aoColumns and aoColumnDefs at the same time? I also tried to run this through the debugger but wasn't able to find if there were any glaring omissions or any errors - I can post a link (with data removed) if needed. Would anyone be able to shed some light on this? Many thanks in advance!

-Salman

allan

Hi,

I was looking through this thinking it looks absolutely perfect and then saw your comment:

> unless I can't use both aoColumns and aoColumnDefs at the same time?

And it clicked. Actually yes you can - but you are effectively using the same property twice (mDataProp and mData are the same) and aoColumns takes priority - thus your mData function for the forth column is being removed...

Try this:

[code]
var oTable = $("#report").dataTable({
"bProcessing": true,
"bServerSide": true,
"sPaginationType": "bootstrap",
"sAjaxSource": "rest/reporting/payment",
"aoColumnDefs": [ {
"aTargets": [3],
"mRender": function ( val, type, all ) {
return ( type === "display" || type === "filter" ) ? "$"+val : val;
}
} ],
"aoColumns": [
{ "mData": "transactionId" },
{ "mData": "processedTime" },
{ "mData": "orderId" },
{ "mData": "amount" },
[/code]

That uses the new mRender option (in 1.9.3) to make life much easier :-)

Allan

shasan

Allan,

Thank you for the prompt (and working!) suggestion - I grabbed 1.9.3 and it worked with your modifications. I just have one further issue which I hope to bring up while I have you here :)

I failed to mention that I'm also hoping to format the result as a number (again, as demonstrated in the orthogonal demo) - that way it would (hopefully) append any necessary formatting, changing:

Alexa Holt 51447
to..
Alexa Holt $51,447 (with .00 cents if possible/required)

However I am running into exceptions on numberFormat ("numberFormat is not defined"). I tried drawing out the code from the original blog and adding it to your suggestion:

[code]return ( type === "display" || type === "filter" ) ? "$"+numberFormat(val) : val;[/code]

Am I doing something crazy, or missing anything else?! Including this command at least allowed me to render without JS errors with 1.9.2 but is failing now. I've been poring over that blog and don't see what magic converts your 5 digit number to a currency value.

Thank you again!
Salman

allan

'numberFormat' in the blog is just a pseudo function - it doesn't exist, you would need to create it. It is just an example of how a function might be useful inside mData :-)

Having said that, DataTables does actually have a number formatter built in, for its own formatting. It is possible to access it directly, but it isn't really designed to work that way, so you can just pull the code out:

[code]
function ( iIn )
if ( iIn < 1000 )
{
// A small optimisation for what is likely to be the majority of use cases
return iIn;
}

var s=(iIn+""), a=s.split(""), out="", iLen=s.length;

for ( var i=0 ; i

shasan

Allan,

Many, many thanks for this. When I said that I couldn't figure out the magic, I didn't think there was *actual* magic in there. Whoops.

Anyway, the function you suggested, ended up looking like this:

[code]
function formatNumber( iIn ) {
if ( iIn < 1000 ) {
// A small optimisation for what is likely to be the majority of use cases
return iIn;
}
var s=(iIn+""), a=s.split(""), out="", iLen=s.length;
for ( var i=0 ; i