Tips & Tricks: Value Lists or Smart Lookups?

Working in tech support, I’ve noticed that developers learning to use Instant Developer almost immediately run up against the data decoding issue.

Imagine you need to create a Products form, and that the corresponding table contains a CategoryID field that links each product to its category. When showing the data to the user, we can’t use just the ID because it doesn’t mean much: we also have to show the CategoryName. How do we do that?

We could add a column to the load query and also join the Categories table, but there are at least two problems with this solution: the user would not be able to use the CategoryName field to edit information, and the driver might object when updating a query with join. Instant Developer offers a more intriguing mechanism, in which you can add other queries to the main form query: value list queries and smart lookup queries.

Value list queries allow you to decode the value of a field with a combo box whose rows are loaded from the database and sent to the client. It’s a very simple mechanism, but the response from the database has to be fast and have a relatively small number of records to prevent slowing – say less than 100.

Smart lookups work differently, because they send the client just the decoding of the current value and only send the complete list when the user opens the combo box. This makes for faster communications. In addition, writing directly to the field makes it possible to further minimize the data sent and to run a true search, even offering an auto-complete function. You could write “con” in the lookup field in order to choose from Condiments and Confections, as in the image above.

So: to decode the values of a small table, use a value list query; otherwise, use a lookup query. If you haven’t already, take a look at chapter 4.3 of the User’s Guide.