by Dr. Peter Wayne
Alpha Five has many types of lookup fields: you can define a lookup field to use a predefined list, a cascading list, an external table, a choice of external tables, or the field's previous values:
Fig. 1. Lookup choices.
If you define a Table Lookup for a field, then you can specify other fields that are also filled in when the lookup takes place. A typical example would be the fill-in of a customer address, city, state and zip after the customer name is matched. In this example, the "last_account" and "last_amount" fields are used to fill in the "amount" and "account" fields after a match is made on "payee":
Fig. 2. With a table lookup, you can specify other fill-in fields.
The table lookup suffices for almost everything one could ask for in a lookup field. There's only feature missing: the user interface for the table lookup. To select one of the table entries in a form, the user has to press {Alt-Down} or mouse-click on the field expansion icon to see the list of entries in the table. Wouldn't it be handy if you could just start typing and have the field fill itself in, matching itself dynamically against the table options as the typing occurs?
Alpha Five has such an option - the Previous Values lookup. A previous values lookup does a dynamic fill-in as the user types:
Fig. 3. Here, the user types "fr" and the field auto-fills to "frank nelson".
In a previous article I showed how to create your own self-filling field. That article used the OnKey event and involved a lot of complicated code. I recently realized that I could use the previous values field rule to eliminate the need for the OnKey coding, and that I could add some table lookup-style features to fill in other fields in my table.
The basic table for this example is called sample_checkbook:
Fig. 4. Structure of sample_checkbook.
I then created a lookup table, sample_history, to hold the values of each payee's last payment amount and last account:
Fig. 5. Structure of sample_history.
The sample_history table is indexed on the "payee" field with an index named "payee":
Fig. 6. Payee index.
The payee field in sample_checkbook is made into a previous lookup field:
Fig. 7. The payee field in sample_checkbook is defined as a previous-lookup field.
I use the OnSaveRecord event for sample_checkbook to store the most recently entered payee information into the sample_history file:
Fig. 8. The checkbook's OnSaveRecord event stores the last amount and last account values in the history table.
The heavy lifting is done in the OnWroteField event for the payee field. When the user leaves the payee field after making a change or entering a new payee, the OnWroteField event is triggered. The code in this event opens the history table and searches for previous payments to this payee. If previous payments are found, then the last amount and last account values are read and stuffed as defaults into the current record:
Fig 9. The OnWroteField event for payee.
Here's an example of how an entry works. The user starts to make a new entry, and when he enters the "s" in payee, the payee field is filled in with "selwyn rabins". The user accepts that by pressing {enter}, and then the amount and account fields are filled in from the history table:
Fig. 10. The previous value/table lookup hybrid in use.
Download the sample checkbook and history table to study the coding at your leisure.
10/22/00
Don't forget, we need your feedback to make this site better!