Problems with default datetime in editor

TomBajzek

I'm having a strange problem with a datetime field in the editor: When I edit an existing row, the particular field in question displays the datetime field as [object Object] the first time I edit a row. After that, it always defaults to the current date in the editor field, no matter what the previously saved date is.

My Javascript code for the field in question is:

            {
                "label": "Date Due: ",
                "name": "duedate",
                "type": "datetime",
                "placeholder": "Choose Due Date",
                                "dateFormat": $.datepicker.ISO_8601,
                "opts": { firstDay: 0 }
            },

I do some computation server-side to facilitate the filtering requirements for this field. My PHP code for this field is:

        Field::inst( 'duedate' )
            ->validator( Validate::notEmpty() )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( function( $val, $data, $opts) {
                global $id;
                $filterDate = '';
                $displayDate = date("Y-m-d", strtotime( $val) );
                if ( strtotime($val) < strtotime('today') ) {
                    $filterDate = 'overdue';
                } elseif (strtotime($val) === strtotime('today')) {
                    $filterDate = 'today';
                } elseif (strtotime($val) === strtotime('tomorrow')) {
                    $filterDate = 'tomorrow';
                } else {
                    $filterDate = 'beyond';
                }
                //  Now build an object in JSON format
                $dueDateObj = (object) [
                    'filterDate' => $filterDate,
                    'displayDate' => $displayDate
                ];
                return $dueDateObj;
            } )
            ->setFormatter( function( $val, $data, $opts) {
                return date("Y-m-d", strtotime($val));      
            } ),

I've tried a couple of variations that have not helped, because I do't really see another way to do what I'm aiming at.

allan

"dateFormat": $.datepicker.ISO_8601,

This is a jQuery UI parameter. The datetime does not use jQuery UI, but date does (for legacy reasons - i.e. before we had our own date picker).

So there is a little confusion here - the first thing to address is, do you want to use jQuery UI date picker, or use the one built into Editor? The correct answer will be dependent on your site. i.e. if you are already using jQuery UI date picker else where, then it makes sense to continue using it.

Allan

TomBajzek

Hi Allan,

Since I use the jQuery UI datepicker elsewhere in the program, and only need the date (not the time), I changed the type of duedate to be a date. Now when I run this I get a Javascript error inside jQuery-UI, in _determineDate:

TypeError: i.getTime is not a function. (In 'i.getTime()', 'i.getTime' is undefined) at jquery-ui.min.js:3468:143.

I'm wondering if this is caused by the fact that I have the server return an object containing orthogonal data for the duedate field - perhaps this gets passed to jQuery UI which doesn't handle it.

Here is the current code for the duedate field and another field, contractStart, that does not exhibit this problem:

            {
                "label": "Date Due: ",
                "name": "duedate",
                "type": "date",
                "placeholder": "Choose Due Date",
                "dateFormat": $.datepicker.ISO_8601,
                "opts": { firstDay: 0 }
            },
            {
                "label": "Contract Start Date: ",
                "name": "contractStart",
                "className": "estReq",
                "placeholder": "Choose Start Date",
                "type": "date",
                "dateFormat": $.datepicker.ISO_8601
            },

Could the orthogonal data object as shown in the PHP code above be the problem here? If so, is there a way to proceed with this?

Thanks,
Tom

TomBajzek

I also then tried changing duedate from type date to datetime, thinking that would get rid of jquery-ui, which I was assuming couldn't deal with the object returned from the server PHP code. That did not fix it, either.

I don't have the option of not using jquery-ui because that is needed for other functions of the program.

The revised code for duedate that didn't make any difference is:

            {
                "label": "Date Due: ",
                "name": "duedate",
                "type": "datetime",
                "placeholder": "Choose Due Date",
                "format": "YYYY-MM-DD"
            },

I just wanted to let you know that I had tried this variation as well...

Tom

allan

Could you try using:

name: 'duedate.filterDate'

for your Editor initialisation code please? duedate is an object, as seen by the getFormatter you are using from the server-side. That would explain the [object Object]. It would also explain why jQuery UI is giving errors.

That said, that won't resolve the issue completely since Editor (client-side) will then be submitting the value to duedate.filterDate.

A better solution I would suggest would be to just have the server-side work with ISO8601 - remove everything else from the get formatter. Then if you need a different format on the client-side, do that client-side. See this example.

Allan

TomBajzek

Allan,

The example link is to this page. Did you have another page in mind?

Tom

colin

Bless him, he means well... He meant this one: https://editor.datatables.net/examples/dates/formatting-client.html

TomBajzek

Colin,

Thanks for giving me the correct link. I interpret this to be a suggestion to compute my orthogonal data (overdue, today, tomorrow, beyond) as duedate.filterDate on the client. So, then I would filter on duedate.filterDate (as computed on the client), and define my input field as duedate.displayDate, and the server would accept and return duedate only, which would be duedate.displayDate on the client.

Is this the correct interpretation of the advice from you and Allan?

Thanks,
Tom

allan

Oops - thanks for adding the link Colin!

