Allow empty or existing value not in list when submit editor form

asle · June 22

I have a <select> list in records from the main db publikasjon with values from a field isbn.litt_isbn. I pick the first available (=0) value from the DB field as an option and after submitting it is marked used (=1). This works. But I want to be able to accept existing field value and also no(empty) value. I have problems getting theValidate::: working and I get an error when submitting

<b>Fatal error</b>
:  Uncaught Error: Call to a member function optional() on string in /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Validate.php:174
Stack trace:
#0 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Validate.php(1032): DataTables\Editor\Validate::_common('\xEF\xBB\xBF978-82-345-0...', 'isbn')
#1 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Field.php(778): DataTables\Editor\Validate::DataTables\Editor\{closure}('\xEF\xBB\xBF978-82-345-0...', Array, Object(DataTables\Editor\Field), Array)
#2 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor.php(848): DataTables\Editor\Field-&gt;validate(Array, Object(DataTables\Editor), '4096')
#3 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor.php(1071): DataTables\Editor-&gt;validate(Array, Array)
#4 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor.php(716): DataTables\Editor-&gt;_process(Array)
#5 /home/1/m/mfu/www/code/js/Editor-PHP/controllers/upload-mfu.php(118): DataTables\Editor-&gt;process(Array)
#6 {main}
  thrown in <b>/home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Validate.php</b>
on line <b>174</b>
<br/>

Her is the field in js:

{
    label: 'ISBN:',
    name: 'publikasjon.litt_isbn',
    type: "select",
    placeholder: 'Ingen ISBN valgt',
    placeholderDisabled: false
},                   //other fields after this...

And the php code for the field:

Field::inst( 'publikasjon.litt_isbn' )
            ->options(function($db, $field) {
                $options = [];
                // Get the first available ISBN with status = 0
                $row = $db
                    ->query('select')
                    ->get(['isbn'])
                    ->table('isbn')
                    ->where('status', 0)
                    ->order('id ASC')
                    ->limit(1)
                    ->exec()
                    ->fetch();

                if ($row) {
                    $options[] = [
                        'label' => $row['isbn'],
                        'value' => $row['isbn']
                    ];
                }

                // Safely get the current value
                $current = null;
                if ($field !== null && method_exists($field, 'val')) {
                    $current = $field->val(); 
                }

                // If it's not the same as the one above, and it's not already in the list, add it
                if (!empty($current) && (!isset($row['isbn']) || $row['isbn'] !== $current)) {
            $options[] = [
                'label' => $current . ' (tidligere valgt)',
                'value' => $current
            ];
        }
                return $options;
                })
                ->validator(
                Validate::dbValues('isbn', 'isbn'),
                ValidateOptions::inst()->allowEmpty(true)
            )
        ,

Is this the correct way to do the sql to get the first field? I thin there is something I dont have correct in the Validate part but I can not find it out. So my goal is 1) Show current value if exists and accept it 2) Show first available number from isbn.isbn field. 3) Accept empty value on submit

asle · June 22

I think the error is with the Validate. It does not get the value to validate. When I run this

->validator(
        Validate::dbValues(),
        ValidateOptions::inst()->allowEmpty(true)
    )

I get an error:
Table for database value check is not defined for field publikasjon.litt_isbn
But if I try this:

->validator(
        Validate::dbValues(),
        ValidateOptions::inst()
            ->table('isbn')
            ->field('isbn')
            ->allowEmpty(true)
    )

I get this error:

Fatal error: Uncaught Error: Call to undefined method DataTables\Editor\ValidateOptions::table() in /home/1/m/mfu/www/code/js/Editor-PHP/controllers/upload-mfu.php:78 Stack trace: #0 {main} thrown in /home/1/m/mfu/www/code/js/Editor-PHP/controllers/upload-mfu.php on line 78

allan · June 23

The ValidateOptions class doesn't have table or field methods. See the API reference docs for the class here.

            ->validator(
                Validate::dbValues('isbn', 'isbn'),
                ValidateOptions::inst()->allowEmpty(true)
            )

from your first post looks more correct to me. You need to specify the table / column information for the validator since it can't derive it automatically from the options function (since it is a custom options function - which looks fine).

What happens if you use that?

Allan

asle · June 23

I tried your suggestion now and when I try to add a value or no value and update I get this:

