Tip of the Month (April 2009)

Creating a Visual CE Macro that Updates More Than One Table

When a Visual CE macro runs, it can update records in the table associated with the form that holds the macro, but it can't write to any other table. For example, say you have a CUSTOMERS table and an ORDERS table: a macro in ORDERS form can record the order, but it can't update the CUSTOMERS table to record the date of the last order. But you can do this by "handing off" control from the ORDERS form to the CUSTOMERS form.

For example, let's say what you really wanted was a macro that did this:

Step 1: ...some sort of pre-processing on the ORDERS form
Step 2: Assign @today to LAST_ORDER_DATE on the CUSTOMERS form
Step 3: ...some sort of post-processing on the ORDERS form

On your ORDERS form, do the following:

1. EDIT | MACROS/EVENTS and create a macro called SAVE_ORDER
2. Write the macro:
   Step 1: ...some sort of pre-processing on the ORDERS form
   Step 2: ASSIGN (Value: -1, Col: @var(34))
   Step 3: RUN EXTERNAL (Command: CUSTOMERS.VCE)
   Step 4: STOP MACRO
   
3. Click OK
4. Click CLOSE

On your CUSTOMERS form, do the following:

1. EDIT | MACROS/EVENTS and create a macro called STARTUP
2. Write the macro:
   Step 1: SKIP (Num: if @var(34) = -1 then 0 else 3
   Step 2: ASSIGN (Value: @today, Col: LAST_ORDER_DATE)
   Step 3: RUN EXTERNAL (Command: ORDERS.VCE)
   Step 4: STOP MACRO
   Step 5: RETURN FROM MACRO
3. Click EVENTS
4. Select STARTUP in the "On Startup" dropdown
5. Click OK
6. Click CLOSE

Then, back on your ORDERS form, do the following:

1. EDIT | MACROS/EVENTS and create a macro called STARTUP
2. Write the macro:
   Step 1: SKIP (Num: if @var(34) = -1 then 0 else 2
   Step 2: ...some sort of post-processing on the ORDERS form
   Step 3: ASSIGN (Value: 0, Col: @var(34))
   Step 4: RETURN FROM MACRO
3. Click EVENTS
4. Select STARTUP in the "On Startup" dropdown
5. Click OK
6. Click CLOSE

When the SAVE_ORDER macro is run on the ORDERS form, it will first do the pre-processing. It will then set a flag (@var(34)) which lets the STARTUP macro in CUSTOMERS know that it needs to do some processing and return back to the ORDERS form. It then launches the CUSTOMERS form.

When the CUSTOMERS form is started, it will run it's STARTUP macro. This will set ORDER_DATE and re-launch the ORDERS form. Note that @var(34) is still set to the flag value.

When the ORDERS form is re-started, it will run it's STARTUP macro. This will do the post processing and then clear the flag. It's important to clear the flag, otherwise, next time the ORDERS form is started, it will think it was being launched from the CUSTOMERS form.

For clarity, the description of the STARTUP macro on the CUSTOMERS form didn't include code to position to the "right" record. You may have to do this...depending on how you got to the ORDERS form originally. You can use a JUMP instead of a RUN EXTERNAL in the SAVE_ORDER macro if need be. The STARTUP macro of the ORDERS form doesn't have to have to be positioned back to the record it was on because when a Visual CE form opens, it is always positioned on the record it was on when the form was last closed.