How to add columns dynamically

jstuardo

Hello.... I have read about similar problems but none is similar to what I need or they are not very clear.

I have a DataTable that retrieves the data using ajax. I don't know which columns are returned in advance so I need to be able to add them dynamically after the ajax call is returned.

For instance, I have 3 fixed columns:

 columns: [
                    { data: "servicioNombre", width: 100 },
                    { data: "centroCostoNombre", width: 100 },
                    { data: "centroCostoCodigo", width: 100 }
                ],

Then I can apply a date range filter and the grid is refreshed according to that date range (only the grid is refreshed, not the whole page). The resulting data set is this:

[
    {
        "servicioNombre": "Almuerzo",
        "centroCostoCodigo": "",
        "centroCostoNombre": "PRODEMO",
        "2021-03-01": 0,
        "2021-03-02": 4,
        "2021-03-03": 1,
        "2021-03-04": 5,
        "2021-03-05": 4,
        "2021-03-06": 0,
        "2021-03-07": 0,
        "2021-03-08": 3,
        "2021-03-09": 2,
        "2021-03-10": 2,
        "2021-03-11": 3,
        "2021-03-12": 3,
        "2021-03-13": 2,
        "2021-03-14": 0,
        "2021-03-15": 2,
        "2021-03-16": 2,
        "2021-03-17": 2,
        "2021-03-18": 2,
        "2021-03-19": 2,
        "2021-03-20": 4,
        "2021-03-21": 0,
        "2021-03-22": 5,
        "2021-03-23": 4,
        "2021-03-24": 4,
        "2021-03-25": 4,
        "2021-03-26": 3,
        "2021-03-27": 5,
        "2021-03-28": 0,
        "2021-03-29": 6,
        "2021-03-30": 7,
        "2021-03-31": 0
    },
    {
        "servicioNombre": "Almuerzo",
        "centroCostoCodigo": "",
        "centroCostoNombre": "PRACTICA DEMO",
        "2021-03-01": 0,
        "2021-03-02": 1,
        "2021-03-03": 0,
        "2021-03-04": 1,
        "2021-03-05": 1,
        "2021-03-06": 0,
        "2021-03-07": 0,
        "2021-03-08": 1,
        "2021-03-09": 1,
        "2021-03-10": 1,
        "2021-03-11": 1,
        "2021-03-12": 1,
        "2021-03-13": 0,
        "2021-03-14": 0,
        "2021-03-15": 1,
        "2021-03-16": 1,
        "2021-03-17": 1,
        "2021-03-18": 1,
        "2021-03-19": 1,
        "2021-03-20": 0,
        "2021-03-21": 0,
        "2021-03-22": 1,
        "2021-03-23": 0,
        "2021-03-24": 1,
        "2021-03-25": 1,
        "2021-03-26": 1,
        "2021-03-27": 0,
        "2021-03-28": 0,
        "2021-03-29": 1,
        "2021-03-30": 1,
        "2021-03-31": 1
    },
    {
        "servicioNombre": "Cena",
        "centroCostoCodigo": "",
        "centroCostoNombre": "PRODEMO",
        "2021-03-01": 0,
        "2021-03-02": 5,
        "2021-03-03": 3,
        "2021-03-04": 6,
        "2021-03-05": 7,
        "2021-03-06": 0,
        "2021-03-07": 0,
        "2021-03-08": 3,
        "2021-03-09": 7,
        "2021-03-10": 8,
        "2021-03-11": 6,
        "2021-03-12": 5,
        "2021-03-13": 2,
        "2021-03-14": 0,
        "2021-03-15": 3,
        "2021-03-16": 5,
        "2021-03-17": 4,
        "2021-03-18": 5,
        "2021-03-19": 5,
        "2021-03-20": 2,
        "2021-03-21": 0,
        "2021-03-22": 0,
        "2021-03-23": 2,
        "2021-03-24": 4,
        "2021-03-25": 4,
        "2021-03-26": 4,
        "2021-03-27": 2,
        "2021-03-28": 0,
        "2021-03-29": 0,
        "2021-03-30": 0,
        "2021-03-31": 0
    },
    {
        "servicioNombre": "Cena",
        "centroCostoCodigo": "",
        "centroCostoNombre": "DEMO",
        "2021-03-01": 0,
        "2021-03-02": 12,
        "2021-03-03": 20,
        "2021-03-04": 34,
        "2021-03-05": 32,
        "2021-03-06": 0,
        "2021-03-07": 0,
        "2021-03-08": 16,
        "2021-03-09": 27,
        "2021-03-10": 32,
        "2021-03-11": 34,
        "2021-03-12": 33,
        "2021-03-13": 11,
        "2021-03-14": 0,
        "2021-03-15": 15,
        "2021-03-16": 34,
        "2021-03-17": 35,
        "2021-03-18": 35,
        "2021-03-19": 34,
        "2021-03-20": 17,
        "2021-03-21": 0,
        "2021-03-22": 17,
        "2021-03-23": 32,
        "2021-03-24": 34,
        "2021-03-25": 34,
        "2021-03-26": 35,
        "2021-03-27": 11,
        "2021-03-28": 0,
        "2021-03-29": 18,
        "2021-03-30": 34,
        "2021-03-31": 33
    }
]