<b>Fatal error</b>
:  Uncaught Error: Call to a member function optional() on string in /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Validate.php:174
Stack trace:
#0 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Validate.php(1032): DataTables\Editor\Validate::_common('\xEF\xBB\xBF978-82-345-0...', 'isbn')
#1 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Field.php(778): DataTables\Editor\Validate::DataTables\Editor\{closure}('\xEF\xBB\xBF978-82-345-0...', Array, Object(DataTables\Editor\Field), Array)
#2 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor.php(848): DataTables\Editor\Field-&gt;validate(Array, Object(DataTables\Editor), '4096')
#3 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor.php(1071): DataTables\Editor-&gt;validate(Array, Array)
#4 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor.php(716): DataTables\Editor-&gt;_process(Array)
#5 /home/1/m/mfu/www/code/js/Editor-PHP/controllers/upload-mfu.php(116): DataTables\Editor-&gt;process(Array)
#6 {main}
  thrown in <b>/home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Validate.php</b>
on line <b>174</b>
<br/>

The value I am sending to the table publikasjon while the select value is coming from table isbn . Is there some logic I am missing? Also The existing value (if present) does not show. It shows always "Ingen ISBN valgt" if I leave the placeholder code in the field definition:

{
    label: 'ISBN:',
    name: 'publikasjon.litt_isbn',
    type: "select",
    placeholder: 'Ingen ISBN valgt',
    placeholderDisabled: false
},

rf1234 · June 24

If you select the placeholder "No ISBN selected" (translated it from Swedish) you are sending exactly that to the server. That is not going to work because it is not an empty string. Try this please:

{
    label: 'ISBN:',
    name: 'publikasjon.litt_isbn',
    type: "select",
    placeholder: 'Ingen ISBN valgt',
    placeholderDisabled: false,
    placeholderValue: "" //the placeholder value is empty
},

asle · June 24

Thanks, I still get the same error:
<b>Fatal error</b>
: Uncaught Error: Call to a member function optional() on string in /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Validate.php:174
Stack trace:
#0 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Validate.php(1032): DataTables\Editor\Validate::_common('', 'isbn')
#1 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Field.php(778): DataTables\Editor\Validate::DataTables\Editor{closure}('', Array, Object(DataTables\Editor\Field), Array)
#2 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor.php(848): DataTables\Editor\Field->validate(Array, Object(DataTables\Editor), '4096')
#3 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor.php(1071): DataTables\Editor->validate(Array, Array)
#4 /home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor.php(716): DataTables\Editor->_process(Array)
#5 /home/1/m/mfu/www/code/js/Editor-PHP/controllers/upload-mfu.php(116): DataTables\Editor->process(Array)
#6 {main}
thrown in <b>/home/1/m/mfu/www/code/js/Editor-PHP/lib/Editor/Validate.php</b>
on line <b>174</b>
<br/>

Also the current value is still not showing. If the record has a value the select still shows only 2 options, "Ingen ISBN valgt"(selected) and the first available value from isbn.isbn.

allan · June 24

Gah - I messed up the parameters for the dbValues method - sorry. Per the docs it is:

dbValues( ValidateOptions $cfg=null, string $column=null, string $table=null, Database $db=null, array $valid=null)

So you would use:

