Format number field in yarja datatables from query

Level 61

For now you can map over $co and use number_format function to achieve this. Later if someone comes with better option you can refactor to that solution.

Level 3

Thanks but I am now certain what you mean. I have tried adding selectRaw etc

Best Answer

Level 73

@jim1506 the way I do it with DataTables is format the number on the client side. When you initialize the DataTable on your table, you could pass a callback function which will run for each row that has been created.

The function is called createdRow. And I do this:

// add this function for number formatting somewhere in your scripts, so you can reuse it
function formatNumber(num) {
  return num.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, ',')
}   

// then the DataTable

$('#your_table').DataTable({
                ...
    createdRow: function (row, data, dataIndex) {
        if (data.population !== undefined) {
            // 4 here is the cell number, it starts from 0 where this number should appear
            $(row).find('td:eq(4)').html(formatNumber(data.population)); 
        }
    },
    pageLength: 10,
    columns: [
        {data: "population", name: 'population'},
         ... // your columns here
    ]
});

Hope this gives you an idea on how to do it :)

Forgot to mention that this way you don't lose the search benefit, so that the user can search using number without commas. I had a problem formatting it server side, couldn't search using the number before.

Level 3

Thanks. I have a slight problem. I copied the function into a helper which I use but I am getting

syntax error, unexpected ')', expecting variable (T_VARIABLE)

The function as I put it in is:

function formatNumber(num) {
  return num.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, ',');
}

Level 73

@jim1506 you know this is a JavaScript function right? So don't put it in a php helper file, but in a JavaScript one.

Level 3

What a plonker I am!

Level 3

I have a slight problem with the conversion. For example a figure of 29121286 is showing up as 2,12,286, 26711 is showing up as 2,711 and 2986952 is showing up as ,98,952.

I placed the script in a general javascript file which I call from the app layout:

function formatNumber(num) {
  return num.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, ',')
}

and added this to my page in the datatables definition:

createdRow: function (row, data, dataIndex) {
                    if (data.population !== undefined) {
                        $(row).find('td:eq(4)').html(formatNumber(data.population));
                    }
                },

Level 73

@jim1506 I used this guy's script. Can you try the exact one from here:

https://blog.abelotech.com/posts/number-currency-formatting-javascript/

The $1 get's removed here, so copy it from the page there.

Level 3

Perfect - Thanks a lot.

Level 3

If I may be so bold, I also have a field for currency formats, for example

'€'

I want them to show as (in this case) the euro symbol rather than the data

Level 3

It is refusing to let me show the code as it is showing it as the euro symbol but it is the hex code 8364 with &# before and ; after

Level 73

@jim1506 and from the same article you checked the currency formatting?

Level 3

The idea is to show the symbol as no currency amount is shown. It is a table of countries that links to a lot of information and I have a table of currencies which links so it is a case of showing the symbol only.

Level 73

@jim1506 I am sorry but I don't understand the issue.

Level 3

The column is just a column of the symbol itself next to a column of the currency name. The database contains the hex database symbol so all I want to show is the symbol itself. In other words I want to get the string to show up as just html code.