How to add columns dynamically

How to add columns dynamically

jstuardojstuardo Posts: 91Questions: 37Answers: 0

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

This question has accepted answers - jump to:

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

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

    Kevin

  • jstuardojstuardo Posts: 91Questions: 37Answers: 0

    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?

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    Answer ✓

    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

  • jstuardojstuardo Posts: 91Questions: 37Answers: 0

    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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    Answer ✓

    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

  • jstuardojstuardo Posts: 91Questions: 37Answers: 0

    Perfect! it worked!

    Thanks
    Jaime

Sign In or Register to comment.