SearchPanes - conditional format of list item

montoyam · April 2020

Is there a way to add formatting or classes to particular list items in the Search Panel. For example, if Due Date is past due, make the due date (or the whole row preferred) red and bold

sandy · April 2020

Hi @montoyam,

Yes it is possible to do this. Take a look at this example. You'll see in the example there are a couple of commented lines depending on what date you want to compare against.

There are a couple of things going on here. Firstly we are using columnDefs to set the config for the data columns SearchPane, effectively avoiding all of the others. We can then use searchPanes.dtOpts to set the properties of the SearchPanes own DataTable. Then it is just a case of rendering the only column dependant on the date from the column and your chosen comparison date. We can use a columns.render function for this. This function needs to return the data in the same form as it does in the other panes, hence the spans and classes. We add a div with a background-color set for the expired dates.

Hope this helps,
Sandy

montoyam · April 2020

I have having trouble figuring out where to put that code in mine. When I put it in my DataTable columns[] definitions, nothing appears to happen. I already have a SearchPanes section that applies to all the search panes, so not sure how to target only searchpane #4 which has my dates.

            var SubmissionsTable = $('#Submissions').DataTable({
                searchPanes: {
                    cascadePanes: true,
                    viewTotal: true,
                    controls: false,
                    layout: 'columns-6',
                    emptyMessage: "<i><b>none</b></i>",
                    dtOpts: {
                        "columnDefs": [{
                            "targets": 8,
                            "render": function (data, type, row, meta) {
                                 var today = new Date();
                                var compare = new Date(data);
                                return today.getTime() > compare.getTime() ?
                                    '<div style="background-color:red;><span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span></div>' :
                                    '<span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span>';
                            }
                        }]
                    },
                    targets: [4]
                },
                dom: 'PBfrtip',
                ajax: {
                    url: 'api/Submissions',
                    type: 'post',
                    //grab the status of the 'Show Active Only' toggle and send it to the form 
                        data: function (d) {
                            //d['FilterActive'] = ($("#activeFilter").prop("checked") === true) ? 1 : 0;
                        }
                },
                columns: [
                    //first column is blank and using CSS (class details-control) an icon will be here if note records exist                
                        {
                            "class": "details-control",
                            "orderable": false,
                            "data": null,
                            "defaultContent": ""
                        },
                    //*******************
                    { data: "Submissions.SubmissionID" },
                    {
                        data: "SubmissionStatus.SubmissionStatus",
                        searchPanes: {
                            show: true,
                            preSelect: ['Not Assigned', 'Assigned']
                        }
                    },
                    { data: "AssignedTo.UserName" },
                    { data: "SubmissionTypes.SubmissionType" },
                    { data: "Submissions.EmployeeName" },
                    {
                        data: "Submissions.NeedsCCReview",
                        render: function (data, type, row) {
                            return (data == 1) ? "Yes" : "No";
                        },
                    },
                    { data: "CCAssignedTo.UserName" },
                    {
                        data: "Submissions.DueDate",
                        searchPanes: {
                            show: true
/*                            ,
                            render: function (data, type, row, meta) {
                                var today = new Date(); 
                                var compare = new Date(data);
                                return today.getTime() > compare.getTime() ? 
                                    '<div style="background-color:red;><span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span></div>' :
                                    '<span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span>';
                            }
*/
                        }
                    },
                    { data: "ResolutionMethods.ResolutionMethod" },
                    { data: "Submissions.EnteredBy" },
                    { data: "Submissions.RecordAdded" }
                ],
                //for that first column, when the row is created get rid of the class if no notes exist
                    createdRow: function (row, data, line, cells) {
                        if (data.vw_Submissions.HasNotes == 0 ) {
                            $(cells[0]).removeClass('details-control')
                        }
                    },
                //*******************
                select:'single',
                lengthChange: false,
                autoWidth: false,
                responsive: true,
                buttons: [
                    { extend: 'create', editor: SubmissionsEditor },
                    {
                        //if submission is Resolved, then submit/edit not allowed, only close edit screen.
                        extend: 'edit',
                        text: 'Edit/View',
                        editor: SubmissionsEditor,
                        formButtons: {
                            text: function () {
                                var selected = SubmissionsTable.row({ selected: true });

                                if (selected.data().Submissions.ResolutionMethodID == 0) {
                                    return 'Update';
                                } else {
                                    return 'Close';
                                }
                            },

                            action: function () {
                                var selected = SubmissionsTable.row({ selected: true });
                                if (selected.data().Submissions.ResolutionMethodID == 0) {
                                    this.submit();
                                } else {
                                    this.close();
                                }
                            }
                        }
                    }

                ]
            });

