Input not valid for inline and native modal editing datetime

Skrocki

I've been confused about this for weeks and ignored it because I have my own editing page which worked fine, using the jQuery datetime picker. I guess this would be a good time to say I also can't get that date-time picker to work in place of he datatable version you guys developed. Yours is excellent too, but I haven't been able to get yours to work independently on the pages that I've created from scratch.

But that is not my biggest concern today. I'm having a problem with the jQuery datetime picker on my a custom built pages, so I thought I would tried you work around and get the inline picker working. Now I've spent hours on it this evening and looked up all kinds of examples and comments and replies from the forums but I just cannot get it to work. No matter what combination of formatting or what order anything is in, I get the 'Input not Valid' error every single time.

I can direct you to my sandbox with fictional data and you can replicate the issue using the 'Inspection' column. Can anyone help me know what I'[m doing fundamentally wrong so this datetime picker packaged with DataTables doesn't work anywhere at any time? It is of Interest at the date only Fields work just fine, it's only the datetime fields that I'm having a problem with.

Thanks, Steve

https://sandbox.claimpenguin.com/public/dataTable/claimtracker.php

rf1234

I would need to see your PHP setFormatter for the dateTime value on the server.

This is being sent to the server:

based on this input:

That looks ok to me. I am in Cyprus at the moment. Hence Chrome uses GMT+0300 as the timezone which is "Eastern European DST".

Your set formatter must be able to handle the format that is being sent and convert it to your expected database format.

This setFormatter might work if your database has the usual dateTime format:

Field::inst( 'detes_activity.date_time_inspection' )
    ->setFormatter( function($val, $data, $opts) {
        if ( is_null($val) || $val <= '' ) {
            return null;
        }
        $dateTime = new DateTime($val);
        return $dateTime->format('Y-m-d H:i:s');
    }),

I tried it here and it works:
https://onlinephp.io/c/22bff

This should be the format your database expects:

If you don't want those seconds you could do:

return $dateTime->format('Y-m-d H:i:00');

Which gives you this:

Skrocki

Thank you very much brother. I appreciate your time and knowledge. Your solution worked perfectly!

That's a cool sandbox site too. I've never seen it before but I'm gonna try it out.

Skrocki

Oops I spoke too soon...your fix does work, but how do I show 'n/j/Y g:i a' format on the table and still save to sql format? I tried varieties with Validate:: and getFormatter but no love the way I was trying it.

