Footnotes or legend based on data

Loren Maxwell

Is there an example of making footnotes or a legend based on the table data?

For example, suppose I had data for 100 games, 70 of which can be designated home/away ("H" or "A" in the "Site" column) and 30 of which occur at neutral sites ("N" in the "Site" column). Suppose there are only five neutral sites for those 30 games.

Instead of having an entire column to spell out the neutral site for each game, 70% of which will be blank and plenty of repeats in the others, then how could I just simply annotate "N1", "N2", "N3", etc. in the "Site" column and then at the bottom of the table include a legend that shows:
N1 = Washington Stadium
N2 = Adams Stadium
N3 = Jefferson Stadium
etc.

Ideally the solution would be "data-agnostic" to where it doesn't matter if there are 2 or 20 neutral sites, it will calculte the footnotes/legend the same way.

Thoughts?

allan

There isn't I'm afraid, although it sounds like a good idea for a plug-in! Certainly possible to do, there just isn't such an example at the moment.

Allan

Loren Maxwell

OK, I've decided to play around with this and I feel I'm right on the cusp but am not quite seeing what I need to do next.

Here's the page I'm working on: http://ghsfha.org/datatables_cards_unique.html

I've added a drawCallBack function that uses the unique() function to create an array of all the office locations. Then I use indexOf to map the office location back to the index in the array.

Where I'm getting stuck is in rendering the office location with it's index (and moving on to eventually just using the index and referencing a legend that this function will eventually create).

Here's my current code:

drawCallback: function () {
    var api = this.api();
    var rows = api.rows().nodes();
    var values = $.map(api.column('office:name').data().sort().unique(), function(value, index) { return [value]; });
    console.log(values);

    api.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
        var index = values.indexOf(this.cell(rowLoop,'office:name').node().innerHTML);
        console.log(index);
        this.cell(rowLoop,'office:name').render( function(data, type, row) {
            return index+': '+data;
        } )
    } );
}

I'm using cell().render(), which I know is incorrect, but I'm not sure what to do.

Previously I had changed the innerHTML, which worked until I sorted the table because that method alters the data in the cell.

Any thoughts?

allan

I'm not quite getting it I'm afraid - could you show me a screenshot of what you want the page to look like?

Allan

Loren Maxwell

My ultimate goal for this exercise is to produce something similar to this:

The Legend shows that each office has been given an index from 0 to 6 and in the table itself the index is reflected at the end of the Position field in brackets, so using the Legend and the index from the first row I can see Tiger Nixon is a System Architect at Edinburgh.

Here's my overall drawCallback function (I explain each piece below):

drawCallback: function () {
    var api = this.api();
    var rows = api.rows().nodes();
    var values = $.map(api.column('office:name').data().sort().unique(), function(value, index) { return [value]; });
    console.log(values);

    api.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
        var index = values.indexOf(this.cell(rowLoop,'office:name').node().innerHTML);
        console.log(index);
        this.cell(rowLoop,'position:name').render( function(data, type, row) {
            return data+' ['+index+']';
        } )
    } );
}

Dissecting the code above, I can get the array for the legend here (lines 2-5 from above) . . .

    var api = this.api();
    var rows = api.rows().nodes();
    var values = $.map(api.column('office:name').data().sort().unique(), function(value, index) { return [value]; });
    console.log(values);

Result from the console:

Array [ "Edinburgh", "London", "New York", "San Francisco", "Sidney", "Singapore", "Tokyo" ]

. . . and looping through the rows I can get the correct index for each row (lines 7-9 from above) . . .

    api.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
        var index = values.indexOf(this.cell(rowLoop,'office:name').node().innerHTML);
        console.log(index);
    } );

Result from the console:

0
6
3
0
6
2
3
etc.

. . . but I'm struggling with how to insert the index into the Position cell in the table. I'm attempting to use cell().render() (lines 10-11 from above):

        this.cell(rowLoop,'position:name').render( function(data, type, row) {
            return data+' ['+index+']';
        } )

But after more closely reading the documentation for cell().render() I can clearly see this isn't the correct usage, however I'm lost as to what to do now.

I had previously just altered the innerHTML of the Position

this.cell(rowLoop,'position:name').node().innerHTML = this.cell(rowLoop,'position:name').node().innerHTML+' ['+index+']';

That works initially, but it also changes the innerHTML and each time I redraw the table it updates the innerHTML so that I end up with entries like Tokyo [6] [-1] after sorting once and Tokyo [6] [-1] [-1] after soring twice.

After I get this part then I'll work on furthering the callDrawback function to actually generate the Legend.

kthorngren

How about this:

    api.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
        var index = values.indexOf(this.cell(rowLoop,'office:name').node().innerHTML);
        var data = this.cell(rowLoop,'position:name').data();
        data = data.replace(/\[.*\]/g, '');
        this.cell(rowLoop,'position:name').data( data+' ['+index+']');
    } );

It gets the data form the cell. Uses regex to remove the previous index. Then writes the data back to the cell.

Kevin

Loren Maxwell