->validator(
  Validate::dbValues(
    ValidateOptions::inst()->allowEmpty(true),
    'isbn', // column
    'isbn' // table
  ),

Allan

asle · June 24

Thanks, this solved the update. I am able to update now (after adding the missing parenthes, no problem). The value, empty value or the value from "isbn.isbn" is added to the record but I am not able to read it back. The value shows in Datatables but in Editor it still is "Ingen ISBN valgt". The select does not show the current value. Any idea why? Here is my code now on the controller:

Field::inst('publikasjon.litt_isbn')
        ->options(function ($db, $field) {
        $options = [];
        // Get the first available ISBN (status = 0)
        $row = $db
            ->query('select')
            ->get(['isbn'])
            ->table('isbn')
            ->where('status', 0)
            ->order('id ASC')
            ->limit(1)
            ->exec()
            ->fetch();
        if ($row) {
            $options[] = [
                'label' => $row['isbn'],
                'value' => $row['isbn']
            ];
        }
        // Get the current value in the record being edited
            $current = null;
                if ($field !== null && method_exists($field, 'val')) {
            $current = $field->val();
                }
            if (!empty($current) && (!isset($row['isbn']) || $row['isbn'] !== $current)) {
                $options[] = [
                    'label' => $current . ' (tidligere valgt)',
                    'value' => $current
                ];
            }
        return $options;
             }) 
            ->validator(
                Validate::dbValues(
                    ValidateOptions::inst()->allowEmpty(true),
                    'isbn', // column
                    'isbn' // table
                ))

rf1234 · June 24

If you are using my and Allan's code suggestions everything should be fine - since you are not using a getFormatter which isn't required because ISBN is a simple string.

Hard to tell without a test case, I guess. Could you provide one, please?

asle · June 24

I am not sure what you mean but I made a test page here that you can create, edit and delete records.
https://dev.mfu.no/code/admin-test.html
If you look at the child data of a record you can find one with a valid ISBN number but it does not show in Editor. If you add the ISBN number suggested you will see it in the child row but not in Editor. The next time you edit or create a record you see a new ISBN number so it seems that logic is working.

rf1234 · June 25

Hi asle,

I used your test page and created two entries. Yes, there is only one ISBN to select but that is either ok or you have a problem with your options instance which looks a bit "weird" to me.

I created one record that has the one and only ISBN and another one that doesn't have the ISBN filled. Looks fine in the data table and also in the respective Editor pop up.

As I can see you are loading only this one ISBN option from the server:

Roland

allan · June 25

It doesn't show in Editor because the options list only has a single option - which doesn't match the value of the ISBN in the row that was selected, hence it shows the placeholder.

You have ->limit(1) in the options for getting the values for the ISBN. I don't quite understand what the intention is here - what's the point of having a select which only has a single option? Also, presumably you don't want to just list all ISBN's that would be insane. Is free text with an ISBN validator an option for you? As I say, I'm not clear on what the intention with this field is.

One thing, it looks like you are expecting the options to update per row. That isn't the case for the way it is configured. If you need to load different options for select based on a row, you need to use dependent() and have it make an Ajax request to get the options to show for that row.

Allan

rf1234 · June 25

Something in addition to Allan's last remark. If you are requiring updated options per row you would need to load them when needed.

That could be on "select" or on "preOpen" for example. You would pass the id to the server and retrieve the array of label / value pairs.

editor
    .on('preOpen', function(e, mode, action) {  
        var id = 0;
        var that = this;
        if ( action === "edit" ) {
            var selected = yourDataTable.row( {selected: true} );
            if (selected.any()) {
                id = selected.data().id;
            }
        }
        $.ajax({
            type: "POST",
            url: 'your url to get the Options',
            data: {
                id: id            
            },
            dataType: "json",
            success: function (retrievedOptions) {   
                that.field("yourSelectField").update(retrievedOptions);
            }
        });
    });

allan · June 25

Sorry - yes, use preOpen rather than dependent() for this. Good point @rf124!

Allan

asle · June 25

Sorry for the confusion. I will try to explain. I have a table ISBN that contains 1000 valid ISBN numbers. Every record (publication) should get a unique ISBN but not required when the record is created. When creating or editing a record my idea was to show the first free ISBN number. When the number is used it is marked "1" so the next record can choose the next free ISBN number (marked 0). This logic works now. The choosen number is added and marked "1". If the record has a ISBN number, display it. If not show "No valid ISBN". So I force to use the first free number in the ISBN list. Choosing "No valid ISBN" and updating should delete the current number and choosing the displayed number enters that in the record.

Is using a <select> list a bad approach for this? As you see the ISBN is correct in the datatable so it would also be if I used a text field in Editor. I am a bit stuck since I thought the only problem was displaying the current value but I understand this is not so logical.

rf1234 · June 25

It doesn't show in Editor because the options list only has a single option - which doesn't match the value of the ISBN in the row that was selected, hence it shows the placeholder.

Since you have that limitation all you would need to do is to update the options on "preOpen". Just use my logic above and retrieve the relevant options which are:
a) "create": The empty option plus the next available ISBN
b) "edit": The empty option plus the next available ISBN OR the selected ISBN (if any)

Don't know whether that is working with the placeholder and the placeholderValue. If not just modify my code like this for example:

that.field("yourSelectField").update([{ label: "Ingen ISBN valgt", value: "" }]);
that.field("yourSelectField").update(retrievedOptions, true);

rf1234 · June 25

