select field options remote data

select field options remote data

rboudwinrboudwin Posts: 28Questions: 6Answers: 1

Is there a current example of how to populate the select options from a returned query dataset?

fields: [ 
             {
                 label: "Home State:",
                 name: "HOMESTATE",
                 type: "select",
                 options: ["NC", "SC", "VA"]
              }

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 14,099Questions: 25Answers: 3,325

    See if the first part of this thread helps:
    https://datatables.net/forums/discussion/comment/137978/#Comment_137978

    Kevin

  • rboudwinrboudwin Posts: 28Questions: 6Answers: 1

    Thank you for the link. The data is being returned in JSON format, but it isn't populating the options in the select field, HOMESTATE, in my sample code below. I'm not clear if I am supposed to be using the assignments in assignments.HOMESTATE. I'm new to javascript.

    <script>
    var editor; // use a global for the submit and return data rendering in the examples
     
    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor( {
            ajax: {
                    url:    "../cfc/dataTable.cfc?method=updateData",
                    "contentType": "application/json",
                    "type": "POST",
                    "data": function ( d ) {
                            return JSON.stringify( d );
                            }
                },
            table: "#example",
            idSrc: "TESTID",
            fields: [ {
                     label: "First Name:",
                     name: "FIRSTNAME"
                 }, {
                     label: "Last Name:",
                     name: "LASTNAME"
                 }, {
                     type: "hidden",
                     name: "TESTID"
                 }, {  
                    label: "Contact Type:",
                    name: "CONTACTTYPE",
                    type: "select2",
                    options: ["Editor", "Author", "Contributor"]
                    /*opts: {placeholder: "Select Contact Type"},
                    attr:  {
                        multiple: "multiple"
                    }*/
                 },
                 {
                     label: "Gender:",
                     name: "GENDER",
                     type: "radio",
                     options: ["Male","Female"]
                 },
                 {
                     label: "Birthday:",
                     name: "BIRTHDAY",
                     type: "date",
                     dateFormat: "yy-mm-dd"
                 },
                 {
                     label: "Home State:",
                     name: "HOMESTATE",
                     type: "select"
                }
             ]
         } );
    
    
        $.getJSON( '../cfc/dataTable.cfc?method=statesdata', function ( json ) {
        editor.field('assignments.HOMESTATE').update( json.options );
        truth=true;
        } );
    
        // Inline Editing
        $('#example').on( 'click', 'tbody td', function () {
                editor.inline( this );
        });
    
        // Submit Arrow
        $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
                editor.inline( this, {
                    buttons: { label: '&gt;', fn: function () { this.submit(); } }
                } );
        } );
    
    
        // Output Table and Buttons
        $('#example').DataTable( {
        /*ordering: false,*/
        dom: "Bfrtip",
        ajax: "../cfc/dataTable.cfc?method=updateData",
        columns: [
            { data: "FIRSTNAME" },
            { data: "LASTNAME" },
            { data: "CONTACTTYPE" },
            { data: "GENDER" },
            { data: "BIRTHDAY" },
            { data: "HOMESTATE" }
        ],
        keys: true,
        select: true,
        paging: true,
        searching: true,
        responsive: true,
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor },
            'print',
            'excel',
            'pdf',
            'colvis'
        ]
        });
    
    } );
    </script>
    

  • kthorngrenkthorngren Posts: 14,099Questions: 25Answers: 3,325

    I think the problem is that the data is being returned in the data object. This is expect when for the ajax request/response when initializing Datatables but I don't believe it is expected for updating the select options. In the thread I linked to the return json that person is using looks like this:

    [{label: "", value: ""}, {label: "Floaty Floater", value: "Floaty Floater"}, {label: "Purcy Purchasing", value: "Purcy Purchasing"}, {label: "Test User", value: "Test User"}, {label: "Ulf User", value: "Ulf User"}]

    Kevin

  • rboudwinrboudwin Posts: 28Questions: 6Answers: 1

    I saw that, too. I haven't been able to figure out how to change it. I'll keep working on it. Thank you!

  • kthorngrenkthorngren Posts: 14,099Questions: 25Answers: 3,325

    On line 57 of the above code try this:

    editor.field('assignments.HOMESTATE').update( json.data );
    

    Changing json.options to json.data.

    Kevin

  • rboudwinrboudwin Posts: 28Questions: 6Answers: 1
    edited December 2018

    We are getting much closer. It is now showing a dropdown list using the code below, but the list of options are blank. I had added assignments to assignments.HOMESTATE because I saw it in an example. I don't know if it is supposed to be in there. I tried it, and it didn't work.

    $.getJSON( '../cfc/dataTable.cfc?method=statesdata', function ( json ) {
        editor.field('HOMESTATE').update( json.data );
        } );
    
  • rboudwinrboudwin Posts: 28Questions: 6Answers: 1

    I was able to get the returned JSON data without the DATA layer in it, but the dropdown doesn't even work now. I believe it might be a formatting issue, but I can't find what the format should be. Not much current documentation on select remote options.

  • rboudwinrboudwin Posts: 28Questions: 6Answers: 1

    Could this be a case sensitivity issue, since JSON returns all uppercase?

  • rboudwinrboudwin Posts: 28Questions: 6Answers: 1

    NOTE:
    ColdFusion internally represents structure key names using all-uppercase characters, and, therefore, serializes the key names to all-uppercase JSON representations. Any JavaScript that handles JSON representations of ColdFusion structures must use all-uppercase structure key names, such as CITY or STATE. You also use the all-uppercase names COLUMNS and DATA as the keys for the two arrays that represent ColdFusion queries in JSON format.

    I was able to work around this by adding the following lines to my Application.cfc:
    <cfset THIS.serialization.preserveCaseForStructKey = true />
    <cfset THIS.serialization.preserveCaseForQueryColumn= true />

    The select options being populated by an Ajax call now work properly.

  • kthorngrenkthorngren Posts: 14,099Questions: 25Answers: 3,325
    Answer ✓

    Good find. I didn't notice the case.

    Kevin

  • rboudwinrboudwin Posts: 28Questions: 6Answers: 1

    I wasn't able to find documentation that the returned format required lowercase "label" and "value."

    I hope this helps someone else.

    Now it's doing something quirky inline. It shows the changed ID value (other options still show label) inline only. The modal correctly shows the label.

  • rboudwinrboudwin Posts: 28Questions: 6Answers: 1

    See the state column to the right.

  • rboudwinrboudwin Posts: 28Questions: 6Answers: 1
    edited December 2018

    Interesting, if you click on the inline dropdown list, it shows the label rather than the ID (incorrectly displayed in the table view)

    I figured it out. It's the way I was saving it. I'll continue to play around with this.

    Thank you for all of your help!!

This discussion has been closed.