Tip of the Month (January 2007)

Updating Multiple Tables at the Same Time

A form can display data from many tables by using grid controls, lookup controls, dropdown controls, etc. but it can only update a singe table. If you want to update more than one table at the same time, it can be done using multiple forms and macros.

For example, say you have three tables:

     ORDER
     -----
     ORDER_NUMBER (indexed)
     DATE
     NAME
     ADDRESS

     ORDER_ITEM
     ----------
     ORDER_NUMBER (indexed)
     PART_NUMBER
     QUANTITY

     INVENTORY
     ---------
     PART_NUMBER (indexed)
     DESCRIPTION
     NUMBER_IN_STOCK

You want to build a system where the user can add an ORDER_ITEM record to an ORDER, and reduce the QUANTITY from the NUMBER_IN_STOCK.

To do this, you will need three forms: ORDER.VCE, ADD_TO_ORDER_ITEM.VCE, and REMOVE_FROM_INVENTORY.VCE.

1. On the ORDER.VCE form, create Jump Button that jumps to ADD_TO_ORDER_ITEM.VCE keyed on ORDER_NUMBER. Label the button "Add item". Turn on the "Always create" option.
2. On the ADD_TO_ORDER_ITEM.VCE form, add the following controls:
   • A dropdown over the PART_NUMBER field so the user can select the desired part (the dropdown should use the "Include from table" property to fill up the dropdown with part numbers from the INVENTORY table).
   • An edit control connected to @var(34) (as an integer), so the user can specify a quantity.
   • You might consider adding a lookup control, keyed on PART_NUMBER that searches INVENTORY and displays the DESCRIPTION (when asked whether or not to save the value found, reply NO), so the user can see what part they just selected.
   • You might also consider adding a grid control to display the items in the order.
3. On the ADD_TO_ORDER_ITEM.VCE form, create a macro (EDIT | MACROS/EVENTS) called ADD_TO_ORDER that does the following:
   Step 1: ASSIGN the value (QUANTITY + @var(34)) to the column QUANTITY
   Step 2: Jumps to the REMOVE_FROM_INVENTORY.VCE form, keyed on PART_NUMBER
4. On the ADD_TO_ORDER_ITEM.VCE form, create a command button labeled "Add to order". Set this button to run the macro ADD_TO_ORDER.
5. On the REMOVE_FROM_INVENTORY.VCE form, create a macro (EDIT | MACRO/EVENTS) called STARTUP that does the following:
   Step 1: ASSIGN the value (NUMBER_IN_STOCK - @var(34)) to the column NUMBER_IN_STOCK
   Step 2: RUN EXTERNAL (not jump) to the ORDER.VCE form
6. On the REMOVE_FROM_INVENTORY.VCE form, set the "On Startup" event (EDIT | MACROS/EVENTS | EVENTS) to launch this startup macro.

You probably should also provide your user with another form over the INVENTORY table to allow the user to add INVENTORY items.

Your end-user can now use these forms as follows:

1. Add some items to the INVENTORY table
2. Run the ORDER.VCE form
3. Preses the "Add to item"
4. Select the desired part
5. Enters the quantity
6. Press the "Add to order"