Exporting data from the source data rather than from a displayed table cell

stuartsjb-icr

I'm using mRender to shortern a Notes field down to 30 characters (using a substring function I've written) for display purposes in my table. The user can click on the row and then the Edit button in order to view the full Notes field in a pop-out Editor window.

"mRender": function (data, type, row) {
      return (truncate(row.ModuleCourseworkMarks.Notes, 30));
}

This works great except for when the user wants to export the table's data to a CSV file, when the exported data displays only the truncated Notes.

Is it possible to configure csvHtml5 (and copyHtml5 too, actually) to export the full row.ModuleCourseworkMarks.Notes data instead of what's present in the visible table cell?

allan

Yes, have a look at this example which describes what you are looking for.

Allan

stuartsjb-icr

Thanks Allan. I've swapped from using mRender on the cell to using this:

data: "ModuleCourseworkMarks.Notes", render: function (data, type, row) {
       return (truncate(row.ModuleCourseworkMarks.Notes, 30));
}
                    

and changed the Buttons initialisation to this:

buttons: [
                    {extend: 'edit', editor: courseworkMarksTableEditor},
                    {extend: 'copyHtml5',
                        exportOptions: {
                            columns: ':visible',
                            orthogonal: 'export'
                        }
                    },
                    {extend: 'csvHtml5',
                        text: 'Export CSV',
                        exportOptions: {
                            columns: ':visible',
                            orthogonal: 'export'
                        }
                    }
                ]

..but the CSV output still retains the truncated Notes text. Have I missed something obvious? I would share the page URL but unfortunately our IIS runs on an intranet server.

stuartsjb-icr

Durr. Apologies Allan, having a blond moment this afternoon. I have set my code as follows and all is working well!

{data: "ModuleCourseworkMarks.Notes", render: function (data, type, row) {
                            return type === 'fullNotes' ? row.ModuleCourseworkMarks.Notes : truncate(row.ModuleCourseworkMarks.Notes, 30);
                        }
                    }

buttons: [
                    {extend: 'edit', editor: courseworkMarksTableEditor},
                    {extend: 'copyHtml5',
                        exportOptions: {
                            columns: ':visible',
                            orthogonal: 'fullNotes'
                        }
                    },
                    {extend: 'csvHtml5',
                        text: 'Export CSV',
                        exportOptions: {
                            columns: ':visible',
                            orthogonal: 'fullNotes'
                        }
                    }
                ]

Thanks again!

stuartsjb-icr

I've noticed that since adding in support for the orthogonal data that I'm getting "null" values in my exported CSV files for empty fields instead of just empty values ("").

I've had a look at the buttons.exportData page for some inspiration but can't figure out why it's happening. Surely if the orthogonal data type defaults to string then that's what should be output to the CSV file, rather than null (as the contents of the empty fields are literally nothing rather than "null").

allan

Do you have a fullNotes parameter in your data source object? edit This is duff - forget that.

What you have looks about right if you are using the latest version of Buttons. Can you l ink to the page please?

Allan

stuartsjb-icr

Hi Allan. I'm using Buttons v1.1.0. Are there are changes between this and v1.1.2 that could be causing the problem?

Unfortunately the page is hosted on our intranet web server, and as such I'm unable to provide a link. If the issue is not related to the version of Buttons I'm using then I'll try and reproduce the page and code on live.datatables.net on Monday, as I've been scratching my head over this one for a few hours now.

allan

I'm not aware of any changes that would effect that one I'm afraid. A test case would be great.

Allan

stuartsjb-icr

Hi Alan. I've replicated the problem I'm having on live.datatables.net here:

http://live.datatables.net/mefugazi/1/edit?html,js,output

I've used my truncate function on the Position field to demonstrate what I've been getting at in my initial question; it shortens the visible text in the table to 10 characters (whilst allowing the full text to be exported or copied using csvHtml5 or copyHtml5).

As you can see in the JavaScript tab I've added some null data into the ajax object. When I export the data to CSV I get "null" appear when loading the CSV file up in Excel. If I disable the orthogonal data for the buttons' exportOptions though, they appear correctly as empty cells.

allan

You have:

return type === 'export' ?
                    data : truncate(data, 10);

So if the type is export, which it is when you click one of the buttons, it is going to return the original data - in this case null for that position column!

There needs to be another condition in there: (data || '') would do it.

Allan

stuartsjb-icr

Would it not export only the original data for the Position column rather than for all of them? I am getting null exported for all empty column fields when specifying orthogonal data to be exported.

Apologies if my understanding of this functionality is a bit limited. I followed the Orthogonal Data example and assumed that adding the orthogonal exportOption would use original AJAX data only for columns that use a type-conditional render function.

allan

Buttons will ask for the orthogonal data "export" for all columns - but since only one in your table actually handles that switch, the original data should be used for all others.

Allan