editor optional date field

wcmanes

I need to have a date field that is optional. I'm running into two specific issues (maybe by design?) with this:

1. If I use the server side date validation feature a date entry is always required (verified this in the editor date field example). Turning off validation allows non-date values to be entered.

2. Without validation the field may be left blank, but:

a. The value that shows in the table is "0000-00-00". This may be related to a MySQL implementation, but I can force it to display blank in the table using mRender.

b. If a row with "blank" date is edited, the field value in the form shows today's date, which is save on update even if the user does not enter anything.

Is there a way to make it work such that a date field is optional (allowed to be blank), but can be validated and edited without requiring a value.

allan

No - it isn't by design, there is actually an error in the date validation function at the moment which rejects the field if it is given as an empty string (null). If you look in Validate.php you'll find the function `dateFormat` (at the bottom of the file) - replace it with:

[code]
public static function dateFormat( $val, $data, $opts ) {
$format = is_array($opts) ? $opts['format'] : $opts;

if ( $val === '' ) {
return true;
}

$date = date_create_from_format($format, $val);
if ( ! $date ) {
return isset( $opt['message'] ) ?
$opts['message'] :
"Date is not in the expected format";
}
return true;
}
[/code]

That will allow the validation to pass with an empty string (unless you use `dateFormat_required` as the validator).

Regards,
Allan

qwewq

This comment did not solved the problem.
When in the database there is no date (0000-00-00) the editor ever shows the todays date when clicking on "edit" - so the user must delete the date-entry manually before saving.

allan

@qwewq: The function above is for validation, not a get formatter, which it sounds like what you want. The date formatter function (in Format.php) looks like this, and should cope with what you mention:

[code]
public static function date_sql_to_format( $val, $data, $opts ) {
$date = explode(" ", $val);
$date = date_create_from_format('Y-m-d', $date[0]);

// Allow empty strings or invalid dates
if ( $date ) {
return date_format( $date, $opts );
}
return '';
}
[/code]

Could you link to the page showing the problem and confirm which version of Editor and the Editor libraries you are using?

Thanks,
Allan

jgcaudet

Hi Allan.
I have the same problem : "When in the database there is no date (0000-00-00) the editor ever shows the todays date when clicking on "edit" - so the user must delete the date-entry manually before saving"
How can I solve it ?

Thanks

allan

I'll take a look at this tomorrow and get back to you. Are you using jQuery UI's date picker? Also, what would you expect to happen - a date picker showing the year 0?

Thanks,
Allan

jgcaudet

Hi Allan
Yes jQuery UI's date picker. I hope that if there is no date (0000-00-00) or is null in the database the editor shows that field empty.
Thanks

allan

I've got a feeling this might be the PHP formatter of the date actually. I'll experiment a bit and get back to you.

Regards,
Allan

rclayton

I am also having trouble with this. Was there ever an official solution?

I used this formatter function to handle the default value msql was returning:

        ->getFormatter( function ($val ) {
                if ($val != "0000-00-00 00:00:00") {
                    $date = date_create_from_format('Y-m-d H:i:s', $val);
                    return date_format( $date, 'm/d/Y H:i' );
                } else { 
                    return null; 
                }
            })

HTH -- Rob

allan

I've just committed a change to Editor which will correctly handle an empty date input set as null on the database. Note that this doesn't mean that 0000-00-00 isn't a valid date, it is, and is still handled as such, but null data is correctly handled now.

This fix will be in Editor 1.3.2 which will be released shortly.

Allan

worsham

I am seeing something like this in the current implementation of Editor. I am creating an entry in the table that has a 0000-00-00 date, using inline editing if I edit any other field in that row editor is passing back the current date in place of the null in the post headers.

allan

Can you show me the code use are using please, and ideally a link to the page in question so I can try to debug it?

Thanks,
Allan

worsham

The page is inside a corporate network so can't access it. Here are the js definitions for editor and some snippets of the json being passed back and forth. I tried with 2000-00-00 and it changed that as well.