Of course, columns will depend on the date range selected in the filter. Can you give me some clues about how to achieve this?

Thank

kthorngren

Maybe this thread will help. You will need to use jQuery ajax() to fetch the table data instead of Datatables ajax so you can build the columns before DT initializaiotn.

Kevin

kthorngren

Here is a simple example similar to what is in that thread:
http://live.datatables.net/huyexejo/1/edit

Kevin

jstuardo

Thanks @kthorngren. Now, how can I pass the parameters that datatable sends automatically which includes filter and sort information? For example, something similar to other grids I have in my system:

draw: 1
columns[0][data]: grupoServicio
columns[0][name]: 
columns[0][searchable]: true
columns[0][orderable]: true
columns[0][search][value]: 
columns[0][search][regex]: false
columns[1][data]: servicio
columns[1][name]: 
columns[1][searchable]: true
columns[1][orderable]: true
columns[1][search][value]: 
columns[1][search][regex]: false
columns[2][data]: cantidad
columns[2][name]: 
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]: 
columns[2][search][regex]: false
columns[3][data]: valorUnitario
columns[3][name]: 
columns[3][searchable]: true
columns[3][orderable]: true
columns[3][search][value]: 
columns[3][search][regex]: false
columns[4][data]: valorTotal
columns[4][name]: 
columns[4][searchable]: true
columns[4][orderable]: true
columns[4][search][value]: 
columns[4][search][regex]: false
order[0][column]: 1
order[0][dir]: asc
start: 0
length: 10
search[value]: 
search[regex]: false

The ajax function is prepared to receive all that object in order to filter and sort accordingly.

On other grids with fixed columns, I refresh the grid using tabla.search(searchQuery).draw();, where tabla is the datatable.

How can I do that in this case?

kthorngren

The process will be similar. The URL you go to in the jQuery ajax() request should just return the column names, not any row data. Then initialize your Datatables as normal in the success function. For example:
http://live.datatables.net/qimukefe/1/edit

This example uses the same URL as Datatables is configured for:

    $.ajax({
      url: "/ssp/objects.php",

But you will want something different that just returns the column - no data.

Kevin

jstuardo

Thanks @kthorngren, it worked, however, something is left and is concerning grid refresh.

I have created a getReport() function that gets the columns and then create the DataTable object.

Now, the problem I am having is when the filter is changed. I have a modal dialog box allowing to choose a different date range.

On other pages, where all columns are fixed, I just call tabla.search(advancedSearchQuery).draw();.

In this case, with dynamic ones, I have replaced that call with a call to getReport(). The new columns are retrieved correctly, but the DataTable is not repainted in order to show the new columns corresponding to the new date range.

Inside the getReport() function I am using tabla = $('#report').DataTable({ .... }); to create the DataTable. I think repainting is not performed since the object is already created. Should I destroy the DataTable first before recreating? how can I do that?

Thanks again

kthorngren

Should I destroy the DataTable first before recreating? how can I do that?

YEs, to make changes to the columns you need to destroy the Datatable. Use the destroy() API. See the examples in the docs. You may want to use the DataTable.isDataTable() to make sure the Datatables exists before destorying.

Kevin

jstuardo

Perfect! it worked!

Thanks
Jaime