Developing a Database Application:
The dBASE™ PLUS Tutorial

Ken Mayer, based on work by Michael Nuwer and Ken Mayer


Supplemental: Lookups


  Back to the tutorial menu     Back to previous part of tutorial: Creating the Data Modules

Goals and Objectives

The goals of this supplemental section of the tutorial are:

Additional Reading

Lookups -- What Are They?

Lookups are what they sound like -- code to look for some value, and return information based on that value. The simplest example in the tutorial project itself is the use of both the state and country tables. These tables are really basic, having two fields each. The idea is to store a two or three character value in a table, and be able to look up that value in another table, and display the name of the state or country associated with it.

In the days before the Borland dBASE™ developers introduced the Object-Oriented Database Manipulation Language (OODML), you had to write code to perform a lookup and return the required value. This might look something like the following (the sample code does not include any attempts at error trapping -- hitting the end of the table, etc. with the locate commands, etc.)

            // open the database
            open database dbasetutorial
            // point to it so we can use it
            set database to dbasetutorial
            
            // open the customer table
            use customer order names
            // find a specific customer
            locate for trim(:last name:) = "Simpson"
            // get the state id
            cState = :state id:
            
            // open the state table:
            use state order "state id" in 2
            select 2
            // find the state id:
            locate for :state id: = cState
            // display the name of the state:
            ? state
            
            select 1
            // back to customer table
            
            // cleanup
            set database to
            close databases

Of course, one could get fancier and perhaps put the lookup code in a function so it could be called as needed, which would at least simplify the main code a bit. Any code involved in displaying the information in a form (user interface) would be a bit more complex as well. However, hopefully this starts to give the picture.

With the OODML capabilities of dBASE™, this can be done automatically. But, like anything, it takes a bit of explanation. The code shown above was written to give an idea of what most lookup code is doing -- using some value to find data in another table based on that value.

lookupSQL

When the database objects were created, the developers gave us two different options specifically for performing lookups, as well as a variety of options that could be used (rowset.findKey() and others) to perform similar functionality. However, the primary focus of this discussion will be on the two properties of the field object: lookupSQL and lookupRowset. We will examine some other options in a bit.

