Input by Combo box with search

antoniocib

Hi, everybody,
I saw a little bit around the forum and the web and I did not understand how to call this type of input that I would like to insert because there are those who call it combobox, who datalist, who select2, I would like to enter my data in my table (Editor) through an input type with the search that takes the values from a different table from the one where you are entering the data.

rf1234

Ciao Antonio,

Ecco qualche lettura per voi:
https://editor.datatables.net/manual/php/joins#Options

Ciò di cui avete bisogno è il tipo di campo "select" menzionato. Ci sono numerosi plug in che sono circa la stessa cosa: il tipo di campo "select" integrato, "select2", il mio preferito "selectize" ecc.

Ora, guardando questo esempio, si vedono due casi di opzioni per selezionare siti e reparti. Entrambe sono a discesa = selezionare i campi nell'Editor: potete selezionare una voce e assegnarla al record che state modificando.
https://editor.datatables.net/examples/advanced/joinLinkTable.html

E un altro esempio con casi di opzioni più complesse: È possibile selezionare più itmes e assegnarli al record che si sta modificando.
https://editor.datatables.net/examples/advanced/joinArray.html

++++++++++++++++++++++++++++++++++++++++++++

Hi Antonio,

here is some reading for you:
https://editor.datatables.net/manual/php/joins#Options

What you need is the "select" field type mentioned. There are numerous plug ins that are about the same thing: the built-in "select" field type, "select2", my favorite "selectize" etc..

Now, looking at this example, you see two options instances to select sites and departments. Both of them are dropdowns = select fields in Editor: You can select one item and assign it to the record you are editing.
https://editor.datatables.net/examples/advanced/joinLinkTable.html

And another example with more complex options instances: You can select multiple itmes and assign them to the record you are editing.
https://editor.datatables.net/examples/advanced/joinArray.html

_

antoniocib

@rf1234 Hi,
But from what I saw in the examples the input of the fields does not have the integrated search...

rf1234

What is the integrated search? A select2 field is just a drop down. Its data source can be a static array or can come from a linked table. Either 1:N (foreign key) or N:M (link table).
Here is more reading:
https://datatables.net/forums/discussion/comment/155293/#Comment_155293

rf1234

ahhh ... now I guess I understand what you mean by "integrated search". If your options come from an ajax data source you'll see a shortened list of options while typing. This screenshot is a selection from over 10,000 entries while typing "Muster".

And yes, you can do that using "select", "select2", "selectize" etc.

This is the Javascript code that does the job:

var userGovDeptEditor = new $.fn.dataTable.Editor( {
    ajax: {
        url: 'actions.php?action=tblUserGovDept',
        data: function ( d ) {
            var selected = userTable.row( {selected: true} ); 
            if (selected.any()) {
                d.user = selected.data().user.id;
            }
        }
    },
    table: "#tblUserGovDept",
    fields: [ {
            label: lang === 'de' ? 'Name der Abteilung:' : 'Department Name:',
            name:  "govdept_has_user.govdept_id",
            type: "selectize", 
            opts: {
                create: false,
                maxItems: 1,
                openOnFocus: false,
                allowEmptyOption: false,
                placeholder: lang === 'de' ? 'Bitte Abteilung auswählen' :
                                             'Please select a Department'
            }
        }, {
            label: lang === 'de' ? 'Rolle:' : 'Role:',
            name:  "govdept_has_user.role",
            type:  "selectize",
            options: userRoleOptions,
            opts: {
                create: false,
                maxItems: 1,
                allowEmptyOption: false,
                placeholder: lang === 'de' ? 'Rolle für diese Abteilung auswählen' :
                                             'Select Role for this Department'
            }
        }
    ]        
} );

The first "selectize" field has an ajax data source while the second field's source is just a static array ("userRoleOptions").

antoniocib

Ehm yes i need this i can add this in my table editor?

rf1234

Sure! What field type are you using? Select (built-in), Select2 or Selectize?

This should also work with the built-in Select field type, I guess. But I use Selectize for this because I like it better. But that is up to you!
https://editor.datatables.net/reference/field/select

If you want to use Selectize or Select2 you would need a field type plugin. Scroll down on the linked page and you'll find both:
https://editor.datatables.net/plug-ins/field-type/

