PRB: Form with One-to-Many View Does Not Update Parent Records (130864)

CAUSE

This is by design. The data in the view is the result of a query that was performed when the form was initially loaded. The query creates a cursor in memory with a copy of the data from the source tables. During the form load, a thermometer bar appears demonstrating the progress of this query.

When a TABLEUPDATE() command is issued, the changes are sent to the source tables. The child records in the view are not updated because there is no dynamic link between the view and the source table. These records do not display the new information until the query is run again.

RESOLUTION

Here are two possible solutions:

Base the form on the source tables instead of a view. This is possible only if the view is completely comprised of local tables.

-or-
Issue a REQUERY() function after each TABLEUPDATE().

Choose the best solution for your situation by weighing the advantages of using a view with the performance hit caused by the REQUERY() function.

You can place the REQUERY() function in the show or refresh event of the form. This causes the query to be performed each time a Form.Show or Form.Refresh is issued, so the most recent data is always displayed. Alternately, you can place the REQUERY() function in all events that use a TABLEUPDATE() command, such as the InteractiveChange event of a grid or the Click event of a command button.

MORE INFORMATION

When using Visual FoxPro for the Macintosh 3.0b the expression "right- click" means pressing the Control button on the keyboard while clicking.

Steps to Reproduce Behavior

Issue the following command to open the Tastrade database: OPEN DATABASE c:\vfp\samples\mainsamp\data\tastrade MODIFY DATABASE

Right-click the gray background of the Database Designer. Choose New Local View. Click the New View button.
When the "Add Table or View" dialog box opens, select the customer Table and then click the Add button to add the table to the view. Similarly, add the Orders and Order_Line_Items tables to the view. Close the dialog box.

In the Fields tab, add the following fields to the view: Customer.Customer_id, Customer.company_name, Orders.customer_id, Orders.order_id, Orders.order_date, Order_line_items.order_id, Order_line_items.quantity.

In the Update Criteria tab, click the Send SQL Updates box. Make sure all tables are selected from the Table list box. Click in the key column to select the key fields for each table. Make sure that only the key column (Customer.customer_id, Orders.customer_id, and Order_Line_Items.order_id) fields are selected as key fields. Click the Update column, to select all other fields. Click the SQL WHERE clause "Key Fields Only" and the Update using "SQL Update" buttons.
Save the view as MyView.
Create a new form. From the View menu, choose Data Environment. Use a right-click to select Add Table. In the "Add Table or View" dialog box, select to display Views and select the Add the view you created in step 6. Click the ADD button to add the table to the Data Environment of the Form and then close the dialog box.
In the Data Environment of the Form, select the view you just added. Right-click, and select Properties.
Set the BufferModeOverride property to 3 - Optimistic Row Buffering.
Click the title of the view in the data environment, and drag it onto the form. A grid should appear.
Add a command button with the following code in the click event: =TABLEUPDATE()
Save and run the form. Change the company name for one of the companies that has more than one record. Click the command button.
Close the form and reopen it in the Forms Designer. Add the following line of code after the =TABLEUPDATE() in the command button created in step 11: =REQUERY()
Rerun the form and change the company name back to its original value. The updated data should show for all records after the command button is clicked.