How to store multiple values in one database cell?

janosi

Dear Community

I have recently started using Datatables Editor for an upcoming project and am strugling with a few issues when using the editor.

The project is hosted on Typo3 CMS, which has a single usergroups field for all users. This field containds the ID of the usergroup(s) for that user. It is possible that only a single usergroup is selected for a user, but multiple can be present as well. The usergroups are separated by ",".

I tried using the following

Field::inst( 'usergroup' )
            ->setFormatter( 'Format::implode', "," )
            ->getFormatter( 'Format::explode', "," )

However when I select multiple groups and submit, I get a JavaScript error even before the data is sent ("TypeError: e is null"). I assume that the problem is that the Editor does not know that my select field can have multiple values.

I have the usergroups field set for "select" and "opts" "multiple" is enabled.

These settings work fine with actual multiple-type fields (in case of linked joins), but fails in this supposedly more simple case. Could you plesae give me an example on how to solve this?

allan

I assume that the problem is that the Editor does not know that my select field can have multiple values.

The select field type uses jQuery's $().val() method to read the value, and that does work okay with a multiple value select field type - it returns an array of the selected values.

So there is something else going on. Are you able to give me a link to the page so I can debug the issue?

Thanks,
Allan

janosi

Dear allan

I do not think I can provide you with a link, but I can paste the whole generated JS code.

So the problem is related to "fe_users.usergroup".

var actionURL = "fe_users.php";
    var recordID = "";
    var programID = "";
    var displayLimit = "10";
    var dtServerSide = true;
    var dtSetup = [
        {data: 'fe_users.uid'}, 
        {data: 'fe_users.title', render: function (val, type, row) {
                    var lab = '';
                    switch(val){
                         case '': 
                        lab = ''; 
                    break;  case 'Dr.': 
                        lab = 'Dr.'; 
                    break;  case 'Hab.': 
                        lab = 'Hab.'; 
                    break;  case 'Phd.': 
                        lab = 'Phd.'; 
                    break;  case 'Prof.': 
                        lab = 'Prof.'; 
                    break; 
                    }                                
                    return lab;
                }}, 
        {data: 'fe_users.last_name'}, 
        {data: 'fe_users.first_name'}, 
        {data: 'fe_users.usergroup', render: function (val, type, row) {
                    var lab = '';
                    switch(val){
                         case '2': 
                        lab = 'Menedzser'; 
                    break;  case '3': 
                        lab = 'Asszisztens'; 
                    break;  case '4': 
                        lab = 'Partner'; 
                    break;  
                    }                                
                    return lab;
                }}, 
        {data: 'crm_egyeb_teruletek', render: '[, ].nev'}];
var dtEditorSetup = [
    {'name':'fe_users.uid', 'type':'hidden', 'label':'ID'}, 
    {'name':'fe_users.title', 'type':'select', 'label':'Titulus', 'options':[
        {"value":"","label":""},
        {"value":"Dr.","label":"Dr."},
        {"value":"Hab.","label":"Hab."},
        {"value":"Phd.","label":"Phd."},
        {"value":"Prof.","label":"Prof."}]},
    {'name':'fe_users.last_name', 'label':'Vezetéknév'}, 
    {'name':'fe_users.first_name', 'label':'Keresztnév'}, 
    {'name':'fe_users.usergroup', 'type':'select', 'label':'Beosztás(ok)', 'options':[
        {"value":2,"label":"Menedzser"},
        {"value":3,"label":"Asszisztens"},
        {"value":4,"label":"Partner"}]
        , 'attr':{"multiple":"true"}}, 
    {'name':'crm_egyeb_teruletek[].terulet_id', 'type':'select', 'label':'Terület(ek)', 'attr':{"multiple":"true"}}];

    var dtFilterSetup = [{'filter_type':'text', 'text_data_delimiter': ',', 'filter_delay': 500, 'column_number': 1}, {'filter_type':'text', 'text_data_delimiter': ',', 'filter_delay': 500, 'column_number': 2}, {'filter_type':'text', 'text_data_delimiter': ',', 'filter_delay': 500, 'column_number': 3}, {'filter_type':'text', 'text_data_delimiter': ',', 'filter_delay': 500, 'column_number': 4}, {'filter_type':'text', 'text_data_delimiter': ',', 'filter_delay': 500, 'column_number': 5}];