Thanks, Kevin.

That would work in this specific instance, but I was hoping for something that would be "data-agnostic". In this case, if I had an Office with brackets in the name then the regex solution wouldn't work.

I'd like to preserve the data but present it in a different way like the render function does, but I'm not sure how to tap into or adjust the render function from here. Not sure how to pass the index to it even if I could tap into it.

Loren Maxwell

Kevin your suggestion gave me another idea. It didn't work but I thought I'd put it here for so I can show everyone what I have tried.

First I added a render to for the position:

                    {
                        "data": "position",
                        "name": "position",
                        "render": function (data, type, row) { return data +' ['+row.office+']' }
                    },

Then I changed the drawCallback to preDrawCallback and updated the Office data with the index:

                preDrawCallback: function () {
                    var api = this.api();
                    var rows = api.rows().nodes();
                    var values = $.map(api.column('office:name').data().sort().unique(), function(value, index) { return [value]; });
                    console.log(values);

                    api.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
                        var index = values.indexOf(this.cell(rowLoop,'office:name').node().innerHTML);
                        console.log(index);
                        console.log('Before '+this.cell(rowLoop,'office:name').data());
                        this.cell(rowLoop,'office:name').data(index);
                        console.log('After '+this.cell(rowLoop,'office:name').data());
                    } );
                }

I can see on the console the Office field has been updated to the index, but in the Position field I get "Accountant [Tokyo]", so it still grabs the original data.

Also, when the table is redrawn (sort or another page) then the Office indexes all become -1.

kthorngren

I tried this with a different approach using the ajax DataFilter function to build the array of offices. Then used columns.render option to append the index. Here is the example:

http://live.datatables.net/dowoweju/1/edit

Not sure if this is a good approach but it seems to work.

Kevin

tangerine

I may be missing the point here, but couldn't you handle this at the database level?

A "legends" table would have "1 | Tokyo, 2 | Birmingham" etc. and your query would return a concatenation of "position" and "legend id" for the Position field.

Also, the "legends" table - or "key" - should be displayed outside the main table.

Loren Maxwell

@kthorngren, I appreciate the work on the ajax solution, but I think the regex one is better for my actual application.

I actually plan on showing the index in superscript so I would surround it with '<sup>'+index+'</sup>'. The regex should work since I don't anticipate any fields including a ```<sup>```` tag, but it's not "data-agnostic" like I was hoping (not quite sure I'm using that term properly, but I'm sure you get my meaning!).

I'm still curious as to if @allan can verify is there's no specific command that would achieve what I was trying to do with the following lines:

this.cell(rowLoop,'position:name').render( function(data, type, row) {
    return data+' ['+index+']';
} )

In other words, is there way to render the index with the position without changing the actual data itself?

@tangerine, a database solution would work, but I was hoping to make something self-contained within DataTables. Additionally, the Legend would reside in a <div id=legend"> or something similar somewhere else on the page. The rest of the JS function would fill that in based on the array.

Loren Maxwell

Here's the latest, working version, thanks to @kthorngren's regex suggestion:

http://ghsfha.org/datatables_cards_unique.html

I also changed from drawCallback to initComplete, which only runs once.

Still would love to hear from @allan on whether I can do this with something like a render function so that the data isn't affected.

kthorngren

I tried columns.render to set the display to the index, similar to your example above but it didn't work. Even though the console output looked like it should have set the index generated from the drawCallback or preDrawCallback it did not.

You example is looking good.

Kevin

allan

What you'll need to do is to create the legend data before the DataTables rendering function runs. If you are using ajax, that can be done with the xhr event, or jQuery's dataFilter callback:

var lookup = {};

...
ajax: {
  url: ...
  dataFilter: function ( str ) {
    var json = JSON.parse( str );
    ... pull out the legend information
  }
}

then the rendering function could be something like:

render: function ( data, type, row ) {
  return data +' ['+ lookup[ row.office ] + ']';
}

where the lookup is something like:

{ "Edinburgh": 0, "London": 1, ... }

You could use an array as well which would be easier to generate, but slower for the lookup.

The alternative would be to let the table initialise - have the rendering function cope with lookup not being defined, use the API to get the legend data (which in fairness is easier), use that to set the lookup and then rows().invalidate() the table.

Allan

Loren Maxwell

Thanks, @allan.

@kthorngren had put up a dataFilter example above, which I considered. I'll look at the xhr event as well.

I was also wondering if dataSrc might be an option. It looks like functionally it would do the same as the dataFilter, but the purpose of the dataSrc is "Data property or manipulation method for table data", so I thought conceptually it might fit better there if it's possible.

Anyway, it doesn't sound like there's a method to do what seems like the cleanest solution to me, which is to somehow update the render function by looping through the rows:

this.cell(rowLoop,'position:name')._SOME_RENDER_UPDATE_FUNCTION_(function(data, type, row) {
    return data+' ['+index+']';
} )

At any rate, I'll play around with dataFilter, dataSrc, and xhr.

As always I appreciate everyone's help!