Dynamically generating columns

htwyford

I am looking to pivot an MJoined datasource. I have selected Users from my system, and MJoined Schedules related to that user. What I am left with is a dataset of Users, and their associated scheduling (# hours per week). Here's a sample set of my JSON:

{
  "draw": null,
  "data": [
    {
      "DT_RowId": "row_1",
      "User": {
        "ID": 1,
        "firstMidName": "Harry",
        "lastName": "Twyford"
      },
      "Schedule": [
        {
          "week_ending": "2017-05-26",
          "JobID": 14,
          "hours": 40
        },
        {
          "week_ending": "2017-06-02",
          "JobID": 14,
          "hours": 50
        }
      ]
    },
    {
      "DT_RowId": "row_2",
      "User": {

etc...

I want to display this data as one row for each user, with columns extending out for each week_ending entry. I am having trouble coming up with a way to define my DataTables columns.data() columns dynamically, with one column for each week. This is what I have so far:

var builtColumns = [];
$('#schedulingTable').on('xhr.dt', function (e, settings, json, xhr) {
      builtColumns = constructColumns(json);
});
$('#schedulingTable').DataTable({
    dom: "Bfrtip",
    ajax: {
        url: ajaxUrl,
        data: { id: id },
    },
    //is this valid? Assigning an existing array to columns?
    columns: builtColumns,
    });
});

function constructColumns(json) {
    var currentHighest = 0;
    for (var row in json.data) {
        var weekCount = Object.keys(json.data[row].Schedule).length;
        if (weekCount > currentHighest) { currentHighest = weekCount; }
    }
    var column0 =
        {  //first column is always the User
            data: "User",
            render: function (data, type, full, meta) {
                return data.firstMidName + ' ' + data.lastName;
            }
        };
    var columns = [];
    columns[0] = column0;
    //one column for every week_ending entry. 
    //as many columns as the highest number of week_endings across all the rows.
    for (var i = 1; i <= currentHighest; i++) {
        columns[i] =
            {
                data: "Schedule[" + i + "].hours"
            };
    };
    return columns;
}

I have a couple of problems:
- is this even possible? Creating an array, then just assigning it to columns?
- the XHR event doesn't seem to be firing at all. Advice?

Edit: I would like for this to eventually become an Editor table, looking like this:

-----------------------------------------------------------------
User                      May 26                 June 3          ...
-----------------------------------------------------------------
Harry Twyford       40                         30
-----------------------------------------------------------------
Bob Smith             40                         30
-----------------------------------------------------------------

Where the hour values can be edited. If you have any advice on how to tackle this, I'd be grateful. Otherwise, I'll just tackle that when I can even get the DataTable running.

allan

//is this valid? Assigning an existing array to columns?
columns: builtColumns,

Yes, that in and of itself is valid. You can construct the array of column information any way you want.

BUT! (isn't there always a but....). You are listening for xhr from DataTables to build the table. But the table can't emit that event until it is triggered!

Have a look at the Q. Can I define my columns in Ajax loaded JSON?.

Allan

htwyford

An update, for those who may be looking for the answer in the future. I used bindred's answer here as inspiration, and created arrays of my DataTable columns and Editor columns by calling a function inside the ajax: success event. I then instantiated DataTables and Editor after that. This did mean that I couldn't use Editor's .NET libraries, and instead had to define my own server responses. The actual pivoting of the data is also done on the server-side, and sent as a DataTables-formatted ajax call (with DT_RowId, etc.)

I've taken out some bits of code (RowGrouping, custom buttons) for conciseness.

Code:

$(document).ready(function () {
    var builtColumns = [];
    var builtFields = [];
    $.ajax({
        url: ajaxUrl,
        success: function (response) {
            var [builtColumns, builtFields] = constructTable(response);

           // the number of columns is not known ahead of time, so the 
           // actual HTML table headers have to be built dynamically
            builtColumns.forEach(function (element) {
                $("#schedulingTable>thead>tr").append(
                        "<th>" + element.data + "</th>");
            });

            var editor;
            editor = new $.fn.dataTable.Editor({   
                table: '#schedulingTable',
                fields: builtFields
            });

            editor.on('preSubmit', function (e, data, action) {
                //your custom logic here...
                }               
            });


            $('#schedulingTable').DataTable({
                dom: 'Blfrtip',
                data: response.data,
                columns: builtColumns,
                select: true,

                buttons: [
                    { extend: "create",  editor: editor },
                    { extend: "edit",  editor: editor },
                    { extend: "remove", editor: editor },
                    { extend: 'copy'},
                    { extend: 'excel'}
                ]
            });
        }
    });
});

function constructTable(json) {
    //columns for DataTables
    var columns = [];
    columns[0] = { data: "Job Number" };
    columns[1] = { data: "Employee" };
    // more logic to build columns, 
    // based on what data you sent from your server...

    //fields for Editor
    var fields = [];
    // These are just my fields, to show that you must create 
    // these objects to match the "label:/name:" Editor structure
    fields[0] =
        {
            label: "Job",
            name: "JobID",
            type: "select",
            placeholder: "Select a job"
        };
    fields[1] =
        {
            label: "Employee",
            name: "UserID"
        };
    // more logic....

    return [columns, fields];
}

Hopefully this helps somebody in the future!

allan

Perfect - very nice! Thanks for sharing your solution with us.

Allan

JW

Thanks @allan and @htwyford. This actually helped me build a Datatable with dynamic columns while making sure the data mapped to the dynamic columns as well. Cheers!