Datatables - Editor - append a selected value to select2 input

chris.cavage

This is the final piece of a puzzle. I am using a select2 input field that searches a database for contact names as the user types. Like a type-ahead script. When the user selects the contact from the select2 dropdown menu, the id is saved to my DB as assigned_to_contact_id.

That works well.

However, when the user then clicks on the datatables row to edit the record, because there are no pre-populated options for the assigned_to_contact_id select2, it cannot populate the input field with the contact id, thus resulting in a NULL value being stored in my DB when it's updated!

Here's what I am working on:

The select2 is now called assigned_to_contact_id_search. When the user selects the contact, the id is saved to a hidden field called assigned_to_contact_id. This way the id can be retrieved on the update form so it's saved properly again if it's not changed.

I need to show the user however the contact name in the select2 input when they open the update form, so they know it was populated.

I can retrieve the name with a left join on contact_id no problem, but once I get the name, I need a way to append the option to my select2 (assigned_to_contact_id_search) and have it selected it so it looks populated.

From the PHP Editor, is there a way to do that?

Is my work around the correct approach? I hope I explained this well.

 Editor::inst( $db, 'users_todos', 'id' )
->fields(
    Field::inst( 'text' )
        ->validator( 'Validate::notEmpty' ),
    Field::inst( 'due_date' )
        ->validator( 'Validate::notEmpty' )
        ->validator( 'Validate::dateFormat', array( 'format'=>'D, M j Y' ) )
        ->getFormatter( 'Format::date_sql_to_format', 'D, M j Y' )
        ->setFormatter( 'Format::date_format_to_sql', 'D, M j Y' ),
    Field::inst( 'company_id' ),
    Field::inst( 'assigned_to_user_id' )
   //have to do the below else blank values for select2 fields were entered as 0, not null
         ->setFormatter( Format::ifEmpty( $user_id ) )
         ->getFormatter( Format::ifEmpty( $user_id ) ),
    Field::inst( 'assigned_to_contact_id' )
         ->setFormatter( Format::ifEmpty( null ) )
         ->getFormatter( Format::ifEmpty( null ) ),
    Field::inst( 'inserted_by_user_id' ),
    Field::inst( 'status_id' )
)
->where( $key = "assigned_to_user_id", $value = $user_id, $op = "=" )
->process( $_POST )
->json();