And of course you would need Selectize or Select2 themselves:
https://cdnjs.com/libraries/selectize.js/
https://cdnjs.com/libraries/select2

Here is some documentation on Selectize that I have been using frequently:
https://github.com/selectize/selectize.js/blob/master/docs/usage.md

For the sake of completeness, here are the two PHP field instances for both fields. The first is populated with an Editor options instance, the second is populated client side with a simple static array of options. (Since the Editor options instance doesn't know LEFT JOIN I had to code this with an implicit INNER JOIN via the WHERE clause.)

.................
if ($lang === 'de') {     
    $msg[0] = 'Feld darf nicht leer sein.';
} else {
    $msg[0] = 'Field may not be empty.';
}
............
Field::inst( 'govdept_has_user.govdept_id' )
    ->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
    ->options( Options::inst()
        ->table('govdept, gov')
        ->value('govdept.id')
        ->label( array('gov.name', 'govdept.name') )
        ->render( function ( $row ) {               
            return $row['gov.name'].' / '.$row['govdept.name']; 
        } )
        ->order( 'gov.name asc' )
        //where clause MUST be a closure function in Options!!!
        ->where( function($q) {
            $q ->where('govdept.gov_id', 'gov.id', '=', false); //join
        } )
    ),
Field::inst( 'govdept_has_user.role' )
    ->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
-----------

Good luck!

antoniocib

Let me explain better, the main table must have its fields and that's it, but when I go to insert in the main table the data the fields to be inserted must dropdown of the pre-printed data, that is present in another sql table, how can I do it?

rf1234

If I understand you correctly then this is precisely what I am doing in the code I posted.

The main table here is "govdept_has_user". This table has an Editor instance client side and another one on the server.

The options for the main table's field "govdept_has_user.govdept_id" come from two other MySQL tables called "gov" and "govdept". If you only need them from one table: then it will be even easier for you.

antoniocib

@rf1234 look this

rf1234

Please also look at the Editor examples!
This one is really good: https://editor.datatables.net/examples/advanced/joinLinkTable.html

The only disadvantage is that it doesn't have the "typeahead" effect in the Editor select fields. But for everything else you are asking the example fits perfectly I guess.

rf1234

Saw your picture. The example I mentioned does exactly what you need. Please read it ... You seem to want to assign a driver to "scrivania" - whatever that is. If that is what you want then you have the solution in front of you.

In my example I assign a department and a role to a user.

In the Editor example a site and a department are being assigned to a user. You see both options instances in the server side code. This is really the maximum I can do for you, Antonio ...

What does "Stamp the data of the table" mean?
What does "Improvise code of insert data" mean?

antoniocib

okaaay Work @rf1234

Now how implement the search in this dropdown?

        fields: [
            {
                "label": "Autista Ritiro:",
                "name": "drit",
                 "type": "select",
                 "options": [
        { "value": "drivers.d_name" },

]
},

rf1234

The built-in "Select" fields do not come with the "typeahead" effect or what you call "search". If you want that you would need to use Selectize. I posted all the information you need for that already.

I use "Select" a lot but only for small and static options lists. For server populated options I only use "Selectize" because it has the "typeahead" effect which is really nice if you have many options.

antoniocib

Can u do an example with my code @rf1234 cause i dont understand how do implement selectize..

rf1234

No, sorry. In your code you would basically only need to replace the word "select" with "selectize". If you require more selectize options: See the link I posted above and look at the code I posted! It is all at your finger tips.

You certainly need to install Selectize and the field-type as well. It's all described above and you have all the links, too.

Just download the field-type plugin JS and CSS and use the CDN service for the Selectize JS and CSS. Done!

antoniocib

Which libraries i need to add? i see on the link and are more

kthorngren

Are you seeing this on the plugins page?

License required
The Editor plug-ins are available to download for licensed users of Editor only. If you already have an Editor license, please sign-in. Otherwise, an Editor license can be purchased to be able to access the plug-ins.

If you don't have a license send Allan or Colin a PM to see if they will provide a copy for you to test.

Kevin

antoniocib

I was just writing that I solved, I used the libraries that I recommend for editor and everything works, now I was finding a way to avoid that when I select the field "selectize" it doesn't give me the dropdown of the elements, but only after the search input

i've license btw