NotInList event...

NotInList event...

LapointeLapointe Posts: 103Questions: 22Answers: 1

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

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 1,768Questions: 51Answers: 257
    Answer ✓

    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.

  • LapointeLapointe Posts: 103Questions: 22Answers: 1

    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.

  • rf1234rf1234 Posts: 1,768Questions: 51Answers: 257

    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 ...

  • LapointeLapointe Posts: 103Questions: 22Answers: 1

    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 ?

  • rf1234rf1234 Posts: 1,768Questions: 51Answers: 257
    edited October 16

    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>';
                    }
                  }
                },
        },  {
    
  • LapointeLapointe Posts: 103Questions: 22Answers: 1

    @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)

  • rf1234rf1234 Posts: 1,768Questions: 51Answers: 257

    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 ...

  • LapointeLapointe Posts: 103Questions: 22Answers: 1

    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 ?

  • rf1234rf1234 Posts: 1,768Questions: 51Answers: 257
    edited October 18

    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

Sign In or Register to comment.