The lookupSQL property of the field object is designed to create a brand new table reference in a form, report, or data module, to allow dBASE™ to handle the actual lookup itself. The basics are that you assign a SQL select statement to open the table (which must be in the same database, or if a different one you must modify the syntax appropriately, we won't get into all that), and return the appropriate information.

The select statement must use a minimum of two fields, and dBASE™ will do all the rest of the work. For example, if you have a data module or form set up to open the database and set up your query objects for your data, to perform a lookup using the "State ID" field shown in the XDML example above, you would (using the Inspector, or just in code) assign to the lookupSQL property of the "State ID" field:

               select * from state order by state

What confuses most developers at first is that what ends up being displayed on a form (or report, etc.) is the full name of the state, not the two-letter code, but what is stored in the customer table is the two-letter code.

The following image is an attempt to show what a lookup actually does:


Probably the most confusing thing for the developer is that once a lookupSQL property is set, querying the field returns the value returned by the lookup. The following is some basic OODML code that shows how this works:

               // set up the database object:
               dTutorial = new database()
               dTutorial.databaseName := "dbasetutorial"
               dTutorial.active       := true
               
               // set up the customer table as a query:
               qCust = new query()
               qCust.database := dTutorial
               qCust.sql      := "select * from customer"
               qCust.active   := true
               
               // set the rowset's indexName:
               qCust.rowset.indexName := "names"
               qCust.rowset.exactMatch := false // otherwise it looks for full expression
               
               // set the state id field's lookupSQL:
               qCust.rowset.fields["state id"].lookupSQL := "select * from state order by state"
               
               // find the customer:
               qCust.rowset.findKey( "Simpson" )
               
               // display the state:
               ? qCust.rowset.fields["state id"].value
               
               // cleanup:
               qCust.active := false
               release object qCust
               qCust = null
               dTutorial.active := false
               release object dTutorial
               dTutorial = null

As with most OODML, the set up and cleanup code is a bit more extensive, but if you notice the actual lookup itself is automatic. Once we set the lookupSQL property of the field, all we need to do is to query the value of the field in the customer table, and we see the name of the state, rather than the two-letter code.

One trick, because there are times that a developer may need to do this, is that you can get the underlying two-letter code (in this example):

               ? qCust.rowset.fields["state id"].lookupRowset.fields[1].value

We could use, instead of the number '1' (which means "first field in the select statement"), the name of the field:

               ? qCust.rowset.fields["state id"].lookupRowset.fields["state id"].value

The trick is knowing how to traverse the objects used here:

               qCust = query for the customer table
               rowset = rowset object for the query
               fields = fields array for the customer rowset
               lookupRowset = the rowset returned by the lookupSQL property
               fields = fields array for the lookupRowset

Notes: It should be noted that this is a simple lookup in a table that only has two fields. If your lookup needed to return a different field other than the "second" field in the table, the lookupSQL property's select statement would need to be different than what is shown above. For example, let's say the table had a third field which included the population of the state (the table doesn't actually have this, so the code shown would cause an error):

               // set the state id field's lookupSQL:
               qCust.rowset.fields["state id"].lookupSQL := "select "state id", population from state"

This would then perform the same type of lookup, but instead of displaying the name of the state, it would display the population. You would probably want to associate that with a different field in the customer table, but there you go.

Using the same additional (non-existant) field, you could actually set up your lookup this way:

               // set the state id field's lookupSQL:
               qCust.rowset.fields["state id"].lookupSQL := "select "state id", state, population from state"

Note that there are three fields there. However, to get the value of the population field to display you would need to access it specifically, doing something like:

               ? qCust.rowset.fields["state id"].lookupRowset.fields["population"].value

lookupRowset

So the big question becomes: Why do we have both a lookupSQL property and a lookupRowset property of the field object?

The first thing to note is that once the lookupSQL property is defined, the lookupRowset is automatically created, and this is what dBASE™ actually uses to perform the lookups. However, the property can be used on its own without using the lookupSQL property.

The purpose of the lookupSQL property is to define the lookup when there is no rowset already defined. If you have a small lookup table, the lookupSQL property is perfect in many situations to do what is needed. However, if your lookup table is fairly large, this can start to be a problem. The reason is that for a complex application the lookup may need to be performed for multiple fields on the same form. If your table is large, or you are using multiple lookups, then you may find your application slows down, as memory gets used to load the table(s) each time you define a lookupSQL.

The solution is to define a query just for the lookup, and then setting the lookupRowset property for the individual fields to that query. The code below shows this. It is modified to add a new query object, and then rather than using the lookupSQL property, it uses the lookupRowset:

               // set up the database object:
               dTutorial = new database()
               dTutorial.databaseName := "dbasetutorial"
               dTutorial.active       := true
               
               // set up the customer table as a query:
               qCust = new query()
               qCust.database := dTutorial
               qCust.sql      := "select * from customer"
               qCust.active   := true
               
               // set the rowset's indexName:
               qCust.rowset.indexName := "names"
               qCust.rowset.exactMatch := false // otherwise it looks for full expression
               
               // set up query for lookupRowset:
               qState = new query()
               qState.database := dTutorial
               qState.sql      := "select * from state"
               qState.active   := true
               qState.rowset.indexName := "state"
               
               // set the state id field's lookupSQL:
               qCust.rowset.fields["state id"].lookupRowset := qState.rowset
               
               // find the customer:
               qCust.rowset.findKey( "Simpson" )
               
               // display the state:
               ? qCust.rowset.fields["state id"].value
               
               // cleanup:
               qCust.active := false
               release object qCust
               qCust = null
               dTutorial.active := false
               release object dTutorial
               dTutorial = null

The nice thing about the lookupRowset property is that if you had multiple fields in the same table (or even different tables) that needed to perform lookups (for example, you might have different addresses for the customer), they can all use the same lookup table (qState, in this example) without loading the data more than once.

Using lookupSQL and lookupRowset in a Form

The nice thing about defining these properties (either one) is that if you use a combobox (the suggested control) for the interface with the field, then all you need to set is the dataLink property. When the user views the data, they will see the state names, but what is stored in the table is the two-letter code. When navigating through the table the value displayed is updated based on the code, when adding or editing the data, the value stored in the table is automatically the two-letter code, not the full name of the state. dBASE™ handles it all for you. When you get to the "Forms" part of the tutorial you will actually see this with both the State and Country tables.

Note: There is a bug with the Form Designer (which has been reported to the R&D team) where setting the lookupRowset property for a field in the Inspector will cause the designer to crash. You can, however, set the code for the lookupRowset property in the Source Code Editor, if you wish to do so, and the Form Designer will recognize it and work with it just fine after you do that (you would need to save the form, close the Form Designer, open the form in the Source Code Editor, make the change, etc.). The lookupSQL property does not cause this problem. Interestingly, the error does not occur in the Report Designer. (This was reported by Mervyn Bick.)

Other Types of Lookups

Of course the field properties shown above are great for some purposes, but sometimes you need to do things in different ways. The rowset object in dBASE™ gives you some functionality to perform lookups in three different ways, these can get a little complicated, but basically what you have are:

These are discussed in several places, see the additional reading materials listed at the beginning of this page. The disadvantages are that they don't directly interact with the user interface the way the lookupSQL and lookupRowset properties of the field object do. They are useful in code to do a lot of your work. The sample code shown above uses the findKey() method of the rowset to find a specific record in the customer table, by way of example.

That's It?

For the purpose of the tutorial, yes, that is it. This was just a side-trip designed to help explain in a little more detail how all of this works. It doesn't get into every single variation of what can be done with lookups, but hopefully it will help explain a bit better.


  Back to the tutorial menu     Back to previous part of tutorial: Creating the Data Modules