Create a Multiple-Column Lookup
A lookup list is a field whose value is retrieved from another table. This article explains how to create a lookup list with multiple columns (whereas a choice element is effectively just one column).
A lookup field gets data from a query, the example below shows the steps and the code for a customer lookup list with three columns, customer name, address, and phone.
Step 1: Open the form
Step 2: Go in design view
Step 3: Click “AutoScript”
Step 4: You must write your query in the procedure Form_Start and you must end your code with the procedure Select_xxx_Params. See the example below.
start_sql "CUSTOMER" 1 "KOSMOS"
CUSTOMERS.PHONE, CUSTOMERS.CUSTOMERS AS CUSTOMERS
CUSTOMERS.CUSTOMER_NAME LIKE :E
proc Select_CUSTOMER 3_Params()
t = GetEditedText()
call ParamsEmbSQL("CUSTOMER 3";t)
1. In the “” you must write the query’s name (anything you want)
2. The query’s last field must be the table’s (CUSTOMER) primary key and the name must be the index of the relationship table (ORDER)
3. Query’s name
Step 5: Save changes
Step 6: Click the field that you want to define as a lookup field, enter the query’s name in "Selection list" and the fields with their width -separated with a ;- in "Selection list format" (in the window “Set” on the right).
Selection List: CUSTOMER
Selection list format: NAME;100;ADDRESS;50;PHONE;15;
Step 7: Exit designing and save changes
For more help please see