Displaying choices from another table

Problem

Often you would like to accept input from a user to a field but have the value encoded when it's stored into the database. A good example is part numbers for an order entry system. You'd like to store the part numbers in the order table but the users don't want to learn all the part numbers and would prefer to choose from descriptions.

Solution

To solve this problem you can use one of the DBPak enumerated widgets. These widgets can display one set of values and return a different value depending on the displayed value chosen by the user. Using enumerated widgets you can display part descriptions but return part numbers. You can implement the previous example easily using a XiDBCombinationBox widget, one of the DPPak enumerated widgets.

Here's an example of how to build a form that has a Combo Box to input part numbers, presumably as the beginning of an invoice order form. The part descriptions are actually displayed, but the part numbers are returned as the value from the combo box. If you'd like, you can follow along. All the tables come from the sample database, OrderEntry, supplied with DX.

1. Create a bulletin board to hold the part number combo box.
2. Select the OrderEntry database in the Schema Browser.
3. Select the InvoiceDetail table. This table is intended to be the line items on a order form in a order entry application. Notice that the InvoiceDetail table has a Part_Number field but no description. That's because you could get the description from the Inventory table that's related by the Part_Number field. There's no reason to store the description in more that one place in the database.
4. Select Combo Box as the Object Type.
   
   
5. Drag using mouse button two, the Part_Number field to the bulletin board. Two widgets are automatically created the combo box and a column. The column just provides visual alignment and displays the label.
   
   
6. In play mode now, when you pull down the combo box list, you don't see any values - not very interesting! You'd like to see descriptions with the returned values as part numbers.
7. Go back into build mode, select the combo box, and using the resource editor set the mapType resource to XiDB_MAP_SQL and the map resource to "select Part_Number, Description from Inventory". Be sure to hit OK after making the change.



8. In play mode when you pull down the combo box list, you now see a list of part names - much easier on your users. When they select a name, the part number is actually returned to the database.