Treat blank value as a different value for ordering

gb691

Hi,

I have a column in my DataTable in which either a date will be shown (where one exists in the database) or it will be blank. Presently, when sorting by this column ascending, all the blanks are shown first, before the actual dates. Is there a way to say "in this column full of dates, treat any blank values as a really far off date, e.g. 01/01/3000" ?

Many thanks for your help in advance.

allan

A couple of options. The first is the "Absolute" sorting plugin which will keep specific values at the end of the table regardless of sorting.

If that isn't what you want, then you'll need to use a rendering function and orthogonal data to have the empty value return as a far off date (or perhaps more usefully, the integer value of that).

Do you want a hand with either of those approaches?

Allan

gb691

Hi @allan ,

Many thanks for your response.

I'm currently using the columns.render property to sanitise the data in this column. The data in this column sourced from a database will either be a date in the format yyyymmdd or any other nonsense data. Unfortunately I'm not able to sanitise the data that is returned from the database. So, within the render function I'm currently creating a javascript Date object if the data's in the "correct" format, or blank if it's anything else. However, I tried adding another if(type === "sort") within the render function, expecting that this will fire whenever the table is ordered by this column, but it doesn't appear to.

{ 
                    data: "idCardCollectedDate",                    
                    title: "ID Card Collected Date",
                    render: function(data, type, row) {
                        if(type === "display" || type === "filter") {
                            if(data.match(/\d{8}/)) {
                                var s = data.substring(0,4) + "-" + data.substring(4,6) + "-" + data.substring(6,8);
                                var d = new Date(s);
                                if(d instanceof Date && !isNaN(d)) {
                                    return d.toLocaleDateString("en-GB");
                                }
                            }                           
                            return "";
                        }
                        return data;
                    },
                    visible: true
                }

if(type === "sort") {
                            if(data.match(/\d{8}/)) {
                                var s = data.substring(0,4) + "-" + data.substring(4,6) + "-" + data.substring(6,8);
                                var d = new Date(s);
                                if(d instanceof Date && !isNaN(d)) {
                                    return d.toLocaleDateString("en-GB");
                                }
                            }                           
                            return new Date("3000-01-01");
                        }

I feel like I'm not quite understanding how and when the render function is called.

Sadly, I'm also not able to convert the "idCardCollectedDate" data into a JSON object with additional attributes for the orthogonal data.

allan

However, I tried adding another if(type === "sort") within the render function, expecting that this will fire whenever the table is ordered by this column,

It will trigger once per cell - DataTables caches the value until it is invalidated.

There is a proviso here that the above only applies if you are using client-side processing? If you are using server-side processing (serverSide) then it is the server that does the data sorting, and possibly that is what you running into?

Allan