Datagrid combobox lookup [solved]

[andyj]

Hi.
New to the forum so this is probably easy to answer...
This is based on this tutorial:
http://www.jeasyui.com/tutorial/datagrid/datagrid12.php

I have created a datagrid.
One of the fields is an integer-type field, with its editor set to combobox.
My combobox is loaded with data from a mysql db, not local array data.
The integer is based on a lookup table other than the table the datagrid is based on.
1) How do I populate the combobox with remote data?
2) How do I display not the integer but a field from the remote table (the lookup table)

Code:

$('#tt').datagrid({
title:'Estimate Line Items',
iconCls:'icon-edit',
width:1200,
height:650,
singleSelect:true,
idField:'estimatelinedetail_id',
url:'data_select.php',
columns:[[
{field:'estimatelinedetail_id',title:'ID',width:60},
{field:'item_id',title:'Item Code',width:300,
editor:{
type:'combobox',
options:{
mode:'remote',
url:'item_id_select.php',
valueField:'item_id',
textField:'ItemCode',
required:true
	}
}
},
{field:'Description',title:'Description',width:300,align:'left',editor:'textarea'},
{field:'Quantity',title:'Quantity',width:100,align:'right',editor:'numberbox'},
{field:'PurchaseRate',title:'Purchase Rate',width:180,align:'right',editor:'numberbox'},
{field:'NetTotal',title:'Net Total',width:100,align:'right',editor:'numberbox'},
{field:'action',title:'Action',width:90,align:'center',
formatter:function(value,row,index){
if (row.editing){
var s = '<a href="#" onclick="saverow(this)">Save</a> ';
var c = '<a href="#" onclick="cancelrow(this)">Cancel</a>';
return s+c;
} else {
var e = '<a href="#" onclick="editrow(this)">Edit</a> ';
var d = '<a href="#" onclick="deleterow(this)">Delete</a>';
return e+d;
}
}
}
]],

data_select.php extracts data into json for my datagrid
item_id_select.php extracts data into json for my combobox lookup

The above displays item_id from the datagrid table. I want to get the 'ItemCode' field from my look up table.
The combobox doesn't load any data.

Can anyone help? Thanks

[stworthy]

Try following steps to display itemCode on item_id field.
1. The datagrid's data source must contains item_id and itemCode fields. This can be easily done by joining the two corresponding table in PHP.
2. Define the formatter function on item_id field for datagrid.

Code:

{field:'item_id',formatter:function(value,row){return row.itemCode},...}

[andyj]

Aha.
Question No. 2 answered  
So the trick is to do all the foreign key joins and pull all the data into a single data source and reference that.
In terms of datagrid displaying the correct data, works like a dream. Thank you!

Question No. 1 still unresolved..
My combobox doesn't work in Edit Mode. No items at all, completely blank...

Code:

{field:'item_id',title:'Item Code',width:300,
					formatter:function(value,row){  
                            return row.ItemCode;  
                        },
					 	editor:{
							type:'combobox',
							options:{
								mode:'remote',
								url:'item_id_select.php',
								valueField:'item_id',
								textField:'ItemCode',
								required:true
									}
								}
					},

item_id_select.php:

Code:

<?php
	$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 20;
	$offset = ($page-1)*$rows;
	$result = array();

	include 'conn.php';

	$rs = mysql_query("select item_id,ItemCode from item limit $offset,$rows");
	
	$items = array();
	while($row = mysql_fetch_object($rs)){
		array_push($items, $row);
	}
	$result["rows"] = $items;

	echo json_encode($result);
?>

[andyj]

I managed to resolve Question 1 and get the combobox working.

I removed from the editor options:
mode: 'remote'

I also removed the header from the json file

Code:

					{field:'item_id',title:'Item Code',width:300,
					formatter:function(value,row){  
                            return row.ItemCode;  
                        },
					 	editor:{
							type:'combobox',
							options:{
//								mode:'remote',  //REMOVED
								url:'item_id_select.php',
								valueField:'item_id',
								textField:'ItemCode',
								required:true
									}
								}
					},

PHP file to create combobox datasource:

Code:

	$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 20;
	$offset = ($page-1)*$rows;
	$result = array();

	include 'conn.php';
	
	$rs = mysql_query("select item_id,ItemCode from item limit $offset,$rows");
	
	$items = array();
	while($row = mysql_fetch_object($rs)){
		array_push($items, $row);
	}

	echo json_encode($items);

Resulting json:

Code:

[{"item_id":"1","ItemCode":"English-French"},{"item_id":"2","ItemCode":"English-German"},{"item_id":"23","ItemCode":"German-English"}]

Previous incorrect json:

Code:

{"rows":[{"item_id":"1","ItemCode":"English-French"},{"item_id":"2","ItemCode":"English-German"},{"item_id":"23","ItemCode":"German-English"}]

Hope this is of some help to someone else.

[fornax]

Hi andyj - it really is a help...

I ran into the same problem.
What is still not working for me is that the given value from your "data_select.php" is not active as selected dropdown option. 

also: How did you manage saving and update into the database.
Can I contact you for help here?