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 ASPRunnerPro/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.
Well, let's start.
Steps:
1. Open ASPRunnerPro/PHPRunner and create new project.
2. Connect to MySQL server and select Northwind database.
3. On Datasource tables tab select Products table.
4. Proceed to Edit SQL query tab and replace existing sql query with the following:
Sample SQL Query
5. Proceed to Choose fields tab.
6. For CategoryId field select following checklists: Add, Edit.
7. Correspondingly for CategoryName field: List, Search, Adv Search, View, Printer, Export.
8. Proceed to Visual Editor tab and for CategoryId field in "Edit as" settings dialog select Lookup wizard.
9. Select needed options as it shown on the following figure.
10. Build your ASPRunnerPro/PHPRunner project and view generated files in Windows Explorer.
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:
- ASPRunnerPro
- PHPRunner