Have you ever had to build a data grid which includes an auto complete column in the meaning that it would suggest items when you update, insert a record? Moreover, the suggested items are not what to be saved to the database but their codes are.

Today I'm going to present to you a user case like that which makes use of our KoolGrid and GridAutoCompleteColumn. Supposed we have two database tables, Orders and Customers, which are joined by the field CustomerId. We want to build a grid for viewing/editing the Orders table. Certainly we could view/edit the field CustomerId of each record directly but it's not very convenient or informative because we remember names better than id codes. GridAutoCompleteColumn comes to the rescue. It would allow us to view/edit each record with customers' names instead of theirs id:

Let's analyse an example's code to see how to do it.

1. First, we need to build a data source's select, update, insert commands. We need customer's names for viewing and customer's number for updating/inserting a record: 
$ds->SelectCommand = "select orderNumber, c.customerName from orders o left join customers c on o.customerNumber = c.customerNumber";
$ds->UpdateCommand = "update orders set customerNumber='@customerNumber' where orderNumber=@orderNumber";
$ds->InsertCommand = "insert into orders (customerNumber) values ('@customerNumber');";

2. Second, we construct the auto complete column: 
$column = new GridAutoCompleteColumn();
$column->DataField = "customerName"; //An auto complete column's data field is used for displaying
$column->HiddenDataField = 'customerNumber'; //while its hidden data field is used for saving.
$column->serviceFunction = 'getSuggestedCustomers'; //The service function is used to retrieved the auto complete items
function getSuggestedCustomers($text) {
  $items = array();
  $result = mysql_query("select customerNumber,customerName from customers where customerName like '$text%'");
  while ($row = mysql_fetch_assoc($result)) {
    $item = array("text" => $row["customerName"], "number" => $row["customerNumber"]);
    array_push($items, $item);
  }
  return $items;
}
$koolajax->enableFunction('getSuggestedCustomers');
$column->itemTemplate = "{text}"; //The item template allows us to customize displaying information.
$column->saveTemplate = "{number}"; //The save template allows us to customize saving information.
$column->KoolAutoCompleteFolder = $KoolControlsFolder . "/KoolAutoComplete"; //and this points to the KoolAutoComplete control folder

And that's it. We have built a nice solution for this user case. You could try it live at this online demo:
http://demo.koolphp.net/Examples/KoolGrid/Columns/GridAutoCompleteColumn/index.php