NotInList event...

Lapointe

Hi all

Hope you are fine.

I'm back.... Some new project start.

I know Christmas is december 25, but .....

Does somebody know if there is a way to create "new value" event in a select

                    Field::inst( 'users.ID_service' )
                        ->options( extOptions::inst()
                            ->pre(array(array('value'=>'','label'=>''),array('value'=>'-1','label'=>'New value'))
                            ) 
                            ->table( 'services' )
                                ->value( 'ID' )
                                ->label('Name')
                        )
                        ->setFormatter( 'Format::ifEmpty', null ),
                    Field::inst( .......

In this sample if I select 'New value', I'd like to popup ask box, get the new value, write it in the table 'services' field 'Name' (used for label in select), get the ID of this new record, refresh the select content (or add this new value in) and select this value.

Writing this seem to be possible, but I'm not sur to be capable to create a pretty new method for select input, that can be called easily for each time needed, as (for sample)

                    Field::inst( 'users.ID_service' )
                        ->options( extOptions::inst()
                            ->pre(array(array('value'=>'','label'=>''))
                            ) 
                            ->allowAdd('LabelForAddNewValue') // or ....
                            ->table( 'services' )
                                ->value( 'ID' )
                                ->label('Name')
                        )
                        ->setFormatter( 'Format::ifEmpty', null ),
                    Field::inst( .......

Best regards

rf1234

Hard to understand what you want to achieve... So I am guessing ...

You want to dynamically add options to a select field that don't exist yet? If that is true I found a way to do this some time ago. Allan commented on it as well.
https://datatables.net/forums/discussion/47399

You can only do this with a selectize field not with the built-in select field type. But that shouldn't be an obstacle.

Lapointe

Thank Rf...
It is what I was looking for
Using PHP class should be better (perhaps Allan will do it one day), but your sample is usefull.

rf1234

Using PHP class should be better (perhaps Allan will do it one day), but your sample is usefull.

I agree, it should be a built-in Editor feature. Just like a couple of work-arounds I posted in this forum ...

Lapointe

Hi. In fact I'm trying to get correct results using selectize and actually can't.
no error throw and nothing dispayed in selectize content:

Element containt

<div class="selectize-dropdown single" style="display: none; visibility: visible; width: 271.476px; top: 413.403px; left: 691.424px;">
<div class="selectize-dropdown-content">
<div data-value="1" data-selectable="" class="option">Angoisse</div>
<div data-value="2" data-selectable="" class="option">Troubles du sommeil</div></div>
</div>

PHP get data

                    Field::inst( 'recettes.ID_Situation' )
                        ->options( Options::inst()
                            ->table('situations')
                                ->value('ID')
                                ->label('Libelle')
                                ->order( 'Libelle asc' )
                            ->where( function($q) use ($UsrID) {
                                $q->where( function($r) use ($UsrID) {
                                    $r->where('ID_Utilisateur',$UsrID,'=');
                                    $r->or_where('ID_Utilisateur',null,'=');
                                });
                            })
                        )   
                        ->validator( Validate::notEmpty( ValidateOptions::inst()
                            ->message( 'la situation doit être renseignée...' ) 
                            )
                        )
                        ->setFormatter( 'Format::ifEmpty', null )   
                    , 

Event catcher
````
->on( 'writeCreate', function ( $editor, $id, $values ) use( $TblName, $NewDataFldList) {

                if ($NewDataFldList)
                    foreach ($NewDataFldList as $NewDataList) {
                        $NewData = $values[$TblName][$NewDataList['fld']];
                        if ( ( ! is_numeric( $NewData ) ) || ( count( explode('.', $NewData) ) > 1 ) ) { //strpos didn't work with a period!!
                            addNewDataFromSelect( $editor->db(), $TblName, $id, $NewDataList, $NewData);
                        }
                    };      

            } )

add function

function addNewDataFromSelect( $db, $TblName, $id, $NewDataList, $TblFld='Libelle', $NewData ) {
$result = $db->insert( $NewDataList['tbl'],
array ($TblFld => $NewData),
array ( 'id' )
);

$db->raw()
   ->bind( ':fk', $result->insertId() )
   ->bind( ':id', $id )
   ->exec( 'UPDATE '.$TblName.' 
            SET '.$NewDataList['fld'].' = :fk  
            WHERE id = :id' 
    );

};

many select in form catcher

        $NewDataFldList = array(
            array('tbl'=>'situations', 'fld'=>'ID_Situation'),
            array('tbl'=>'prestations', 'fld'=>'ID_Prestation'),
            array('tbl'=>'modes_reglement', 'fld'=>'ID_ModeReglement')
        );

Field description

            { name: 'recettes.ID_Situation', label: 'Situation',
                // type: 'select'
                type:  'selectize' 
                //,
                // opts: {
                    // create: true,
                    // createFilter: function(val) {
                      // return ( isNaN(val) || val.indexOf('.') > -1 ) ? val : false;
                    // },
                    // maxItems: 10,
                    // maxOptions: 20,
                    // openOnFocus: false,
                    // allowEmptyOption: false,
                    // placeholder: 'Description',               
                    // // render: {
                        // // option_create: function(data, escape) {
                            // // return '<div class=\'create\'>Nouvelle<strong>'
                                   // // + escape(data.input) + '</strong>&hellip;</div>';
                        // // }
                    // // }
                // },
            },              

```
I comment opts for test but nothing run corectly..
I use selectize.css and .js
Can help ?

rf1234

You don't need any special HTML to use selectize instead of the built-in select field type. But of course you need selectize itself (js and css) plus the plug-in for Editor.

It is all described here:
https://editor.datatables.net/plug-ins/field-type/editor.selectize

The CDN links in the doc above point to a very old version of selectize. I personally use version 0.12.6 which is a bit newer.

It is important to use the standalone version of selectize.

This is useful to understand how to use it:
https://github.com/selectize/selectize.js/blob/master/docs/usage.md

Here are a couple of use cases to make life easier:

a) special rendering, single select:

}, {
    label: lang === 'de' ? 'Investor:' : 'Investor:',
    name:  "contract.creditor_id", //render investor name, city
    type: "selectize", 
    opts: {
        create: false,
        maxItems: 1,
        openOnFocus: false,
        allowEmptyOption: false,
        placeholder: lang === 'de' ? 
            "Bitte einen Investor wählen" : 
            "Please select an investor",   
        render: {
            option: function(data, escape) {
                var strong = ''; var eStrong = '';
                var label = data.label;
                if (label.substr(0, 8) === '<MATCH: ') {
                    strong = "<strong>";
                    eStrong = "</strong>";
                }
                return '<div>'+strong+escape(label)+eStrong+'</div>';
            }
        }
    }
}, {

b) multi select with placeholder:

}, {
    label: lang === 'de' ? 
            'Wählen Sie einen oder mehrere Verträge über Basisgeschäfte:' : 
            'Select one or more underlying contracts:',
    name:  "underlying[].id", //render serial, instrument, type, number
    type: "selectize", 
    opts: {
        create: false,
        maxItems: null,
        openOnFocus: true,
        allowEmptyOption: true,
        placeholder: lang === 'de' ? 
            'Bitte Grundgeschäft auswählen' : 'Please select underlying'
        }
}, {

c) single select, allowing to create new options on the fly:

fields: [ {
        label: lang === 'de' ? 'Kontrahent:' : 'Counterparty:',
        name:  "contract.gov_manual_creditor_id", //render creditor name
        type: "selectize", 
        opts: {
            create: true,
            createFilter: function(val) {
              return ( isNaN(val) || val.indexOf('.') > -1 ) ? val : false;
            },
            maxItems: 1,
            openOnFocus: false,
            allowEmptyOption: false,
            placeholder: lang === 'de' ? 
                "Bitte Kontrahenten wählen oder hinzufügen" : 
                "Please select a Counterparty or add one",                
            render: {
                option_create: function(data, escape) {
                    var add = lang === 'de' ? "Neu: " : "Add ";      
                    return '<div class="create">' + add + '<strong>'
                           + escape(data.input) + '</strong>&hellip;</div>';
                }
              }
            },
    },  {

Lapointe

@rf1234
Hi
I'm trying to use selectize to allow user to add new options, adding then in source table, not just adding text value.

As I said I place the ->on( 'writeCreate' in the

            Editor::inst( $db, $TblName )
                ->fields( .....
                )
                ->on( 'writeCreate', function ( $editor, $id, $values )  use( $TblName, $NewDataFldList) {

So I do not understand where to intercept the process (writeCreate not fired before editor display an error message)

rf1234

Yep, it is all done here
https://datatables.net/forums/discussion/47399
Including the database insert of the newly created value.

You have an SQL error that you need to fix first ...

Lapointe

Hi rf

First of all thank you for your answer.

Using selectize, when adding a value, id seem to be same as label by default...

writeCreate is fired after data has been submited.

So error is fired, because trying to set incorrect value.

I do the job using preCreate / preEdit that are fired before writing editor fields to server like :

                ->on( 'preCreate', function ( $editor, $values )  use( $TblName, $NewDataFldList, $UsrID, $IsAdmin) {

                    if ($NewDataFldList)
                        foreach ($NewDataFldList as $NewDataList) {
                            $NewData = $values[$TblName][$NewDataList['fld']];
                            if ( ( ! is_numeric( $NewData ) ) || ( count( explode('.', $NewData) ) > 1 ) ) {
                                //on doiit créer la donnée dans la table des options, avec le bon usrID
                                $NewOptionID = AddNewValueToOptionsTable($NewDataList['tbl'], $NewData, (($IsAdmin) ? null : $UsrID));
                                if ($NewOptionID) {
                                    $editor
                                        ->Field($TblName.'.'.$NewDataList['fld'])
                                        ->setValue($NewOptionID)
                                        ;
                                }
                            }
                        };

                } )

adding new data in source options table, setting the value to resulting ID (added value) and no more.
In JS I do refresh option field content as

        $('select', editor.field('recettes.ID_Situation').node()).change(function() {
    $.ajax ({
                                url: 'getdata.php',
                                method: 'GET',
                                dataType: 'json',
                                data: {
                                    table: 'situations',
                                    labelField: 'Libelle',
                                    valueField: 'situations.ID',
                                    EmptyAllowed: '0',
                                    order: 'Libelle asc'
                                },
                                success: function ( data ) {
                                    editor.field('recettes.ID_Situation').update( data )
                                }
                            })
                        })
    });

Getdata process convert parameters to sql sentence and exec

(SELECT DISTINCT  situations.ID as 'value', Libelle as 'label' FROM  situations  ORDER BY Libelle asc) 

What I'm looking for is to allow option list field update in the same process as adding new data to options table (without js refresh)
Do you (or Allan) know if it is possible to update (refresh shoul be the correct term, because same parameters as initial opts for option list field) option list field content in editor->on event ?

rf1234

Hi Lapointe,

the SQL error you are getting has it all: You are trying to insert 'aqsz' into an ID field. It is actually not you but Editor that is trying to do that. Why? Selectize passes a newly created value to the server and you don't check whether the value is numeric (hence a selected ID of an existing option) or not numeric (hence a newly created option which cannot have an ID yet because it needs to be inserted into the database first).

If I am reading this from my old post I am a bit surprised myself:

I added two new event listeners 'writeEdit' and 'writeCreate' which refers to the parent table not to the table that contains the options. Here I check whether the field passed from the client 'contract.gov_manual_creditor_id' actually contains an id or a new string entered by the user. If it contains a string instead of the id (and the users cannot enter an integer number at the front end) I insert that value into the options table and update the foreign key in the parent table as well which previously was filled with NULL by the options instance.

I don't recall exactly why the options instance fills the id field in the parent table with NULL in case a non-numeric value is passed.

But you need to make sure the foreign key ID field may contain NULL values by checking your database configuration. That's for sure. Here is my field definition of the foreign key:

To make sure Editor doesn't try to update the foreign key with a string you can check and pass NULL in that case. For some reason it wasn't required for me ...

Just checked it with MySQLWorkbench: What does MySQL do if you want to update a nullable integer field with a string? Answer is: It produces a warning and it inserts 0. Interesting. Didn't care about that because it worked ...

I ran this query and you can see the results:

That's the warning:

6 row(s) affected, 6 warning(s): 1366 Incorrect integer value: 'bla' for column 'gov_manual_creditor_id' at row 8 ...

0 or null doesn't really matter in my case: both are invalid ID values - and are updated right after. But this of course has other implications as well: Since the foreign key can temporarily contain null or 0 referential integrity in your DBMS doesn't work for this ID field!! You can't have RI with a nullable field anyway ... So if you have referential integrity defined for your foreign key you must remove it first - OR you must have a dummy value in the child table, its ID being 0. (Didn't test that - but it might work as well. But then you may not have ON DELETE CASCADE either ...).

If you add this code here you make sure Editor doesn't try to update your foreign key with a string value but uses null or 0. The SQL error should be gone - unless you have referential integrity defined.

->on('validatedCreate', function ( $editor, $values ) {   
    $manualId = $values['recettes']['ID_Situation'];
    if ( ( ! is_numeric( $manualId ) ) || 
         ( count( explode('.', $manualId) ) > 1 ) ) {
        //if you don't set the field at all and the db default is NULL => will work
        $editor->field('recettes.ID_Situation')->set( false );
        //alternatively you can set the field with null or 0 if you like
        $editor->field('recettes.ID_Situation')->setValue( 0 );
    }
})
->on('validatedEdit', function ( $editor, $id, $values ) {   
    $manualId = $values['recettes']['ID_Situation'];
    if ( ( ! is_numeric( $manualId ) ) || 
         ( count( explode('.', $manualId) ) > 1 ) ) {
        //if you don't set the field at all and the db default is NULL => will work
        $editor->field('recettes.ID_Situation')->set( false );
        //alternatively you can set the field with null or 0 if you like
        $editor->field('recettes.ID_Situation')->setValue( 0 );
    }
})

Bonne journée
Roland

Lapointe

Hi @rf1234 @allan @kthorngren

I did work to get correct (not perfect) solution allowing user to add new data to select source table using selectize 0.12.
because of option list need to be filtred, when adding data to source table I need to insert additional information ($usrID) to this table.

php editor fields

                    Field::inst( 'recettes.ID_Client' )
                        ->options( Options::inst()
                            ->table('clients')
                                ->value('ID')
                                ->label(array('Nom','Prenom'))
                                ->render(function($row){
                                    return $row['Nom'].' '.$row['Prenom'];
                                })
                                ->where( function($q) use ($UsrID,$CurCli) {
                                    $q ->where('ID',(is_null($CurCli))?0:$CurCli,(is_null($CurCli))?'>':'=');
                                    $q ->where('ID_Utilisateur',$UsrID,'=');
                            })
                        )
                        ->validator( Validate::notEmpty( ValidateOptions::inst()
                            ->message( 'le client doit être renseigné...' )
                            )
                        )
                    ,
                    Field::inst( 'civilites.Libelle'),

PHP editor event catching

                ->on( 'preCreate', function ( $editor, $values )  use( $TblName, $NewDataFldList, $UsrID, $IsAdmin) {
                    if ($NewDataFldList) return AddDataToOptionList($editor, $values, $TblName, $NewDataFldList, (($IsAdmin) ? null : $UsrID));
                } )
                ->on( 'preEdit', function ( $editor, $id, $values )  use( $TblName, $NewDataFldList, $UsrID, $IsAdmin) {
                    if ($NewDataFldList) return AddDataToOptionList($editor, $values, $TblName, $NewDataFldList, (($IsAdmin) ? null : $UsrID));
                } )

PHP function called from event

function AddDataToOptionList($editor, $values, $TblName, $NewDataFldList, $UsrID, $TblFld='Libelle'){
    global $db;
    foreach ($NewDataFldList as $NewDataList) {
        $NewData = $values[$TblName][$NewDataList['fld']];
        if ( ((strlen($NewData) > 0) && ( ! is_numeric( $NewData ) )) || ( count( explode('.', $NewData) ) > 1 ) ) {
            //on doiit crééer la donnée dans la table des options, avec le bon usrID
            $result = $db->insert( $NewDataList['tbl'],
                ($UsrID != null) ? array($TblFld => $NewData, 'ID_Utilisateur' => $UsrID) : array($TblFld => $NewData)
            );
            if ($result->insertId()) {
                $editor
                    ->Field($TblName.'.'.$NewDataList['fld'])
                    ->setValue($result->insertId())
                    ;
            }
        }
    }
};

JS form fields

                { name: 'recettes.ID_Situation', label: 'Situation',
                    type:  'selectize',
                    def: null,
                    opts: {
                        dropdownParent: null,
                        render: {
                            option_create: function(data, escape) {
                                return '<div class=\'create\'>Nouvelle situation :<strong>'
                                       + escape(data.input) + '</strong>&hellip;</div>';
                            }
                        }
                    }
                },

extend selectize default setting

...
        conf._input.selectize( $.extend( {
            valueField: 'value',
            labelField: 'label',
            searchField: 'label',
            sortField: 'label',
            allowEmptyOption: false,
            placeholder: 'Valeur requise',
            create: true,
            createFilter: function(val) {
              return ( isNaN(val) ) ? val : false;
            },
            addPrecedence: true,
            maxItems: 1,
            maxOptions: 20,
            openOnFocus: true,
            delimiter: ',',
            persist: false,
            dropdownParent: null
        }, conf.opts ) );

        conf._selectize = conf._input[0].selectize;

JS editor events (select field update)

            .on( 'postCreate postEdit', function () {
                editor.off( 'preClose' );

                        $.ajax ({
                            url: 'getdata.php',
                            method: 'GET',
                            dataType: 'json',
                            data: {
                                table:          'situations',
                                labelField:     'Libelle',
                                valueField:     'ID',
                                EmptyAllowed:   false,
                                order:          'Libelle'
                            
                                },
                                success: function ( data ) {
                                    editor.field('recettes.ID_Situation').update( data )
                                }
                            })

The last point is I need to reload data on editor open to clear the duplicated value in the select option list...

            .on( 'open', function () {  
                table.ajax.reload( null, false );

I have many select inputs and don't know how to test if update is required or not for each (in JS editor events). I think it should be more efficient to only refresh required select controls (with new data added).

I'm sur there is a better way to do add values in the source options table, adding optional datas (like userID or else needed by option filter), but don't know how.

So that's work, but not as pretty as I'd like.

Help ?
...

allan

As you note, using ajax.reload() would reload the entire DataTable. From your description what you actually want to do is make an Ajax call to the server to get just the new options and then use the field().update() method to put those options into the Selectize list.

The Editor PHP libraries don't provide a route to just get the options alone - that would need to be a new PHP script that will query the database to get the options and return them.

Allan

Lapointe

Hi @allan

I do field().update() on postEdit postCreate

.on( 'postCreate postEdit', function () {
    editor.off( 'preClose' );
 
            $.ajax ({
                url: 'getdata.php',
                method: 'GET',
                dataType: 'json',
                data: {
                    table:          'situations',
                    labelField:     'Libelle',
                    valueField:     'ID',
                    EmptyAllowed:   false,
                    order:          'Libelle'
                 
                    },
                    success: function ( data ) {
                        editor.field('recettes.ID_Situation').update( data )
                    }
                })

but when opening the selectize option list, the value is duplicated while ajax.reload() not fired...

If you want to have a look to this 'problem' I will comment the on open ajax.reload and set the reload interval to 30 seconds, so when adding selectize data you'll see 2 occurence of the added data after closing and reopening the form (Les interventions).
If selectize control stay open, the duplicate value will disapears when refrsh will be done (each 30 sec)

I do send you 2 mails about accont creation in http://dafaction.appinfo.fr

allan

Hi,

Thanks - I wondered what the logins were when they came through this morning!

What is really weird is that the success functions for the getdata.php Ajax calls don't appear to be happening at all.

That said - you actually shouldn't need them! The Data.php script should be responding with an options property in the JSON that contains all the latest options. What is really odd is that it does for GET requests (the periodic refresh), but not the POST submit from the Editor form. Can you show me the whole script?

Also - one thing I spotted:

whereString: 'situations.ID_Utilisateur = 5 OR ISNULL(situations.ID_Utilisateur)'

This is really dangerous. You've got yourself wide open to an SQL injection attack there since any SQL string could be submitted!

Allan

Lapointe

Hi @allan

I send you a wetransfer...

Thanks

Lapointe

hi @allan @rf1234 @all

I'm back for this point... Did you have a look ?

After adding a new option (postcreate event in php), then
doing ajax call on postcreate in js, and on opening editor no value is displayed, but value seem to be ok, as if I save again the correct (last created) option is yet selected at reopen.

Closing editor and reopening... everything is ok...

Perhaps ajax refresh call is misplaced or ?

sample here http://test.appinfo.fr (using user / pass as : NUJBD6DE / NUJBD6DE)

Can you help please ?

allan

I'm not seeing a postCreate event on https://test.appinfo.fr/form/donnees.php?T=recettes ? The only thing is:

            .on( 'postCreate postEdit', function () {
                editor.off( 'preClose' );
            } )

Allan

Lapointe

Hi @allan
I do not understand...
This afternoon.... and this evening I post back the version with postcreate activated and... this work better than ever....
I can't beleve it
I'm sure you are magic

Thanks
Bobby

allan

. Don't think I can take any credit on this one - probably a cache issue I guess.

Time to hop onto my broomstick...

Allan