`<?php
require $_SERVER['DOCUMENT_ROOT'] . '/controllers/components/globalconfig.php';
include $_SERVER['DOCUMENT_ROOT'] . '/modules/claimtracker/dataTable/php/lib/DataTables.php';

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;

// Build our Editor instance with SQL and process the data coming from _POST
Editor::inst($db, 'deets_insured', 'deets_insured.claimtrackerid')
->where('deets_insured.usergroup', $usergroup)
->fields(
Field::inst('deeets_insured.claimtrackerid'),
Field::inst('deets_insured.claim_number'),
Field::inst('deets_insured.insured_name'),
Field::inst('deets_insured.insured_address'),
Field::inst('deets_insured.insured_city'),
Field::inst('deets_insured.insured_state'),
Field::inst('deets_insured.insured_zip'),
Field::inst('deets_insured.insured_county'),
Field::inst('deets_insured.insured_phone'),
Field::inst('deets_activity.date_of_loss')
->validator(Validate::dateFormat('n/j/Y',))
->getFormatter(Format::dateSqlToFormat('n/j/Y'))
->setFormatter(Format::dateFormatToSql('n/j/Y')),

      Field::inst('deets_activity.date_time_reported')
           ->validator(Validate::dateFormat('n/j/Y g:i a'))
           ->getFormatter(Format::datetime('Y-m-d H:i:s', 'n/j/Y g:i a'))
           ->setFormatter(Format::datetime('YYYY-MM-DD hh:mm:ss', 'Y-m-d H:i:s')),

      Field::inst('deets_activity.date_time_assigned')
           ->setFormatter(function ($val, $data, $opts) {
               if (is_null($val) || $val <= '') {
                   return NULL;
               }
               $dateTime = new DateTime($val);
               return $dateTime->format('Y-m-d H:i:s');
           }),

// Field::inst('deets_activity.date_time_assigned')
// ->validator(Validate::dateFormat('n/j/Y g:i a'))
// ->getFormatter(Format::datetime('Y-m-d H:i:s', 'n/j/Y g:i a'))
// ->setFormatter(Format::datetime('YYYY-MM-DD hh:mm:ss', 'Y-m-d H:i:s')),

// Field::inst('detes_activity.date_time_inspection')
// ->validator(Validate::dateFormat('n/j/Y g:i a'))
// ->getFormatter(Format::dateSqlToFormat('Y-m-d H:i:s'))
// ->setFormatter(Format::dateFormatToSql('YYYY-MM-DD hh:mm:ss')),

      Field::inst('deets_activity.date_time_inspection'),
          $val = "format('n/j/Y g:i a')";
           ->setFormatter(function ($val, $data, $opts) {
               if (is_null($val) || $val <= '') {
                   return NULL;
               }
               $dateTime = new DateTime($val);
               return $dateTime->format('Y-m-d H:i:s');
           }),

// Field::inst( 'timestampdiff(DAY, now(), deets_activity.date_time_inspection)', 'ago' )

      Field::inst('deets_activity.current_status')
           ->options(options::inst()
                            ->table('list_items where list_id = 36 AND usergroup = 0 OR usergroup = ' . $usergroup . ' order by list_item')
                            ->value('list_item')
                            ->label('list_item')
           ),

      Field::inst('deets_inspection.inspection_scope')
           ->options(
               options::inst()
                      ->table('list_items where list_id = 23 AND usergroup = 0 OR usergroup = ' . $usergroup . ' order by list_item')
                      ->value('list_item')
                      ->label('list_item')
           ),
      Field::inst('deets_inspection.assignment_notes'),
      Field::inst('deets_insured.claimtrackerid'),
      Field::inst('detes_insured.claimtrackerid'),
      Field::inst('detes_activity.date_time_inspection'),
      Field::inst('deets_insured.userid'),
      Field::inst('deets_insured.usergroup'),
      Field::inst('deets_insured.username')
  )
//->leftJoin('deets_insured', 'deets_insured.claimtrackerid', '=', 'deets_insured.claimtrackerid')
//->leftJoin('list_items', 'list_items.list_item', '=', 'deets_activity.current_status')
  ->leftJoin('deets_insured as detes_insured', 'detes_insured.claimtrackerid', '=', 'deets_insured.claimtrackerid')
  ->leftJoin('deets_insured as deeets_insured', 'deeets_insured.claimtrackerid', '=', 'deets_insured.claimtrackerid')
  ->leftJoin('deets_activity as detes_activity', 'detes_activity.claimtrackerid', '=', 'deets_insured.claimtrackerid')
  ->leftJoin('deets_activity', 'deets_activity.claimtrackerid', '=', 'deets_insured.claimtrackerid')
  ->leftJoin('deets_inspection', 'deets_inspection.claimtrackerid', '=', 'deets_insured.claimtrackerid')
  ->process($_POST)
  ->json();

`

Note that I have implemented your change on date_time_inspected and date_time_assigned, so those are finally saving at least. But I'd rather use a more familiar format for the visible table. I tried:

->validator(Validate::dateFormat('n/j/Y g:i a'))
and
->getFormatter(Format::datetime('n/j/Y g:i a'))

How do I control the visible format? With my code on this question, you can see I used the syntax presented in the tutorial I think.

Thanks again for any help that anyone can provide.

rf1234

Oops I spoke too soon...your fix does work, but how do I show 'n/j/Y g:i a' format on the table and still save to sql format? I tried varieties with Validate:: and getFormatter but no love the way I was trying it.

If you use Editor and don't change the table values outside of Editor the rendering in the data table is not relevant for saving values in sql format. On the other hand you need to make sure that Editor can deal with the format you return from the server.

All you need is either
a) a getFormatter to send the rendered format from the server, or
b) a renderer on the client side to render the database format if you don't use a getFormatter on the server

The kind of validator you can use depends on whether you choose a) or b).

I personally prefer solution a) but many people would choose b).

Since I do a) I use moment.js on the client side on the rare occasions that I do date comparisons client side. I serve dates in two user languages. So I use momentLocale to make sure I am refering to the right date format (in my case "de" which is DD.MM.YYYY or "en-gb" which is DD/MM/YYYY).

If you search the forum and include "rf1234" in the search string you should find more on this.

rf1234

No rule without exception ...

Since I always return DD.MM.YYYY or DD/MM/YYYY from the server I do special dateTime formatting client side as well. But that is only for rendering in the data table. Hence it has no effect on server setFormatting!

Here I use moment.js to render the file update_time (which is a timestamp in the usual database format)´depending on the user language:

if (lang == 'de') {         
    returnString += ( lineBreak + rowFile[ix].updaterName + ', am: ' + 
        moment(rowFile[ix].update_time.replace(/ /g, ' ')).format('DD.MM.YYYY, HH:mm [Uhr]: '));
} else {
    returnString += ( lineBreak + rowFile[ix].updaterName + ', on: ' + 
        moment(rowFile[ix].update_time.replace(/ /g, ' ')).format('DD/MM/YYYY, @ hh:mm A: ') );
}