Search and sorting of lookup values

Home | Site map

Search and sorting of lookup values

You can put values from one table into another with a Lookup list. For example, if you had a shop, you might have one table for your products and another table for the categories that contain them. With a Lookup list of category names from the Categories table inserted into a field in the Products table, you could simply click on the correct Category from a menu instead of typing it from the keyboard.

Quite often you have to search and sort tables by lookup fields. This document will show you step-by-step, how to create lookup fields and what changes you need to make in your ASPRunner/PHPRunner project to ensure correct sorting and searching.

We will show you all steps you need to make on the example of the Northwind sample database which includes Products and Categories tables. If you do not have this database on your computer, you can download sql script that will create Northwind database on your MySQL server.

Well, let's start:

	Open ASPRunner/PHPRunner and create new project
	Connect to MySQL server and select Northwind database
	On Datasource tables tab select Products table

Proceed to Edit SQL query tab and replace existing sql query with the following

Sample SQL Query

SELECT `ProductID`,
`ProductName`,
`SupplierID`,
`categories`.`CategoryName`,
`products`.`CategoryID`,
`QuantityPerUnit`,
`UnitPrice`,
`UnitsInStock`,
`UnitsOnOrder`,
`ReorderLevel`,
`Discontinued`
FROM `products` INNER JOIN `categories` ON `categories`.`categoryID`=`products`.`categoryID`

	Proceed to Choose fields tab
	For CategoryId field select following checklists: Add, Edit
	Correspondingly for CategoryName field: List, Search, Adv Search, View, Printer, Export

	Proceed to Visual Editor tab and for CategoryId field in "Edit as" settings dialog select Lookup wizard
	Select needed options as it shown on the following figure

	Build your ASPRunner/PHPRunner project and view generated files in Windows Explorer
	Open with any text editor commonfunctions.php file which located in Include folder
	Go to Function GetFullFieldName and replace its code with the following

PHPRunner Example

function GetFullFieldName($field,$table="")
{
global $strTableName;
if (!$table)
$table=$strTableName;
$field = RemoveFieldWrappers($field);
//products
if ("products"==$table && $field=="CategoryID") return "`products`.`CategoryID`";
if ("products"==$table && $field=="Discontinued") return "`products`.`Discontinued`";
if ("products"==$table && $field=="ProductID") return "`products`.`ProductID`";
if ("products"==$table && $field=="ProductName") return "`products`.`ProductName`";
if ("products"==$table && $field=="QuantityPerUnit") return "`products`.`QuantityPerUnit`";
if ("products"==$table && $field=="ReorderLevel") return "`products`.`ReorderLevel`";
if ("products"==$table && $field=="SupplierID") return "`products`.`SupplierID`";
if ("products"==$table && $field=="UnitPrice") return "`products`.`UnitPrice`";
if ("products"==$table && $field=="UnitsInStock") return "`products`.`UnitsInStock`";
if ("products"==$table && $field=="UnitsOnOrder") return "`products`.`UnitsOnOrder`";
if ("products"==$table && $field=="CategoryName") return "`categories`.`CategoryName`";
return "";
}

ASPRunner Example

function GetFullFieldName(strTableN,strField)
field = RemoveWrappers(strField)
strTable=RemoveWrappers(strTableN)
'Products
if "Products"=strTable and "ProductID"=field
then GetFullFieldName="[Products].[ProductID]"
exit function
end if
if "Products"=strTable and "ProductName"=field
then GetFullFieldName="[Products].[ProductName]"
exit function
end if
if "Products"=strTable and "SupplierID"=field
then GetFullFieldName="[Products].[SupplierID]"
exit function
end if
if "Products"=strTable and "CategoryID"=field
then GetFullFieldName="[Products].[CategoryID]"
exit function
end if
if "Products"=strTable and "QuantityPerUnit"=field
then GetFullFieldName="[Products].[QuantityPerUnit]"
exit function
end if
if "Products"=strTable and "UnitPrice"=field
then GetFullFieldName="[Products].[UnitPrice]"
exit function
end if
if "Products"=strTable and "UnitsInStock"=field
then GetFullFieldName="[Products].[UnitsInStock]"
exit function
end if
if "Products"=strTable and "UnitsOnOrder"=field
then GetFullFieldName="[Products].[UnitsOnOrder]"
exit function
end if
if "Products"=strTable and "ReorderLevel"=field
then GetFullFieldName=[Products].[ReorderLevel]"
exit function
if "Products"=strTable and "Discontinued"=field
then GetFullFieldName="[Products].[Discontinued]"
exit function
end if
if "Products"=strTable and "CategoryName"=field
then GetFullFieldName="[Categories].[CategoryName]"
exit function
end if
end function

Now searching and sorting records in the Products table by the CategoryName field will work correctly, and to choose category on the Edit page you just have to select needed value from the Lookup list.

Applies to:
ASPRunner Pro
PHPRunner

Home | Products | Downloads | Support | Contacts

© 1999 - 2006 XLineSoft. All rights reserved.

All comments send to webmaster@xlinesoft.com