$(document).ready(function() {
  var editor = new $.fn.dataTable.Editor( {
    "ajax": "./ajax-reqs.php",
    "table": "#reqs",
    "idSrc": "id",
    "fields": [
      {
        "label": "Priority",
        "name": "priority"
      },
      {
        "label": "Req #",
        "name": "req"
      },
      {
        "label": "Position",
        "name": "position"
      },
      {
        type:  "select",
        label: "Grade",
        name: "grade",
        options: [
              "4",
              "6",
              "8",
              "10",
              "11",
              "12"
          ]
      },
      {
        label: "Owner",
        name: "owner",
        type: "autoComplete",
        "opts": {
          "source": "./autoperson.php",
          "minLength" : "2"
        }
      },
      {
        type:  "select",
        label: "Funding",
        name: "funding",
        options: [
              "Incremental",
              "Backfill"
          ]
      },
      {
        label: "Program",
        name: "org",
        type: "select",
        options: [
        <?php
$query = "SELECT * FROM program WHERE org_id=".$org_id;

$result = runSqlQuery($db, $query);

while($row = mysqli_fetch_array($result))
  echo "\"".$row["name"]."\",";
        ?>

          ]
      },
      {
        type:  "select",
        label: "Location",
        name: "location",
        options: [
              "BLR",
              "BXB",
              "SEA",
              "SJC",
              "China"
          ]
      },
      {
        label: "Created Date",
        name: "created",
        type:       "date",
                dateFormat: 'yy-mm-dd'
      },
      {
        label: "Approved Date",
        name: "approved",
        type:       "date",
                dateFormat: 'yy-mm-dd'
      },
      {
        label : "Age",
        name : "age",
        type : "readonly"
      },
      {
        type:  "select",
          label: "Current State",
          name:  "current-state",
          options: [
              "Open",
              "Interviewing",
              "Identified",
              "Pending",
              "Extended",
              "Accepted"
          ]


      },
      {
        "label": "Comments",
        "name": "comments"
      },
      {
        "label": "Jumpball",
        "name": "jumpball"
      }
    ]
  } );

      editor
        .on( 'open', function ( e, type ) {
            if ( type === 'inline' ) {
                // Listen for a tab key event when inline editing
                $(document).on( 'keydown.editor', function ( e ) {
                    if ( e.keyCode === 9 ) {
                        e.preventDefault();
 
                        // Find the cell that is currently being edited
                        var cell = $('div.DTE').parent();
 
                        if ( e.shiftKey && cell.prev().length && cell.prev().index() !== 0 ) {
                            // One cell to the left (skipping the first column)
                            cell.prev().click();
                        }
                        else if ( e.shiftKey ) {
                            // Up to the previous row
                            cell.parent().prev().children().last(0).click();
                        }
                        else if ( cell.next().length ) {
                            // One cell to the right
                            cell.next().click();
                        }
                        else {
                            // Down to the next row
                            cell.parent().next().children().eq(1).click();
                        }
                    }
                } );
            }
        } )
        .on( 'close', function () {
            $(document).off( 'keydown.editor' );
        } );
 
    $('#reqs').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this, {
            submitOnBlur: true
        } );
    } );



  table = $('#reqs').DataTable( {
    "dom": 'Tfrtip',
    "ajax": "./ajax-reqs.php?id=<?php echo($_COOKIE['ops']);?>&type=1",
    "stateSave" : "true",
    "columns": [
      { data: null, defaultContent: '', orderable: false },
      {
        "data": "priority"
      },
      {
        "data": "req"
      },
      {
        "data": "position"
      },
      {
        "data": "grade"
      },
      {
        "data": "owner"
      },
      {
        "data": "funding"
      },
      {
        "data": "org"
      },
      {
        "data": "location"
      },
      {
        "data": "created"
      },
      {
        "data": "approved"
      },
      {
        "data": "age"
      },
      {
        "data": "current-state"
      },
      {
        "data": "comments",
         "render": function ( data, type, full, meta ) {
      return type === 'display' && data.length > 80 ?
        '<span title="'+data+'">'+data.substr( 0, 78 )+'...</span>' :
        data;
    }
      },
      {
        "data": "jumpball"
      }
    ],
    "tableTools": {
      "sRowSelect": "os",
       "sRowSelector": "td:first-child",
      "aButtons": [
        { "sExtends": "editor_remove", "editor": editor },
        {
                    "sExtends":    "text",
                    "fnClick": function ( nButton, oConfig, oFlash ) {
                      newReq();
                    }
                }
      ],
      "align":"left"
    }
  } );

  var colvis = new $.fn.dataTable.ColVis( table );
 
    $( colvis.button() ).insertAfter('div.header-open');

The new field request:

action=create&org_id=1

then response

{
    "data": [
        {
            "id": "45",
            "priority": "",
            "req": "",
            "position": "",
            "grade": "0",
            "owner": "",
            "funding": "",
            "org": "",
            "location": "",
            "created": "2015-03-20",
            "approved": "0000-00-00",
            "age": "1",
            "current-state": "Open",
            "comments": "",
            "jumpball": "0"
        }
    ]
}

Then an edit where only priority was changed on the ui

action  edit
data[age]   1
data[approved]  2015-03-20
data[comments]  
data[created]   2015-03-20
data[current-state] Open
data[funding]   
data[grade] 
data[jumpball]  0
data[location]  
data[org]   
data[owner] 
data[position]  
data[priority]  1
data[req]   
id  45

Which shows that the approved date is being set to the current date.

Thanks!

Stephen

allan

Hi Stephen,

Thanks for the details. I'm afraid I still don't have an immediate answer for you here - I can't quite figure out what is going wrong. The data you have above for "The new field request" looks really short - it doesn't submit the other fields?

However, the response shows that the data is obviously created okay. Then you do an edit and it submits the created data incorrectly - very odd!

Are you using jQuery UI's date picker, a Bootstrap date picker, Chrome's date picker, or something else?

Thanks,
Allan

worsham

I created my own version of new / create as I wanted it to insert a blank row so that's why it looks a little different.

I am using the jQuery UI date picker for the two date fields.

I have played with it a bit more and the only time it updates is if is an "illegal" date. I changed it to be 2000-01-01 and its fine but if i put 2000-00-00 it modifies it.

Thanks,

-Stephen

worsham

Allan,

Any chance to take a look?

Thanks

allan

Sorry I didn't get a chance to reply before.

It sounds like this is part of the behaviour of jQuery UI's date picker control - invalid dates become "today".

Perhaps the best bet would be to use null rather than 0000-00-00 in the database for fields which don't have a value?

Allan