Talha asked 10 years ago
AoA,
I am trying to utilize the Autocomplete feature, it works fine when single table is involved (as given in the demo) but when it comes to multiple tables, that does not work.
Scenario:
———
Table 1 (Customers): CustomerID, CustomerName
Table 2 (Invoices): InvoiceID, CustomerID, ……
What I am trying to do is to apply auto complete on customer name while registering invoices.
The code is like this:
————————————
$col = array();
$col["title"] = "Customer";
$col["name"] = "CustomerID";
$col["editable"] = true;
$col["width"] = "80";
$col["formatter"] = "autocomplete"; // autocomplete
$col["formatoptions"] = array( "sql"=>"SELECT CustomerName as k, CustomerID as v from Customers", "search_on"=>"CustomerName", "update_field" => "CustomerID");
$cols[] = $col;
$g->select_command = "SELECT * FROM Invoices";
————————————
The above code fetches CustomerID as I type the Customer name, and saves the ID (but I need to display Name). I guess I am doing it right till here and I do not have idea how do display Customer Name in that column instead of ID ?
Thanks,
Talha
Abu Ghufran answered 10 years ago
You need to add 2 columns. One is hidden customerid that will be updated by autocomplete and submitted.
$col = array();
$col["title"] = "CustomerId";
$col["name"] = "CustomerID";
$col["editable"] = true;
$col["hidden"] = true;
$cols[] = $col;
Second column will be just for autocomplete function, that will display customername on grid, perform autocomplete and set customerid in above field.
$col = array();
$col["title"] = "Customer";
$col["name"] = "CustomerName";
$col["editable"] = true;
$col["width"] = "80";
$col["formatter"] = "autocomplete"; // autocomplete
$col["formatoptions"] = array( "sql"=>"SELECT CustomerID as k, CustomerName as v from Customers", "search_on"=>"CustomerName", "update_field" => "CustomerID");
$cols[] = $col;
To fill second column in grid display, you need to select customername in sql query
$g->select_command = "SELECT Invoices.*, CustomerName FROM Invoices INNER JOIN Customers ON Customers.CustomerID = Invoices.CustomerID" ;
