autocomplete to link foreign key field

[bmais]

My grid has a foreign key link to the user table.  I would like to use autocomplete to search the user table for users by name but store only the user_id in the database.

Everything is working except I don't know how to store the user Id field in the grid.

( ui.item.id ) is the correct user_id value

How do I write it to the dataIndx:"user_id"?

I would also like to hear suggestions of how to not write the user_descriptor field to the database on save.

Bonus points:
.focus(function () {
        $(this).data("autocomplete").search($(this).val());
}
throws an error message, but everything works when its commented out.  Any ideas what it does and if I need it?

Parts:

dataModel:
  {title:"User ID",  width:4, dataType:"integer", dataIndx:"user_id", hidden:true},
  {title:"User", width:100, dataType:"string", dataIndx:"user_descriptor", editor: { type: autoCompleteEditor }},

Function:
var autoCompleteEditor = function (ui) {
    //debugger;
    var $cell = ui.$cell,
        rowData = ui.rowData,
        dataIndx = ui.dataIndx,
        width = ui.column.width,
        cls = ui.cls;
    var dc = $.trim(rowData[dataIndx]);
     
    var $inp = $("<input type='text' name='" + dataIndx + "' class='" + cls + " pq-ac-editor' />")
        .width(width - 6)
        .appendTo($cell)
        .val(dc);

    $inp.autocomplete({
        source: function( request, response) {
                    $.getJSON($SCRIPT_ROOT +'/fkey_search', { table:'users', srch: request.term }, function( data ) {
                        response( data )
                    });
                 },
        minLength: 2,
        select: function( event, ui ) {
            // this is where I would like to set the id value
            // alert(( ui.item.id ));
        }
       
    }).focus(function () {
        //open the autocomplete upon focus
        // Paramvir - this gives me an error message
        //$(this).data("autocomplete").search($(this).val());
    });
}

Python:
@app.route('/fkey_search')
def fkeySearch():
  tbl = request.args.get('table', 0, type=str)
  srch = request.args.get('srch', 0, type=str)
  fmt_srch = "%"+ srch +"%"

  if tbl == 'users':
    sql = "select user_id as id, name as value from users where name ilike %s"
  r = query_db(sql, (fmt_srch,))
  json_output = json.dumps(r)
  return json_output   

[paramvir]

bmais

1)

You have got reference to rowData in your autoCompleteEditor  so you can set the user_id simply by

rowData["user_id"] = ui.item.id;

2)

.focus(function () {
        $(this).data("autocomplete").search($(this).val());
}

It opens up the auto complete editor when the field receives focus. Your editor would work fine without it.

It looks like it's throwing an error because $(this).val is not set on field focus because of lazy loading used in your case. So you may comment it.

[bmais]

rowData["user_id"] = ui.item.id;
updates the cell but doesn't mark the cell as dirty.

Is there a way to mark the cell as dirty the same time I write to it?

The attachment shows the error message I get from: $(this).data("autocomplete").search($(this).val());

Thanks,
Bill

[paramvir]

Bill

1)
There is no easy way to mark the cell as dirty when making changes though rowData directly.

And You don't need to mark user_id as dirty because it's hidden and whenever any cell in a row changes the whole row gets dirty and is included in the getChanges API. Both user_id and user description are changed simultaneously in your case and user description is marked as dirty.

2)
You can disable it for the time being as it's not important for the functionality. I would share more about it when I create an example for autocomplete lazy loading.

[bmais]

Thanks