dTable_editor_1 = new $.fn.dataTable.Editor( {
        "ajax": { 
            "url": actionURL,  
            "type": "POST",
            "data": function ( d ) {
                d.recordID = recordID;
                d.programID = programID;
            }
        },
        "table": elementID,
        "fields": dtEditorSetup,        
        "i18n": {
           "create": {        
                "button": "Új",        
                "title":  "Új bejegyzés létrehozása",        
                "submit": "Létrehoz"    
            },     
            "edit": {        
                "button": "Módosítás",       
                "title":  "Bejegyzés módosítása",        
                "submit": "Módosítás"    
            },     
            "remove": {        
                "button": "Törlés",        
                "title":  "Törlés",        
                "submit": "Törlés",        
                "confirm": {            
                    "_": "Biztos le akar törölni %d sort?", 
                    "1": "Biztos le akarja törölni a sort?"        
                }    
            },     
            "error": {        
                "system": "Rendszerhiba történt."    
            }
        }
    } );

dTable_1 = $(elementID).DataTable({
        dom: "Tlrtip",
        ajax: {
            url: actionURL,
            type: "POST",
            "data": function ( d ) {
                d.recordID = recordID;
                d.programID = programID;
            }
        },
        iDisplayLength: displayLimit,
        lengthMenu: [[5, 10, 25, 50, -1], [5, 10, 25, 50, "Összes"]],
        serverSide: dtServerSide,
        columns: dtSetup,
        order: [ 1, 'asc' ],
        tableTools: {
            sRowSelect: "os",           
            /* sRowSelector: 'td:first-child', */
            sSwfPath: "fileadmin/template/metro_lab_template/assets/DataTables/DataTables-1.10.7/extensions/TableTools/swf/copy_csv_xls_pdf.swf",
            aButtons: [



                 {  sExtends: "editor_create", editor: dTable_editor_1  } ,

                 {  sExtends: "editor_edit", editor: dTable_editor_1  } ,

                 {  sExtends: "editor_remove", editor: dTable_editor_1  } ,

    

                {
                    sExtends: "collection",
                    sButtonText: "Exportálás",
                    sButtonClass: "save-collection",
                    aButtons: [ 'copy', 'csv', 'xls', 'pdf' ]
                }
            ]
        }, 
        language: {                
            url: "fileadmin/template/metro_lab_template/assets/DataTables/DataTables-1.10.7/media/js/dataTables.hungarian.lang"
        },
        initComplete: function(settings, json) {
            addSelectHandler_1(); 
        }   
    });
    
    // Filter
    yadcf.init(dTable_1, dtFilterSetup, 'footer');
});

allan

Could you also give me the backtrace for the error ( TypeError: e is null ) so I can see where it is occurring in the code and what is calling the function that is causing the error.

Thanks,
Allan

janosi

Dear Allan

Sorry for my late reply, I have been very busy as of late and this issue was set on halt in favour for more important bits.

EDIT:
I removed some parts of this post, becasue it became invalid.

I was able to make fast progress with this. It turns out that the usergroups was not the source of the issue afterall (even though it only ever appeared when I selected more then one usergroup).

The actual cause appears to be with the "crm_egyeb_teruletek" field.
Right now the datatable works good if "crm_egyeb_teruletek" is set to checkbox, but fails when I try to set it to a multiple select.
This field is link-joined with another table (based on one of the examples here). In the examples it works as checbox too, but in my case there are too many selectable options, so I need a select box.

On a different topic:
is it possible to filter (add where clause) a table inside leftjoin statement in the editor?

allan

The actual cause appears to be with the "crm_egyeb_teruletek" field.

So are you still getting an error on the page? If so, can you give me a backtrace?

is it possible to filter (add where clause) a table inside leftjoin statement in the editor?

The where() condition can be applied to the joined fields as well, yes. It's just a case of adding the column name in the condition.

Allan