AutoComplete : How to make it work in case of linked/related tables

QuestionsAutoComplete : How to make it work in case of linked/related tables
Talha asked 4 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

2 Answers
Abu Ghufran answered 4 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" ;

Talha answered 4 years ago

Thanks a ton, it worked 🙂

Your Answer

0 + 19 =

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

We are sorry that this post was not useful for you!

Let us improve this post!