It could be something like this (provided that the placeholder and placeholderValue stuff still works). You would only need to call the server in case the record doesn't exist yet or no ISBN has been selected yet.

editor
    .on('preOpen', function(e, mode, action) { 
        var that = this;
        if ( action === "edit" ) {
            var selected = yourDataTable.row( {selected: true} );
            if (selected.any()) {
                var isbn = selected.data().isbn;
                if ( isbn > "" ) {
                   that.field("yourSelectField").update([{ label: isbn, value: isbn }]);
                   return;
                }
            }
        }
        $.ajax({
            type: "POST",
            url: 'your url to get the Options',
            dataType: "json",
            success: function (retrievedOptions) {  
                that.field("yourSelectField").update(retrievedOptions);
            }
        });
    });

This would retrieve the option in case it hasn't been saved yet (using Editor's db handler and Editor`s "raw" method):

$stmt = ('SELECT isbn as label, isbn as value
            FROM isbn
           WHERE status = 0
        ORDER BY id ASC
              LIMIT 1');  
$result = $db ->raw()
              ->exec($stmt);
return $result->fetchAll(PDO::FETCH_ASSOC);

asle · June 26

Thanks. I don't want to ask before really trying to understand but my knowledge is not good enough in this case. To be clear: I have always retrieved the ISBN value in Datatables but Editor is the problem. There are some things that may seem logical but I fail to understand them. In my PHP code from June 24 (after Allan helped me get the ValidateOptions: right) I fetch the first available ISBN

    ->exec()
            ->fetch();
        if ($row) {
            $options[] = [
                'label' => $row['isbn'],
                'value' => $row['isbn']
            ];
        }......

but I still don't understand why I can not get the current value since I thought $field->val() is available:

// Get the current value in the record being edited
            $current = null;
                if ($field !== null && method_exists($field, 'val')) {
            $current = $field->val();
                }
            if (!empty($current) && (!isset($row['isbn']) || $row['isbn'] !== $current)) {
                $options[] = [
                    'label' => $current . ' (tidligere valgt)',
                    'value' => $current
                ];
            }
        return $options;

I am not sure what code now is to be replaced.
1) @rf1234 Where do i put the code in my javascript from in the block and what do I replace with
yourDataTable.row
yourSelectField (I tried publikasjon.isbn and isbn.isbn)

editor
    .on('preOpen', function(e, mode, action) {
        var that = this;
        if ( action === "edit" ) {
            var selected = yourDataTable.row( {selected: true} );
            if (selected.any()) {
                var isbn = selected.data().isbn;
                if ( isbn > "" ) {
                   that.field("yourSelectField").update([{ label: isbn, value: isbn }]);
                   return;
                }
            }
        }
        $.ajax({
            type: "POST",
            url: 'your url to get the Options',
            dataType: "json",
            success: function (retrievedOptions) { 
                that.field("yourSelectField").update(retrievedOptions);
            }
        });
    });

2) This seems to replace the PHP code where I fetch the first available ISBN? Not sure what to replace in my PHP code.

$stmt = ('SELECT isbn as label, isbn as value
            FROM isbn
           WHERE status = 0
        ORDER BY id ASC
              LIMIT 1'); 
$result = $db ->raw()
              ->exec($stmt);
return $result->fetchAll(PDO::FETCH_ASSOC);

rf1234 · June 26

Where do i put the code in my javascript from in the block and what do I replace with
yourDataTable.row
yourSelectField (I tried publikasjon.isbn and isbn.isbn)

I don't know what your datatable is called. So you would need to replace "yourDataTable" with the real name of your data table. Your select field should be the first one you mentioned because you are editing publikasjon and not the isbn table

This seems to replace the PHP code where I fetch the first available ISBN? Not sure what to replace in my PHP code.

In a way it does. But your code is executed only once for all records retrieved: There is always only one options array loaded when the data table is reloaded. But you need to load the options each time a new record is created or edited. That means you need a separate ajax call to retrieve the options.

Since you are no longer using the options retrieved on ajaxReload of the entire table you can also get rid of it and just rely on the individual ajax calls.

rf1234 · June 27

I tried publikasjon.isbn and isbn.isbn

"publikasjon.isbn"
seems to be wrong though
Above the field was called:
"publikasjon.litt_isbn"
And that should be your "select" field unless you changed its name

In my code example it should go like this accordingly:

var selected = yourDataTable.row( {selected: true} );
if (selected.any()) {
  var isbn = selected.data().publikasjon.litt_isbn;
  if ( isbn > "" ) {
      that.field("publikasjon.litt_isbn").update([{ label: isbn, value: isbn }]);
      return;
   }
}

asle · June 29

Fantastic @rf1234! I am so grateful for you (and @allan ) hanging in on this. I almost gave up but your comment about the correct field names solved it (almost). If I edit a record with an registered ISBN It shows the current ISBN

If I create a new record, I get the first available ISBN number! Updating works fine:

There is a problem when I edit a record with an empty ISBN. I get a line with an empty value and not the first available ISBN number like when I create a new record:

I have now this JS for the propen function:

editor
    .on('preOpen', function(e, mode, action) {
        var that = this;
        if ( action === "edit" ) { console.log('edit record!');
            var selected = table.row( {selected: true} );
            if (selected.any()) {
                var isbn = selected.data().publikasjon.litt_isbn; console.log('current isbn: ' + isbn);
                if ( isbn > "" ) {
                   that.field("publikasjon.litt_isbn").update([{ label: isbn, value: isbn }]);
                   return;
                }
            }
        }
        $.ajax({
            type: "POST",
            url: '/code/js/Editor-PHP/controllers/get-isbn.php',
            dataType: "json",
            success: function (retrievedOptions) {
                that.field("publikasjon.litt_isbn").update(retrievedOptions);
            }
        });
    });

Here is my code for get-isbn.php

<?php

// DataTables PHP library
include( "../lib/DataTables.php" );

// Set proper content type
header('Content-Type: application/json');

use \DataTables\Database;

// Prepare the SQL statement
$sql = 'SELECT isbn AS label, isbn AS value
        FROM isbn
        WHERE status = 0
        ORDER BY id ASC
        LIMIT 1';

try {
    // Use the existing $db object provided by DataTables.php
    $result = $db->raw()
                 ->exec($sql)
                 ->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($result);
} catch (Exception $e) {
    echo json_encode([
        "error" => $e->getMessage()
    ]);
}

The reponse for get-isbn.php is like this:

[
  {
    "label": "978-82-345-0763-2\r",
    "value": "978-82-345-0763-2\r"
  }
]

I don't understand why I don't get the updated select list when the ISBN is empty but I suspect it is not handled in the JS since I only have a logic for if ISBN is NOT empty?

if ( isbn > "" ) {
                   that.field("publikasjon.litt_isbn").update([{ label: isbn, value: isbn }]);
                   return;
                }

rf1234 · June 29

The check for an empty ISBN should work, but it looks like it doesn't. If you are editing a record with an empty ISBN these two lines of code should not be executed.

if ( isbn > "" ) {
  that.field("publikasjon.litt_isbn").update([{ label: isbn, value: isbn }]);
  return;
}

It seems that they are being exectuted. Because the empty option is added and then the "return" command is executed. That shouldn't happen.

Please check this with your debugger and potentially change the "if" statement to e.g.
if ( isbn > "0" )
or whatever is suitable.

asle · June 29

Wow @rf1234, help on a Sunday! Yes that did it! Now the ISBN select is displayed correctly wheb I edit a record both new and existing records with no ISBN.
I understand more now how the the post and requests go. Thank you again @rf1234 and @allan! You are the best!

if ( isbn > "0" ) {. // this checks values over e.g. "978-82-345-0745-8"
  that.field("publikasjon.litt_isbn").update([{ label: isbn, value: isbn }]);
  return;
}

rf1234 · June 30

Glad you got it sorted!

[
  {
    "label": "978-82-345-0763-2\r",
    "value": "978-82-345-0763-2\r"
  }
]

I see you have those line feeds in there. Do you also save them in the database? If not I would get rid of them. That is probably also the reason why "if ( isbn > "" )" didn't work because you had the (invisible) line feeds even in the empty string?! But I am just guessing here...

Roland

asle · June 30

Thanks I checked the isbn table and it seems something got in there when I imported so I am cleaning it up!

Allow empty or existing value not in list when submit editor form

Allow empty or existing value not in list when submit editor form

Answers

Howdy, Stranger!

Categories

DataTables

Allow empty or existing value not in list when submit editor form

Allow empty or existing value not in list when submit editor form

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables