Create a Multiple-Column Lookup

A lookup list is a field whose we retrieve the value from another table. This article explains creating 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.

lib

proc Form_Start()
    ……
    ……
    ……

    start_sql “CUSTOMER“ 1 “KOSMOS“
         SELECT
         CUSTOMERS.CUSTOMER_NAME, CUSTOMERS.ADDRESS,
         CUSTOMERS.PHONE, CUSTOMERS.CUSTOMERS AS CUSTOMERS
         FROM
         CUSTOMERS
         WHERE
         CUSTOMERS.CUSTOMER_NAME LIKE :E
         ORDER BY
         CUSTOMERS.CUSTOMER_NAME
   end_sql

    ……
    ……
    ……
end

proc Select_CUSTOMER 3_Params()
         t = GetEditedText()
         call ParamsEmbSQL(“CUSTOMER 3“t)
end

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).
For example
Selection List: CUSTOMER
Selection list format: NAME;100;ADDRESS;50;PHONE;15;
Step 7: Exit designing and save changes

For more help, please see http://www.youtube.com/watch?v=i2OCjI1KZr8