Here's my js:

 function formatResultsSelection(results) {

    if (results.id === '') {
        return '(Searching all locations)'; //placeholder added this way since using ajax per docs.
    }

    //if select person, assign to hidden field
    editor_todos.field('assigned_to_contact_id').val(results.id);
    return results.contact_name + ' ' + results.birthdate;

}

   var editor_todos = new $.fn.dataTable.Editor({
    ajax: 'php/table.user_todos.php?user_id=' + user_id,
    table: '#user_todos',
    fields: [
               {
            "label": "assigned_to_contact_id:",
            "name": "assigned_to_contact_id",
            "type": "hidden"
        },
            {
            "label": "Attach to Contact:",
            "name": "assigned_to_contact_id_search",
            "type": "select2",
            "opts": {
                ajax: {
                    url: function () {
                        return "ajax_get_json.php?what=company_autocomplete_contacts";
                    },
                    dataType: 'json',
                    delay: 250,
                    data: function (params) {
                        return {
                            query: params.term, // search term
                            page: params.page
                        };
                    },
                    processResults: function (data, params) {
                        // parse the results into the format expected by Select2
                        // since we are using custom formatting functions we do not need to
                        // alter the remote JSON data, except to indicate that infinite
                        // scrolling can be used
                        params.page = params.page || 1;

                        return {
                            results: data,
                            pagination: {
                                more: (params.page * 30) < data.total_count
                            }
                        };
                    },
                    cache: true
                },
                escapeMarkup: function (markup) {
                    return markup;
                }, // let our custom formatter work
                minimumInputLength: 1,
                templateResult: formatResults,
                templateSelection: formatResultsSelection,
                allowClear: true,
                placeholder: {
                    "id": "",
                    "text": "(Searching all locations)"
                }
            }
        }


.... });

      //when select 2 search is cleared for contact, clear the contact id hidden field
      editor_todos.field('assigned_to_contact_id_search').input().on('select2:unselecting', function (e) {

                   editor_todos.field('assigned_to_contact_id').val('');

      });

allan

What should be happening, is when the Editor edit starts, there will be an Ajax request to ajax_get_json.php?what=company_autocomplete_contacts with initialValue=true set and also a value option with the current value. You can respond to that Ajax request with the JSON for the option(s) for that given value and that will populate the Select2 list.

Its a bit of a pain needing to do that, but I haven't found a better way yet I'm afraid.

Allan

chris.cavage

Is initialValue= true a select2 method or Editor related?

Also, how do I know in code when the Editor opens in edit mode?

That should be enough to get me moving.

colin

Hi @chris.cavage ,

initialValue would be a Select2 thing - it's not used in the DataTables code.

You can use the events to get notifications of when things happen, such as preOpen. This example here from this thread, is doing that for Bubble editing - not quite the same as what you want but if you check mode === "main" and action === "edit", then that will get you what you need,

Cheers,

Colin

chris.cavage

I'm a little lost here, sorry.

I did drop the whole hidden 'assigned_to_contact_id' field idea, as it seems I should be able to solve this with that field being the select2 input. Therefore the select2 field is now called assigned_to_contact_id instead of assigned_to_contact_id_SEARCH

Added options since I started:

     "label": "Attach to Contact:",
          "name": "assigned_to_contact_id",   <----- DROPPED THE _SEARCH ENDING
          "type": "select2",
          "opts": {
                value: "",  <----- ADDED
                initialValue: true,   <----- ADDED
                 .........

What confuses me is that I'm only searching for options for select2 as the user types. So, based on what you're suggesting, I need to use ajax to get the info I need when the editor opens in edit mode and assign it to the select2 field, correct?

I also made a way to get the JSON data I need that will be needed for the select2. The first part of this problem is that editor_todos.field('assigned_to_contact_id').val() doesn't exist, so there's no $id to search by. It isn't populated when the editor opens as all the other fields are.

       editor_todos.on('open', function (e, mode, action) {

            $('div.DTE_Footer').css('text-indent', -1);

            if (mode === 'main') {

                   if (action === 'edit') {

                            $id = editor_todos.field('assigned_to_contact_id').val();

                            $.ajax({
                                    url: 'ajax_quick_get_value.php',
                                    type: "GET",
                                    dataType: 'json',
                                    data: {
                                            table: 'contacts',
                                            field: 'id',
                                            field_equals: $id
                                      },
                                      success: function (response) {

                                            RESPONSE.FIRST_NAME = SELECT2 LABEL
                                            RESPONSE.ID = SELECT2 VALUE

                                      }
                            });

                   }

              }

       });

I feel like I went off the tracks somewhere here....

chris.cavage

ok! I'm so close now. It hit me like a ton of bricks that you meant, Allan, the value will be sent to my php ajax page as a URL parameter on load by default with a select2 field type. So, in my php ajax page I catch if intialValue=true to return the data I need.

Here's my script:

             {
            "label": "Attach to Contact:",
            "name": "assigned_to_contact_id",
            "type": "select2",
            "opts": {
                value: "",
                initialValue: true,
                ajax: {
                    url: "ajax_get_json.php?what=company_autocomplete_contacts",
                    dataType: 'json',
                    delay: 250,
                    data: function (params) {
                        return {
                            query: params.term, // search term
                            page: params.page
                        };
                    },
                    processResults: function (data, params) {
                        // parse the results into the format expected by Select2
                        // since we are using custom formatting functions we do not need to
                        // alter the remote JSON data, except to indicate that infinite
                        // scrolling can be used
                        params.page = params.page || 1;

                        return {
                            results: data,
                            pagination: {
                                more: (params.page * 30) < data.total_count
                            }
                        };
                    },
                    cache: true
                },
                escapeMarkup: function (markup) {
                    return markup;
                }, // let our custom formatter work
                minimumInputLength: 1,
                templateResult: formatResults_editor,
                templateSelection: formatResultsSelection_editor,
                allowClear: true,
                placeholder: "Search..."
            }
        },

Here's my formatResults script:

      function formatResultsSelection_editor(results) {

              return results.contact_name + ' ' + results.birthdate;

       }

So the tyepahead works now. The value on edit will get the right JSON response from company_autocomplete_contacts.php BUT the select2 is still blank. It's not populating. I am returning the same JSON response when the value id is sent to the ajax page on edit as I am when searching via the typeahead feature.

Shouldn't it just populate then if the response is the same?

If it helps, the placeholder in select2 is saying 'undefined undefined'

chris.cavage

Here's the sample data coming back from the server at:
ajax_get_json.php?what=company_autocomplete_contacts&initialValue=true&value=%224258%22

   {"id":"1","text":"load this text!","location":"location","contact_name":"name","birthdate":"bday","label":"label","value":"value","location_id":"location_id","company_id":"company_id","no_contact_pic_requested":"pic"}

So shouldn't the select2 populate then with the label and id field?

allan

I think you just need to put that object in an array - e.g.:

[{
    "id": "1",
    "text": "load this text!",
    "location": "location",
    "contact_name": "name",
    "birthdate": "bday",
    "label": "label",
    "value": "value",
    "location_id": "location_id",
    "company_id": "company_id",
    "no_contact_pic_requested": "pic"
}]

Allan

chris.cavage

Good suggestion, but still nothing populates.

chris.cavage

I got it! I had to get rid of the templateSelection: formatResultsSelection_editor part of it. Once I did, it populates. Thanks.

I'm gonna post one more question I think, and I'll be done with this project. This is great though.

allan

Ah! Nice one. Thanks for posting back with that . I not sure I would have got that...!

Allan

chris.cavage

Something tells me you would have, Allan! Thanks again.

carlop

Hi, I have a similar problem.
My select2 ajax call returns different values based on a previous radio selection.
It works great when I create a new row, but when I reload the page and I edit any row, on modal opening the ajax call contains initialValue=true and value with the saved value, but not the type parameter.
The result is an empty select2.

          {
            "label": "Type:",
            "name": "type",
            "type": "radio",
            "options": [
              "Product",
              "Category",
              "Brand"
            ]
          },
          {
            "label": "Code:",
            "name": "code",
            "type": "select2",
            "opts": {
              "ajax": {
                  url: "ajax.php",
                  dataType: 'json',
                  delay: 250,
                  data: function (params) {
                      var pars= {
                        value: params.term,
                        type: editor.get('type')
                      }
                      return pars;
                  },
                  processResults: function (data, params) {
                      return {
                        results: data
                      };
                  }
              },
              "theme": "bootstrap",
              "multiple":false
            }
          }

I guess I should set a different ajax call for editing, but I don't know how.

allan

You are correct - the Select2 plug-in doesn't currently take into account the ajax.data function for the Select2 options I'm afraid. That needs a change to the plug-in. I'll look into that.

Allan