1. Tech

Your suggestion is on its way!

An email with a link to:

http://delphi.about.com/od/database/l/aa090401a.htm

was emailed to:

Thanks for sharing About.com with others!

Lookup!
Page 1: Why and when to use lookup fields; Creating a data entry form
 More of this Feature
• Page 2: New...lookup
• Page 3: DBLookupCombo
• Page 4: DBGrid.PickList
 Join the Discussion
"Post your views and comments to this chapter of the free Delphi database Programming Course"
Discuss!
 Related Resources
• free DB Course.TOC
• Coloring the DBGrid
• Using Data Controls
• more Database articles

Back in the master-detail relationships chapter of this course, we've seen that in most cases one database table is related to the data in one or more other tables.

Consider the next example. In our sample aboutdelphi.mdb database, Applications table lists Delphi applications uploaded by the visitors to this site. Among other fields, the Author and Type fields are linked with the corresponding fields in the Authors and Types tables. Only values from the AuthorName field in the Authors table can appear in the Author field of the Applications table. The same rule is applied to the TypeName field (Types table) and the Type field (Applications table).
Another situation: consider an application data entry form whose fields are connected to the Applications table. Let's say that this table has only one information related to the author of the application, an AuthorNo field corresponding to authors unique number. The Authors table, on the other hand, contains an AuthorNo field corresponding to authors UN, and also contains additional information, such as the authors name, email and a web page. It would be convenient if the data entry form enabled a user to select the author by its name (and email) instead by its UN.

Lookup!
If you have a Delphi form with data controls designed to allow editing the Applications table, you have to make sure that only TypeName values from the Types table can be applied to the Types field of the Application table. You also have to make sure that only AuthorName values from the Authors table can be applied to the Author field of the Application table. The best way to make this sure is to provide users with a string list to select the values from rather than having them enter values manually.

Both TDBLookupListBox and TDBLookupComboBox are data-aware controls that enable us to choose a value from another table or from a predefined list of values. This pair of components are so similar that it makes sense to discuss only one of them. Both components are designed to list items from either a dataset or from a secondary datasource. We'll be looking at the TDBLookupComboBox component.

In this chapter, we'll see how to use lookup fields in Delphi to achieve faster, better and safer data editing. We'll also see how to create a new field for a dataset and discuss some of the key lookup properties. Plus, take a look at how to place a combo box inside a DBGrid.

There are three ways you can set a lookup field in Delphi.

1. If you have a data form where all editings are done in a DBGrid, the best is to create a new (lookup) field for the dataset.
2. If a form hosts a set of data controls (DBEdit, DBComboBox, etc.), it makes sense to just use DBLookupComboBox without creating a new field.
3. The last one is to use columns of a DBGrid with its PickList property, again without creating a new field. This approach is not a true lookup approach but you'll see that it can act as one. The PickList list values that the user can select for the field value - just like a standard DBComboBox - but inside a DBGrid. We'll make it to list values from another dataset, thus defining sa lookup.

We'll discuss each of them, but we first need to build a data entry form.

   Creating a data entry form
Creating a data editing form by hand is not to much complicated, as we already know. When developing database applications with Delphi (and ADO), most of the work is done inside the IDE by simply connecting various components together, thus having to write no code. A typical data browsing/editing form presents a database table inside a DBGrid. Another way is to add several data aware controls to a form and link them to the data source. We'll place both a DBGrid and several data aware controls.

Have a new Delphi project with an empty form, then add the next set of components: one ADOConnection, one DataSource and two ADOTables.
Use the Object Inspector and connect all those components in the following way:
First set the name of the ADOConnection component to be ADOConnection. Use ConnectionString property to link to our aboutdelphi.mdb database (LoginPrompt = False). Set ADOTable1.Name = ApplicationTable, ADOTable2.Name = AuthorsTable. Set DataSource1.Name = ApplicationsSource. Set the Connection property of all ADOTable components to point to ADOConnection component. Set ApplicationSource.DataSet = ApplicationsTable. Finally, set ApplicationTable.Table = Applications, AuthorsTable.Table = Authors. Finally, add a DBGrid (DBGrid1) to a form and Connect it with ApplicationsSource.

Here is a list of relevant values, as can be seen in the dfm file for a form.

  object ApplicationsTable: TADOTable
    Connection = ADOConnection
    TableName = 'Applications'
  end
  object AuthorsTable: TADOTable
    Connection = ADOConnection
    TableName = 'Authors'
  end
  object ApplicationsSource: TDataSource
    DataSet = ApplicationsTable
  end
  object DBGrid1: TDBGrid
    DataSource = ApplicationsSource
  end

Double click the Applications table (Fields editor) and create a persistent set of field objects - pick Name, Description, Type, Size and Author.

All set up (at least for now). It's time to see the first approach to lookup fields.

Next page > New...lookup field > Page 1, 2, 3, 4

DB Course Next Chapter >>
>> Compacting an Access database with ADO and Delphi - DB/16

©2014 About.com. All rights reserved.