Question: I have a list (or combo-box) on a form. How to I change the values in the list when the form is running?

Answer: When you define a field object as a selection list or combo box, Paradox creates an additional list object. The list object provides properties that change the list dynamically, e.g. while the form is running:

1. The DataSource property assigns the contents of a table to a list.

2. The List.Selection and List.Value properties let you select and manipulate individual list values.

This article discusses these in detail. It provides several examples, discusses design considerations, and offers several tips for using lists more effectively.

Note: In Paradox, the word "list" can be confusing. Does it mean a field object defined as a list? Or does it refer to the underlying list object? This article uses the phrase "selection list" for the former and "list object" for the latter.

For your convenience, a ZIP file containing the examples used in this article is available from our Download area. We recommend installing these files into a new directory. Please note that you need to change your working directory to the directory containing the unzipped files before you can open the enclosed Examples form.

Understanding Lists

The key to managing lists effectively lies in understanding the objects Paradox creates when you define a field object as a selection list or drop-down combo box. When you do this, Paradox creates two objects, a field object and a list object. The field object manages the value selected by the user and the list object manages the underlying list of values. In addition, Paradox creates a temporary text object when you physically type values into combo boxes. This text object disappears when you submit (or save) your new value into the field object. (As we said earlier, it can confusing--especially when you're new to Paradox.)

To change lists dynamically, you need to change the properties of the underlying list objects, not the field objects. The next few sections demonstrate this in detail.

Populating lists from tables and queries

The easiest way to dynamically populate a list is with a table. Suppose, for example, you have a single-field table called Colors, which contains seven records, each naming a color in the rainbow. Furthermore, suppose you want to populate a combo box with the data in this table when your form opens. To do this, add the following code to the open() event of the combo box's list object:

   doDefault
   Self.DataSource="[:work:colors.colorname]"

Please note that the square brackets ([]) are required.

Unfortunately, it's not always feasible to directly assign a table to DataSource, for you may have duplicates or the values may not be sorted properly. For example, suppose you're creating an order entry form using the sample files typically provided with Paradox. If you've reviewed the Stock table, you may have noticed that it contains two descriptive fields for the items in stock: Equipment Class (which specifies the type of equipment available) and Part No (which uniquely identify each item).

Now, suppose you've decided to create two combo boxes, one that lets the user choose from the available equipment classes and a second that shows the part numbers in the selected class. Naturally, you'll want to re-populate the part numbers when the user chooses different categories.

You can't directly assign Stock to the DataSource property of either combo box's list object, for you'd get many duplicates and the order of the records would confuse many users. However, you can use queries to limit and sort the values in Stock and then assign the resulting Answer tables to the appropriate DataSource properties.

The following example (taken from the open() event of the list object attached to a combo box) shows how to populate a list from a QBE query:

var
   qbeClasses  Query
endVar

   setMouseShape( mouseWait )
   doDefault
   qbeClasses = Query

      Stock.db | Equipment Class |
               | Check           |
   endQuery
   qbeClasses.executeQBE()

   Self.List.Count = 0  ; clears existing values
   Self.DataSource="[:priv:answer.equipment class]"
   setMouseShape( MouseArrow )

The next example shows how to update the second combo box to display the part numbers associated with the selected equipment class. It's taken from the arrive method of the second combo box in our example.

method arrive(var eventInfo MoveEvent)
var
   dbActive  Database
   sqlItems  SQL
endVar

   doDefault
   setMouseShape( mouseWait )

   ; Clear previous value, if any.  It may not longer match.
   if not Container.isBlank() then
      Container.Value = ""
   endIf

   dbActive.open()
   sqlItems = SQL

      select distinct s.Description from "stock" s
      where s."Equipment Class" =
         ~("\"" + fldClasses.theList.List.Value + "\"")

   EndSQL
   sqlItems.executeSQL( dbActive )
   fldItems.theList.DataSource = "[:priv:answer.Description]"

   setMouseShape( mouseArrow )

endMethod

We used a SQL query in the second example to illustrate additional possibilities. Please note that the above code assumes that the equipment class combo box has been named fldClasses and its list box has been named theList. It's also possible to locate a list object generically, as illustrated here.

Note: Those using SQL queries may be interested in the way we inserted the first list's value into the SQL query. A future article will explore this technique in more detail.

Design Considerations

Strictly speaking, the second example doesn't behave exactly according to the rules laid out earlier. Specifically, the second combo box doesn't update until you actually move focus to it. You may recall we said we wanted to update the part numbers when the user selects a different equipment class. While we could certainly implement the code to behave that way (perhaps by adding it to the first combo box's ChangeValue event), doing so actually risks making things more difficult for users, for the underlying query would be run each time the first combo box is changed.

Suppose, for example, you consistently select the wrong equipment class. This would, in turn, run the SQL query each time the user makes a mistake. By designing the code to run when the user arrives on the second combo box, we reduce the side-effects of a simple (and common) mistake and, in turn, reduce the user's frustration associated with simple typographic errors. Considerations like this can be as important as the layout of your applications.

While queries limit and sort values assigned to DataSource properties, they can run slowly. This happens because queries generally do not use indexes when retrieving data. As a result, each record is individially examined and then copied into the Answer table if it matches the query. Next, duplicates are removed (if appropriate) and the results are sorted.

This process usually runs quickly when working with data stored on your local hard drive; however, delay due to the amount of work can become very noticeable when working with data stored on network or remote servers--especially when you have more than one query running when your form opens.

Because of this, we recommend against using queries to populate lists, especially while your form opens. Instead, we prefer to use TCursors and secondary indexes to populate individual list values dynamically, as shown later in the next section.

Populating lists manually

You can also modify individual values of list objects using the List.Selection and List.Value properties. Set List.Selection to the item number you want to change and List.Value to the text you the list to display. The next example, also taken from the open() event of a list object, populates a list object with the names of the months. (As a bonus, it also selects the name of the current month as a default value):

method open(var eventInfo Event)
var
   aryMonths Array[] String
   siCounter SmallInt
endVar

   doDefault
   aryMonths.addLast( "January" )
   aryMonths.addLast( "February" )
   aryMonths.addLast( "March" )
   aryMonths.addLast( "April" )
   aryMonths.addLast( "May" )
   aryMonths.addLast( "June" )
   aryMonths.addLast( "July" )
   aryMonths.addLast( "August" )
   aryMonths.addLast( "September" )
   aryMonths.addLast( "October" )
   aryMonths.addLast( "November" )
   aryMonths.addLast( "December" )

   self.list.count = 0  ; clears the list.
   self.list.count = 12 ; performance trick
   for siCounter from 1 to 12
      self.list.Selection = siCounter
      self.list.Value = aryMonths[ siCounter ]
   endFor

   ; Now, select this month
   self.list.Selection = month( today() )
   container.value = self.list.Value

endMethod 

Because this is attached to the list object, the code is self contained and portable. You can copy the field object (and the code) to a new form and it will still work, regardless of the names of the objects involved.

In practice, though, it's a good idea to name objects that you manipulate with code. This avoids naming conflicts when copying objects between forms and creates more self-documenting applications.

The previous section noted the performance limitations of queries in a networked environment. The following shows a better way to populate lists more quickly in these situations:

method arrive(var eventInfo MoveEvent)
var
   tcStock  TCursor
   siItems  SmallInt
endVar

   doDefault
   setMouseShape( mouseWait )

   ; Clear previous value, if any.  It may not longer match.
   if not Container.isBlank() then
      Container.Value = ""
   endIf

   tcStock.open( ":work:Stock" )
   tcStock.SwitchIndex( "ClassPartNo" )
   tcStock.setRange( fldClasses.theList.List.Value )
   self.List.Count = 0
   self.List.Count = tcStock.nRecords()
   siItems = 0
   scan tcStock :
      siItems = siItems + 1
      self.List.Selection = siItems
      self.List.Value = tcStock."Part No"
   endScan

   setMouseShape( mouseArrow )

endMethod

Either approach will appear to run equally well when used on small tables stored on the local hard drive; however, the approach shown above will perform much more quickly when used with larger tables stored on a network or remote server.

Note: The ClassPartNo secondary index provided with the article's sample file is not provided with the Paradox samples. You'll need to create this index on the Stock table to repeat this example using the sample tables. To do so:

1. Restructure the Stock table.

2. Create a new secondary index and name it ClassDesc.

3. Add the Equipment Class and the Description fields to the index.

4. Save your changes.

At this point, you should be able to repeat the previous example with your own copies of the sample tables.

As you can see, Paradox provides a great deal of flexibility for populating lists. Once you understand the basic properties involved and have a little experience using other available tools, you can frequently create database applications that behave exactly the way you want (or need) them to. Granted, some ways or more appropriate than others, but this generally depends on the specific circumstances involved and the application you're trying to create. In general, you're really only limited by your creativity.

Tips for using lists effectively

This section provides tips designed to make lists easier to work with, both at Design time and while your form is running.

1. It can be difficult to select the list object of combo boxes. For best results, use the Object Tree pane of the Object Explorer.

2. When resizing selection lists, remember to resize the list object and not the containing field object. By default, the field object expands to hold the list object. Unfortunately, the reverse isn't true.

3. If, like us, you prefer to use wide scrollbars in your Paradox applications, remember that field objects do not support scroll bars. (The Wide scrollbar property is disabled in the property sheet.) Instead, you need to select the list object to set this property.

   Alternatively, you can select the field object and then press Shift+F6 to set the Wide scrollbar property. For more information, please see Changing Properties of Multiple Objects.

4. As far as possible, try to place code on list objects, instead of their attached field objects. This lets you use the self and container ObjectPAL built-in variables and makes your work more portable between forms. The Months example illustrates this.

5. When using unbound combo boxes (e.g. those that aren't bound to a field in a table), it's frequently useful to set default values. The following example taken from a list object's open() event shows one way to do this:

   doDefault
   Self.List.Selection = 1
   Container.Value = Self.List.Value

6. You can also use default values to determine whether or not your users actually select values from your lists. If you add a control value (such as "<Select A Value>") as the first item in your list, you can text the value of the field object for that value. Wrapping your control value in angle brackets ensures your control value appears at the top of the list.

   To detect the error condition, compare the value of the field object to your error control value in your field object's CanDepart event, as shown in the following code sample:

   var
      strDefault String
   endVar
   
      fldCombo.theList.List.Selection = 1
      strDefault = fldCombo.theList.List.Value
      if fldCombo.Value = strDefault then
         msgStop( "Can't Continue",
                  "You need to select a value from the list." )
         fldCombo.moveTo()
      else
         doSomething( fldCombo.Value )
      endIf

7. To clear a list, set the list object's List.Count property to 0.

   Tip: If you're working with lists containing many values or values that can vary widely, it's a good idea to add the following code to the close() event of your list object:

      self.list.count = 0

   This clears the list when the form is closed or put into Design mode.

8. For better performance when adding individual values to a list, set the list's Count property to the total number of items before adding the items. Again, see the Months for a demonstration.

9. Remember that users can type any value they like into field objects. To verify that your users enter values that are actually in your list, add code to compare the user's value against the contents of the list. For more information, please see Using Lists to Limit Field Values.

10. Lists are best used for small sets of values. If you have a large number of values to choose from, you should store them in a separate table and then display them using a second form. (An example showing how to do this will be posted soon.)

11. Due to a long outstanding bug in Paradox, combo boxes placed near the bottom of your form will appear clipped when the list extends below the border. Short of using the Combo Box control from the ActiveX toolbar, the only workaround is to place the combo box so there's enough room to display the list. Yes, it's annoying, but that's all there is to it.

12. To automatically display the drop-down list when a combo box receives focus, add the following code to the field object's arrive event:

       self.postAction( EditDropDownList )

13. In Paradox v10, Corel introduced an interesting new feature called StringLists, which are (essentially) text files containing string values. A future article will demonstrate these in action by showing how to assign a StringList to a List Object.