Refreshing a One-to-Many Display Based on a List Value

  • Article

When the user chooses to go to a record by picking a value in a list, you might have a one-to-many relationship that needs to reflect the changed record pointer in the parent table. You can implement this functionality with both local tables and local or remote views.

Local Tables

If the RowSourceType of the list is 2 - Alias or 6 - Fields and the RowSource is a local table with a relationship set in the form's data environment, issue THISFORM.Refresh in the InteractiveChange event when the user chooses a new value. The many sides of the one-to-many relationship automatically displays only the records that match the expression of the parent table involved in the relation.

Views

Refreshing a one-to-many display is a little different if the RowSource of the list box is a local or remote view. The following example describes creating a form with a list box and a grid. The list box displays the values from the cust_id field in the TESTDATA!Customer table. The grid displays the orders associated with the cust_id field selected in the list box.

First, in the View Designer create a parameterized view for the orders. When you create the view in the View Designer, set the selection criterion for the foreign key to a variable. In the following example, the variable is called m.cCust_id.

Parameterized view using a variable

Then, when you design the form, follow the steps in the following procedure. Note that the view requires a value for the parameter that isn't available when the form is loaded. By setting the NoDataOnLoad property of the view cursor object to true (.T.), you prevent the view from being run until the REQUERY( ) function is called, at which time the user would have selected a value for the variable used in the parameterized view.

To design a one-to-many list based on local or remote views

  1. Add the table and the parameterized view to the data environment.

  2. In the Properties window for the view cursor object in the Data Environment, set the NoDataOnLoad property to true (.T.).

  3. Set the RowSourceType property of the list box to 6 — Fields, and set its RowSource property to the field referenced as the foreign key in the view's parameter.

    In the example, you would set the RowSource property to customer.cust_id.

  4. Set the RecordSource property of the grid to the name of the view you created earlier.

  5. In the InteractiveChange event code of the list box, store the value of the list box to the variable, and then requery the view, as in this example:

       m.cCust_id = THIS.Value
   *assuming the name of the view is orders_view
   =REQUERY("orders_view")

For more information about local and remote views, see Creating Views.

See Also

Allowing Users to Add Items to a List Box | Displaying Child Records in a List | Adding Pictures to Items in a List | Using Controls | Controls and Objects