select field options remote data

rboudwin

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"]
              }

kthorngren

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

Kevin

rboudwin

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>

kthorngren

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

rboudwin

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

kthorngren

On line 57 of the above code try this:

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

Changing json.options to json.data.

Kevin

rboudwin

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 );
    } );

rboudwin

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.

rboudwin

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

rboudwin

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.

kthorngren

Good find. I didn't notice the case.

Kevin

rboudwin

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.

rboudwin

See the state column to the right.

rboudwin

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