With the code above, I am getting an error for each SearchPane:

DataTables warning: table id=DataTables_Table_0 - Requested unknown parameter '0' for row 0, column 0.

kthorngren · April 2020

I believe that the the dtOpts in searchPanes is applied to the Datatable created for the searchPane. Sandy's example has this:

        show: true,
        dtOpts:{
          "columnDefs": [ {
            "targets": 0,

But you have targets: 8. It should be targets: 0. Its strange to think about but the SearchPane is a Dattaable which you deal with separate from the main table

Kevin

montoyam · April 2020

i later saw that I was not using dtOpts in my column[] section (line 62 above), so I changed it to this and still no luck (no error, but no style happening);

                    {
                        data: "Submissions.DueDate",
                        searchPanes: {
                            show: true,
                            dtOpts: {
                                render: function (data, type, row, meta) {
                                    var today = new Date();
                                    var compare = new Date(data);
                                    return today.getTime() > compare.getTime() ?
                                        '<div style="background-color:red;><span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span></div>' :
                                        '<span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span>';
                                }
                            }
                        }
                    },

kthorngren · April 2020

I noticed your comment came in the same time as mine. Just want to make sure you see my above comment.

Kevin

montoyam · April 2020

thanks. that worked to change it to column zero. but, why doesn't the dtOpts work within the column[] section like the other searchPane options do, like show and preSelect?

kthorngren · April 2020

Like I mentioned earlier the dtOtps is for the searchPane Datatable not the main table column. You need to define columnDefs etc just like Sandy's original example.

Kevin

montoyam · April 2020

ah, got it. So since the searchpane is a datatable separate from the main one that contains all the data. What if I need my rule to be, if (DueDate < today && ResolutionMethodID == 0) since ResolutionMethod is not contained in the DueDate searchpane data.

kthorngren · April 2020

Since SearchPanes is a grouping of like data how would you differentiate between (DueDate < today && ResolutionMethodID == 0) and (DueDate < today && ResolutionMethodID != 0)? I may be mistaken but you could end up with both in the same SearchPane row.

Kevin

kthorngren · April 2020

Depending on what you have and what you want to display in the SearchPane you could do a little manipulation with columns.searchPanes.orthogonal. In this example I pass along the row data I want using a span element with an HTML5 data attribute using columns.render in the main table.

EDIT: A more interesting/clear example:
http://live.datatables.net/xuvuxewi/1/edit

Hope this gives you some ideas.

Kevin

montoyam · April 2020

as is typical with me, I am having trouble with the syntax referencing my data.

In your code you have

var val = $(data).data('value');

I thought mine would be

var val = $(data).data('Submissions.ResolutionMethodID');

but that is coming back as undefined.
my datatable is setup as, i think, as 'normal' and I THINK I have everything else like you have in your example.

            var SubmissionsTable = $('#Submissions').DataTable({
                searchPanes: {
                    cascadePanes: true,
                    viewTotal: true,
                    controls: false,
                    layout: 'columns-6',
                    emptyMessage: "<i><b>none</b></i>"
                },
                dom: 'PBfrtip',
                ajax: {
                    url: 'api/Submissions',
                    type: 'post',
                    //grab the status of the 'Show Active Only' toggle and send it to the form data
                    data: function (d) {
                        //d['FilterActive'] = ($("#activeFilter").prop("checked") === true) ? 1 : 0;
                    }
                },
                columns: [
                    //first column is blank and using CSS (class details-control) an icon will be here if note records exist                
                    {
                        "class": "details-control",
                        "orderable": false,
                        "data": null,
                        "defaultContent": ""
                    },
                    //*******************
                    { data: "Submissions.SubmissionID" },
                    {
                        data: "SubmissionStatus.SubmissionStatus",
                        searchPanes: {
                            show: true,
                            preSelect: ['Not Assigned', 'Assigned']
                        }
                    },
                    { data: "AssignedTo.UserName" },
                    { data: "SubmissionTypes.SubmissionType" },
                    { data: "Submissions.EmployeeName" },
                    {
                        data: "Submissions.NeedsCCReview",
                        render: function (data, type, row) {
                            return (data == 1) ? "Yes" : "No";
                        },
                    },
                    { data: "CCAssignedTo.UserName" },
                    {
                        data: "Submissions.DueDate",
                        searchPanes: {
                            show: true,
                            orthogonal: 'sp',
                            dtOpts: {
                                columnDefs: [{
                                targets: 0,
                                    render: function (data, type, row, meta) {
                                        var today = new Date();
                                        var compare = new Date(data);
                                        var val = $(data).data('Submissions.ResolutionMethodID');
                                        console.log(val);
                                        return (val == 0) && (today.getTime() > compare.getTime()) ? 
                                            '<div style="background-color:red;><span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span></div>' :
                                            '<span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span>';
                                    }
                                }]
                            }
                        },
                        render: function (data, type, row) {
                            if (type === 'sp') {
                                return '<span data-value="' + row.Submissions.ResolutionMethodID + '">' + data + '</span>';
                            }
                            return data;
                        }
                    },
                    { data: "ResolutionMethods.ResolutionMethod" },
                    { data: "Submissions.EnteredBy" },
                    { data: "Submissions.RecordAdded" }
                ],
                //for that first column, when the row is created get rid of the class if no notes exist
                    createdRow: function (row, data, line, cells) {
                        if (data.vw_Submissions.HasNotes == 0 ) {
                            $(cells[0]).removeClass('details-control')
                        }
                    },
                //*******************
                select:'single',
                lengthChange: false,
                autoWidth: false,
                responsive: true,
                buttons: [
                    { extend: 'create', editor: SubmissionsEditor },
                    {
                        //if submission is Resolved, then submit/edit not allowed, only close edit screen.
                        extend: 'edit',
                        text: 'Edit/View',
                        editor: SubmissionsEditor,
                        formButtons: {
                            text: function () {
                                var selected = SubmissionsTable.row({ selected: true });

                                if (selected.data().Submissions.ResolutionMethodID == 0) {
                                    return 'Update';
                                } else {
                                    return 'Close';
                                }
                            },

                            action: function () {
                                var selected = SubmissionsTable.row({ selected: true });
                                if (selected.data().Submissions.ResolutionMethodID == 0) {
                                    this.submit();
                                } else {
                                    this.close();
                                }
                            }
                        }
                    }

                ]
            });

montoyam · April 2020

line 57 above is where my trouble seems to start

kthorngren · April 2020

You have:

return '<span data-value="' + row.Submissions.ResolutionMethodID + '">' + data + '</span>';

The actual data attribute is value so you need:

var val = $(data).data('value');

Here is a tutorial on using HTM5 data attributes:
https://www.sitepoint.com/how-why-use-html5-custom-data-attributes/

Kevin

montoyam · April 2020

ahhh, I see. that 'value' was not referring to your data column called 'value', but rather the html5 data. Got it.

Yes, I see what you mean by you could get two dates (one past due and one not past due) but that is ok. The fact that it is color coded helps one make sens of that.

Also, I see that dtOpts does work within the column[] definitions, I must have just coded it incorrectly.

So, here is the finished code...thank you so much Keven and Sandy. I have been pulling my hair out on this one feature all day.

montoyam · April 2020

                    {
                        data: "Submissions.DueDate",
                        searchPanes: {
                            show: true,
                            orthogonal: 'sp',
                            dtOpts: {
                                columnDefs: [{
                                    targets: 0,
                                    render: function (data, type, row, meta) {
                                        var today = new Date();
                                        var compare = new Date(data);
                                        var val = $(data).data('value');
                                        return (val == -1) ?
                                            '<div style="background-color:red;><span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span></div>' :
                                            '<span class="dtsp-name"">' + data + '</span><span class="dtsp-pill">' + row.total + '</span>';
                                    }
                                }]
                            }
                        },
                        render: function (data, type, row) {
                            if (type === 'sp') {
                                var today = new Date();
                                var compare = new Date(data);
                                var resolutionID = row.Submissions.ResolutionMethodID;
                                var isPastDue = (today.getTime() > compare.getTime() && resolutionID == 0) ? -1 : 0; //use -1 so past due sort to top
                                return '<span data-value="' + isPastDue + '">' + data + '</span>';
                            }
                            return data;
                        }
                    },

kthorngren · April 2020

SearchPanes is a bit of a tricky one to learn how to use. Glad you got it working for your solution!

@sandy may have a better solution for this.

Kevin

sandy · April 2020

Looks good to me

Glad it works for you @montoyam and thank you @kthorngren for the help.

Cheers,
Sandy

SearchPanes - conditional format of list item

SearchPanes - conditional format of list item

Answers

Howdy, Stranger!

Categories

DataTables

SearchPanes - conditional format of list item

SearchPanes - conditional format of list item

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables