John M. Miller
Combo boxes are a valuable control for interfaces, allowing the user to make a selection of pre-determined items with a couple of keystrokes or mouse clicks. The current table holds a foreign key that points to a record in a lookup table as proper data normalization would suggest. However, binding the data value that the user sees in the combo box to the foreign key that's stored in the primary table isn't straight forward. John Miller describes the pitfalls and the solutions to this important issue.
A common requirement of data normalization is to store a foreign key that points to a lookup table in the current table instead of storing the lookup value itself. It's possible, if not probable, that the user won't know what foreign key value to enter, so the user interface should be able to present a searchable list of possible values. Second, the user may not even be aware of the key if the key is system generated. Therefore the list that's presented must be able to present a descriptive value from the parent table while still processing the key field behinds the scenes. One control that's particularly suited for this task is the combo box.
A combo box consists of a text box where the user can type in a value and a control that the user can select to pop up a list of values from which to select. It's possible to create a combo box that presents the user with data from the lookup table while updating the foreign key in the primary table with the primary key from the parent table when the user selects an item from the list.
(All of the data used is these examples is included on the Developer's Disk. This is a slightly modified version of the TESTDATA database that ships with Visual FoxPro.)
The combo box builder does a decent job of creating combo boxes to validate foreign keys. The builder provides an easy-to-use interface for setting the most important properties of the combo box. In Step 1, List Items (see Figure 1), you specify what fields you want displayed in the combo box list. This step determines the RowSource property of the combo box, adds the table to the form's data environment if necessary, and sets the RowSourceType to 6 - Fields. When using a combo box to select a foreign key, it's important to include the primary key from the lookup table in the Selected Fields list even if the primary key won't be displayed.
Figure 1. Combo box builder, Step 1, List Items.
In this example you'll assign a customer to an order. The order is the current table and the customer is the lookup table. So you select the company and cust_id fields from the customer table to populate the combo box.
In Step 2, Style (see Figure 2), you determine how the combo box should appear and function. The most important setting for validating foreign keys is to select the drop-down list box. A combo box comes in two types: the drop-down combo box and the drop-down list box. A drop-down combo box allows the user to type any value into the text box, whereas the drop-down list box restricts the user to typing values that are in the list. These choices in the builder correspond to the combo box style property.
Figure 2. Combo box builder step 2. Style.
You want a drop-down list box rather than a drop-down combo box because the drop-down list box prevents the user from typing new values. The only valid values are the ones in the list.
The combo box builder's third step allows you to adjust the appearance of the list. Here you can adjust the widths of the columns and the width of the combo box (see Figure 3). Here you set the ColumnWidth property of the combo box. If the user shouldn't see the primary key value, you can prevent the column from being displayed by shrinking the column to the point where it can't be seen, effectively setting the column width to zero.
In this case you'll leave the column widths alone for now so that you can better follow the behavior of the control. You can always go back and change the widths of the columns.
Figure 3. Combo box builder Step 3, Layout.
Step 4, Value, is the most important step (see Figure 4). Here you establish how the data is to be transferred from the parent table to the child table. In this step you specify two things: "Return the value from" and "Field Name." "Return the value from" refers to the column that the user is actually selecting—the one with the full description of the item (for example, the customer name.) This corresponds to a combo box property called BoundColumn. The "Field Name" is the name of the field where the selected value should be stored—the name of the foreign key field in the current table. This is the ControlSource property. When the user selects an item from the list, the value of the control is set to the value of the bound column. When the value of the control is changed the field name contained in the ControlSource property is updated with the new value.
Figure 4. Combo box builder Step 4, Value.
When using a combo box to validate a foreign key, the BoundColumn should refer to the column number of the primary key in the lookup table. The ControlSource property should refer to the name of the foreign key field in the current table. (See Figure 5.)
Figure 5. The completed Foreign Key combo box.
When the changes in the builder are completed and the form is run, selecting a customer in the combo box does indeed update the cust_id field in the orders table with the correct value. Also, if you add navigation buttons to the form or otherwise move the record pointer and refresh the form, the combo box shows the name of the company that placed the order. This example is implemented in the form COMBOS.SCX on the Developer's Disk.
It should be noted that the form's data environment can have an effect on the combo box's behavior. If a persistent relationship from the customer table into the orders table is defined in a database container, the relationship may be brought into the data environment when the tables are added to the data environment. Since the orders table is the primary table of the form (you're updating the cust_id field in the orders table), the relationship from customer into orders is incorrect. This will cause strange behavior. When the user selects a customer from the combo box, this changes the record pointer in the customer table. The relationship then moves the record pointer in the orders table to the first order for that customer and now you're editing the wrong order! It's also important that InitiallySelectedAlias be set to the current table.
This version of the combo box works great except in one very important situation. If the primary and foreign keys are a numeric data type, then another strange behavior surfaces. It isn't a problem with this specific implementation (using character-type foreign keys) but is rather a "feature" of combo boxes whose ControlSource property evaluates to a numeric datatype. Instead of setting the value of the control to the numeric value from the bound column, the value is set to the item number of the selected item in the list. This is what's stored to the control source. If the user selects the second item on the list, a 2 is stored in the field! The reason for this is backward compatibility. However, in 2.x you couldn't have true multicolumn combo boxes so the issue never materialized.
This behavior is a problem because in Visual FoxPro you'll probably want to use the new integer datatype for your primary and foreign keys. They require only four bytes of storage, and they improve join performance.
So how can you get the combo box to handle the numeric control sources properly? This requires a bit of sleight-of-hand. You have to trick the control into thinking that it's working with character data. But before I demonstrate the technique I want to take a second a discuss an interim approach that bypasses the ControlSource property altogether.
Both of these techniques are implemented as classes and ideally they should be implemented as the foundation combo box class because you would want all combo boxes to inherit this behavior. I'll call the first example FKCombo1 and the second FKCombo2.
The first step in creating FKCombo1 is to add a custom property to the class called OldControlSource and delete the value of the new property in the property sheet. Now enter the code in the Init method of the class:
* fkcombo1.init()
if not empty( this.ControlSource ) ;
and type( this.ControlSource ) = 'N'
this.OldControlSource = this.ControlSource
this.ControlSource = ''
endif
If the control has something in the ControlSource property and the datatype of the expression contained in the ControlSource property is a numeric, this method saves the contents of the ControlSource property and then empties the property. The type function returns "N" for the numeric as well as integer and double datatypes (if you want the class to work for the currency datatype then also test for "Y.")
Setting the ControlSource to the empty string has two effects that you'll have to handle. First the control is no longer bound to the field and no changes to the value of the control will be reflected in the data. Thus, when the user selects a new customer name, for example, the contents of the foreign key field in the current table don't change. Second, since the control is no longer bound, no changes in the data will affect the form. When you move from record to record, the changes in the value of the foreign key field in the current table aren't reflected in the display of the combo box.
So now what do you do? First, to get the combo box to update the table when the value of the control changes, you'll check if the combo box is using a numeric control source. If so, you'll update the data manually. This is fairly straight-forward and is accomplished with this snippet of code:
* fkcombo1.valid()
if not empty( this.OldControlSource ) ;
and this.ListCount > 0
replace ( this.OldControlSource ) with val( this.Value )
endif
This method tests for a value in the custom OldControlSource property. If a value exists then the control source must be numeric and the data needs to updated manually by replacing the data. It also tests for the number of items contained in the list to ensure that the update occurs only when an item is available to be selected. The field name stored in the OldControlSource property is replaced with the numeric value of the value of the control. This works as long as the BoundColumn property of the control refers to the column containing the primary key in the parent table. The reason it works is because you assigned the ControlSource property an empty character value after saving its contents to the custom property OldControlSource. Since the ControlSource property is now a character datatype, the control behaves normally and stores the value of the column reference by the BoundColumn property to the Value of the control, as a datatype character!
The Valid method is fired whenever the user selects an item from the list or enters a value into the text box. The Valid method is better than the InteractiveChange or the LostFocus methods because the Valid fires only on a selection. The InteractiveChange fires whenever the user moves from item to item in the list increasing the number of updates to the data. The LostFocus fires whenever the control looses focus even if no selection is made.
The final step is to ensure that value displayed in the control is in sync with data in the field.
Again, the method is conditional based on there being a value in the OldControlSource property, which was set in the Init method when the ControlSource property was found to contain a reference to a numeric datatype. If so, the Value of the control is set to the value of the field name contained in the OldControlSource property converted into a character datatype. This forces the control to reposition itself to the proper item in the list.
The Refresh method is used since it's fired when the form's underlying data is changed and needs to be re-displayed.
* fkcombo1.refresh() if not empty( this.OldControlSource ) this.Value = str( eval( this.OldControlSource )) endif
When the FKCombo1 class is added to a form and the properties are set as I described earlier using the builder, the control will function properly regardless of the datatype of the ControlSource by sensing when it is editing a numeric datatype and changing its behavior. The form FKCOMBO1.SCX demonstrates this combo box class in action.
A problem with this implementation of the FKCombo1 class is that it makes one assumption that a class should never make. It assumes that the contents of the ControlSource property contain the name of a field. It's easily possible that the ControlSource could be a variable or even a property of another object. The replace command in the Valid method obviously wouldn't function properly if this was the case and the last thing you want is to use one class for field controls and another class for other controls. This is where the sleight-of-hand comes in.
This class, which will be implemented as a subclass of the FKCombo1 class, is called FKCombo2 to emphasize the differences between the two classes. However, in actuality all of the code would be implemented as a single foundation class because there's no real benefit in sub-classing the control and it adds an unnecessary level of inheritance.
The trick involves a change to the Valid method that removes the replace command and substitutes a bit of code that handles all ControlSource types:
* fkcombo2.valid() local lcValue, lnListIndex if not empty( this.OldControlSource ) and this.ListCount > 0 lcValue = this.Value lnListIndex = this.ListIndex this.ControlSource = this.OldControlSource this.Value = val( this.List( lnListIndex , this.BoundColumn ) ) this.ControlSource = '' this.Value = lcValue endif
What happens here is that the current value of the control, which you'll recall is a character datatype, is saved. Then the ControlSource, which is currently empty, is set to its old value, which is a numeric type. Now here's the trick. The Value of the control is set to the numeric value of the bound column of the list. This forces an update to the control source. Finally the ControlSource and Value properties are set back to their previous values. This restores the ControlSource reference just long enough to update data. This example is implemented on the form FKCOMBO2.SCX on the Developer's Disk.
Using FKCombo2 in a grid is much more flexible. It allows you to use the same control for all datatypes and all ControlSource types. However, there's one situation where the control will fail—using it in a Grid. To see this try the following steps:
1. Modify the FKCombo2 form.
2. Open the Data Environment.
3. Drag the OrdItems table onto the form to create a grid.
4. Run the Grid Builder to create a column for the order_id field and any other fields.
5. Select the FKCombo2 class combo box and cut it from the form
6. Select the grid with the right mouse button and choose edit.
7. Select the order_id field.
8. Paste the combo box into the column.
This will generate the error shown in Figure 6.
Figure 6. Visual FoxPro error message generated when attempting to add a data bound control to a bound column.
This error isn't a problem with the class, it's a conflict between the properties of the control and the column in which it is being added. It's related to the problem with the class, so resolving it will help you understand the class.
A grid column has a property called Bound and a column by default is bound. When a column is bound, all of the controls contained in the column assume the same control source as the column. The controls contained within a bound column can't have a value for the ControlSource property. When a column is unbound, the control sources of the column and each control are independent and can contain different values. Since the combo box being added to the column has a control source, it violates the requirements of the bound control and generates the error.
In order to add the combo box to the column, paste the control back on the form and remove the contents of the ControlSource property in the property sheet by resetting it to its default value. You must reset the ControlSource to its default value because once a property has been changed it now has a value different from its parent class even if that value is the empty string. Now cut the combo box from the form and paste it into the column as I described earlier.
Next, set the CurrentControl property of the column to the new control as show in Figure 7.
Figure 7. Setting the CurrentControl property of a column.
This tells the column to use the combo box rather than the default text box when the user selects this column.
Once the FKCombo2 combo box is contained within the column and is the current control, the form can be saved and executed. Running the form generates the same error that you saw in Figure 6 and Trace window is activated with the line of code highlighted in Figure 8.
Figure 8. Trace window with line causing error highlighted.
The error is generated for the same reason the control couldn't originally be added to the column. A control contained within a bound column can't have a control source other than the default. What this means is that in order to use the FKCombo1 and FKCombo2 classes in a column of grids, the column must be unbound.
If the Bound property of the column containing the FKCombo2 control is changed to false, the form will run, but there are a couple of problems. The data from the parent table is displayed only when a cell from the column containing the control gets focus and selecting an item from the combo box no longer updates the foreign key field in the child table.
The reason that the data from the parent table is displayed only when a cell from the column containing the control gets focus is because the column is bound to the foreign key field and the combo box is displaying data from the lookup table. By default a column is sparse, meaning that the control specified in the CurrentControl property is available only when the column gets focus.
However, setting the Sparse property to false won't solve the problem. The combo box will be displayed for every row, but the value displayed will be for the first row refreshed, and this value is repeated for every row. This is an unexpected result but even if it did work, it would be undesirable for two reasons: Changing a column from sparse to non-sparse can have a significant impact on the performance of the grid. Displaying a combo box in every row of the grid is unnecessary from a user interface perspective. The solution is to change the ControlSource property of the column to match what is displayed in the combo box. This reinforces the switch to a unbound column.
Selecting an item from the combo box no longer updates the foreign key field in the child table because the combo box loses its control source when the column becomes unbound. Reestablishing the control source will remedy this problem.
One last issue that remains is to properly implement the relationship between the parent and child tables in the data environment. It's important that a relationship be defined from the child table into the parent table. This relationship is contrary to the relationship that is automatically created when two related tables are added to the data environment. Usually a relationship goes from the lookup into the current table, but in this case the relationship must go the other way to ensure that the correct data from the lookup table is displayed when a record from the current table is displayed.
Here are the steps necessary to add the FKCombo2 class to a grid column manually:
It requires quite a bit of manual effort to properly implement the class in a grid, and still more work is required to get the combo box to appear correctly. It would be much better if the combo could be added easily to the column. In the next example the class will be modified to automatically make the required adjustments to work properly within a column.
Once again this will be a subclass of the previous class and you'd probably want to incorporate all of these separate classes into a single foundation class. They're only presented as separate classes to emphasize the solutions to the individual problems.
The new FKCombo3 class focuses on establishing the combo box within a column of a grid and therefore it deals primarily with the Init method. This modified Init is as follows:
* fkcombo3.init()
if this.parent.Baseclass = 'Column'
with this.parent
.Bound = .f.
this.ControlSource = .ControlSource
.ControlSource = this.ColumnName( this.RowSource , 1 )
.CurrentControl = this.Name
endwith
this.SpecialEffect = 1 && No 3D
with this.parent.Text1
this.FontBold = .FontBold
this.FontName = .FontName
this.FontSize = .FontSize
endwith
endif
fkcombo2::Init()
The first problem that the class must solve is determining when the combo box is contained within a column. Since the combo box will almost always be contained within another object, a test of the BaseClass property of the parent container will indicate whether the parent object is a column.
Once it's determined that the parent object is a column, several properties of the column are modified to accommodate the combo box. First the Bound property of the column is set to false to unbind the column and allow the combo box to have a different value for ControlSource property than the column. Next the combo box's ControlSource property is set to the ControlSource property of the column, and the ControlSource property of the column is set to the name of the first column that is displayed in the combo box.
This transfers the control source from the column to the combo box and synchronizes what is displayed in the combo box and what is displayed in the column. This is accomplished through a custom method of the class that returns the name of the field from the RowSource property of the comb box. Here's the code for the ColumnName method:
* fkcombo3.ColumnName()
lparameter tcRowSource, tnColumn
local lnDot, lcAlias, lcFieldList, lnStart, lnEnd, lcRetVal
lcRetVal = tcRowSource
if not empty( tcRowSource )
lnDot = at( '.', tcRowSource )
lcAlias = left( tcRowSource , at( '.', tcRowSource ) )
lcFieldList = substr( tcRowSource, ;
at( '.', tcRowSource ) +1 )
if tnColumn = 1
lnStart = 1
else
lnStart = at( ',', lcFieldList, tnColumn - 1 ) + 1
endif
lnEnd = at( ',', lcFieldList, tnColumn )
if lnEnd = 0
lcRetVal = lcAlias + substr( lcFieldList, lnStart )
else
lcRetVal = lcAlias + substr( lcFieldList, lnStart, ;
lnEnd - lnStart )
endif
endif
return lcRetVal
The custom ColumnName method accepts two parameters: the contents of the RowSource property to be analyzed and the number of the column to be returned. The method then uses some standard FoxPro string manipulation techniques to parse from the row source the name of the desired column.
One thing that's not immediately obvious is that the RowSource property in this case follows a specific format. When the RowSourceType property of the combo box is '6 - Fields' the RowSource property contains a list of the fields delimited with commas. Additionally, the first, and only the first, field name in the list can contain a table alias. The method must extract the alias from the first field and add it to the field name if the column number requested is greater than one.
Back in the Init method, once the ControlSource properties are established, the CurrentControl property of the column is set to the name of the combo box control, making the combo box the control that is used to edit data within the column.
Next follows a group of optional commands that change the appearance of the combo box to allow it to blend in with the other controls in the grid. Most importantly, the SpecialEffect property is set to 1 to make the control appear plain.
Finally, the Init method of the parent class is called to perform the standard initialization required in the case of numeric primary keys. (See Figure 9.)
Figure 9. Example of FKCombo3 class implemented in a grid column.
Adding a combobox control that validates foreign keys to a grid manually is a complicated process. This class encapsulates that process into the Init method of the class, thereby preserving this knowledge and isolating you from having to implement these step repeatedly. The only problem is that it doesn't work! When the combobox is added to the grid, switching the ControlSource property back to the original numeric field name no longer causes the control to update the field. However, this can be remedied easily. When the combobox is contained within a grid the ControlSource can be set only to the name of a field. Grids can only browse data in tables. Therefore, we can revert to the solution in the FKCombo1 class of replacing the value, but only when the control is placed in a grid. Here's the modified method:
* fkcombo3.valid()
local lcValue, lnListIndex
if not empty( this.OldControlSource ) and this.ListCount > 0
if this.parent.Baseclass = 'Column'
replace ( this.OldControlSource ) with val( this.Value )
else
lcValue = this.Value
lnListIndex = this.ListIndex
this.ControlSource = this.OldControlSource
this.Value = val( this.List( lnListIndex , this.BoundColumn ) )
this.ControlSource = ''
this.Value = lcValue
endif
endif
The end result is a combo box class that is much more flexible and usable than the native Visual FoxPro baseclass. In implementing the class a couple of important ideas and techniques are uncovered.
First is the importance of foundation classes—and subclassing the native VFP controls. If you have an existing application that subclassed the combo box control and implemented that subclassed control, it would be a simple matter to implement the code described here in the foundation class.
Another important point is the benefit of encapsulation. By removing the REPLACE command from FKCombo1 and substituting code that restored the original control source, the class was made to function in all uses of the combo box rather than a specific case. The REPLACE command assumed that the control source of the control was a field, which isn't always the case.
A third important concept is to take full advantage of the properties and methods of the controls to solve problems like these. Visual FoxPro exposes so much of the internal workings of the controls that you can often find other ways of accomplishing a goal if you understand the control well enough. Two examples of this are using the list property of the combo box to determine the value of the bound column of the control and using the base class of the parent container to determine if the control is contained in a column.
Visual FoxPro is a versatile product. The impossible is now within our reach.
John M. Miller is a internationally recognized Visual FoxPro consultant, author, speaker, and trainer. As president of Perpetual Data Systems, a Southern California consulting firm, John is responsible for the design and implementation of Visual FoxPro applications and frequently provides technical expertise to in-house development staffs at major corporations. He is the author of several books and articles on Visual FoxPro programming. CompuServe 74176,200.
To find out more about
FoxTalk and Pinnacle Publishing, visit their website at
http://www.pinpub.com/foxtalk/
Note: This is not a
Microsoft Corporation website.
Microsoft is not responsible for its content..
This article is reproduced from the August 1996 issue of FoxTalk. Copyright 1996, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. FoxTalk is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.