My suggestion is that the server (in sending and receiving data) only ever works with ISO8601 formated data. So you have duedate = '2020-01-09' for example. Likewise for any other date fields.

Then on the client-side use the methods shown in the example Colin linked to format the date for the end user to understand (since ISO8601 isn't the most friendly). Those methods will automatically deal with the orthogonal formats for you.

Allan

TomBajzek

With this latest suggestion from Allan, I've solved the problems discussed here. Not all of these issues were evident in the original question. In the whole process I've learned several other things that I think might be useful to others. I'm posting an overview of what I've learned as it might be helpful to others.

I've learned useful things about processing date and time information, filtering data, and orthogonal data. Let me describe the situation:

The origin of this discussion was an attempt to write code that would allow me to filter a dueDate as to being overdue ( older than today ), today, tomorrow, or beyond ( later than tomorrow ), as required by my client's business case.

I began by computing this filtering data in PHP on the server, as I'm reasonably familiar with date manipulation in PHP. I was then returning this data as an object containing the displayDate and the filterDate ( as orthogonal data ). I'd been trying to use the jQuery DatePicker, as it is used elsewhere in the program. However, the jQuery DatePicker could not deal with the object I'd created to contain the orthogonal data, and I did not figure out a way to present jQuery with just what it needed.

I then followed Allan's suggestion to simply format the date on the server and do all of the other date manipulation work for this field on the client, so I deleted the code that computed the filter terms. I also realized in the process that I did not need to deliver the orthogonal data from the server to the client as most of the examples in the DataTables documentation do. Rather, I set up my definition of the dueDate field on the server to render the date in display form except when the type of data to be rendered was 'filter', in which case I returned the filterDate as overdue, today, tomorrow, or beyond, as used in the column filter routine. I believe that doing this made the filter code easier to read and understand than it would have been if I had tried to do that date computations within the filtering code itself.

To summarize several things I learned:

1. It can be very helpful to keep date processing on the server-side restricted to ISO-8601 format.
2. Orthogonal data can be created on the client as needed, when you are rendering a field that needs it. It does not need to be sent from the server. ( It is easy to unnecessarily generalize from examples you see. )
3. Beware of using features of jQuery, as the data as used in DataTables / Editor may not be directly consumable by jQuery. What you want to do may not be possible with the data in the form that DataTables / Editor uses.

Following are come code segments to illustrate how I've done this, as I think these might be helpful to others, especially if it's your first try at doing some of these types of things:

First is the code for computing definitions of the filter terms, overdue, today, tomorrow, and beyond ( I make no claim that these are the best possible ways to do this date manipulation, as this code is just my first solution. You can quite likely do better ):

    // Here we compute definitions for filtering tasks by dueDate
    // Note that we adjust to Eastern Time Zone, where this code will be used
        var year, month, date;
        var d = new Date();
    
        year = d.getUTCFullYear();
        month = d.getUTCMonth();
        date = d.getUTCDate();
        
        var today = new Date(year,month,date);
        var todayMs = today.getTime() - 60 * 60 * 5 * 1000;
        var tomorrow = new Date( 86400 * 1000 + today.getTime());
        var tomorrowMs = tomorrow.getTime() - 60 * 60 * 5 * 1000;
        var beyond = new Date( 86400 * 1000 + tomorrow.getTime());
        var beyondMs = beyond.getTime() - 60 * 60 * 5 * 1000;
        truth = true;
    // End of definitions and computations for filtering tasks

Next is the definition of the dueDate column in the client Javascript:

    {
        "data": "duedate",
        "className": "dFilter",
        "render": function( data, type, row ) {
            // If filtering, return overdue, today, tomorrow, beyond, as computed
            if (type === 'filter') {
                theDueDate = new Date(data);
                filterDate = '';                // Initialize empty
                if ( theDueDate.getTime() < todayMs ) {
                    filterDate = 'overdue';
                }
                if ( theDueDate.getTime() >= beyondMs ) {
                    filterDate = 'beyond';
                }
                if ( theDueDate.getTime() === todayMs ) {
                    filterDate = 'today';
                }
                if ( theDueDate.getTime() === tomorrowMs ) {
                    filterDate = 'tomorrow';
                }
                return filterDate;
            } else {
                // If not filtering, return the dueDate itself in displayDate form
                return data;
            }
        }
    },

Next is the column filter itself as it is applied to the dueDate field. ( Note that this filter is applied to the columns with the dFilter class ):

            this.api().columns('.dFilter').every( function () {
                var column = this;
                var that = this;
                var select = $('<select><option value="">All</option><option value="overdue">Overdue</option><option value="today">Today</option><option value="tomorrow">Tomorrow</option><option value"beyond">Beyond</option></select>')
                    .appendTo( $(column.header()) )
                    .on( 'change', function() {
                        that
                            .search ($(this).val() )
                            .draw();
                    } );
            } );

I post this in the hope that it might help someone else who is grappling with some of these issues by pulling together several of these issues into one posting.

Many thanks to Allan and Colin for leading me to a working solution,
Tom