Software-Hotbuy www.software-hotbuy.com

Data Macro Aliases and the Where Condition Demystified

Whenever I debug a data macro, one of the most common mistakes I find is misusing aliases, especially in the Where Condition of a ForEachRecord or LookupRecord data block.  This blog post is an attempt to demystify this area.  Hopefully, by the time you are done reading this you will have a firm grasp of how to use aliases in data macros.

Key points

1. What is the default data context?

2. When does the default data context change?

3. What else is in the data context?

4. What is the default data context in a named data macro?

5. Using an alias in the Where Condition of a ForEachRecord or LookupRecord.

6. Using an alias to reference a data context outside of the current default data context.

What is the default data context?

In a data macro, the "default data context" is what you are using when you refer to a field without using a table name qualifier.  

Office 2007 professional and Office 2007 Ultimate are so powerfull.When a BeforeChange, BeforeDelete, AfterInsert, AfterUpdate, or AfterDelete event data macro is triggered, a new default data context is created, which can be used inside that block.

This new context is what I like to refer to as the “incoming default data context” or, for brevity, the “incoming record.”  Note that in event data macros, the incoming default data context is always read-only.  This is a change in behavior from the Beta 2 release. Windows 7 is also my love!

When does the default data context change?

The data context stack is expanded and the default data context changes whenever you enter a data block (ForEachRecord, LookupRecord, EditRecord, and CreateRecord).

For example, if you are in a data macro in Table1, and you call “ForEachRecord Table2”, and then refer to a field without a table name qualifier, you are referring to a field in Table2. QuickBooks 2010 is so Helpful!

What else is in the data context?

If you are in Table1.AfterUpdate, then you call “ForEachRecord Table2”, and inside that loop you call “LookupRecord Table3”, the data context at this innermost point contains a record for each of the 3 tables, and can be accessed using the following table name qualifiers:

1. [Table3] contains the record (if any) found by LookupRecord.

2. [Table2] contains the current records in the ForEachRecord loop.

3. [Table1] contains the record that triggered the AfterUpdate event.

Acrobat 9 is so useful!

4. [Old] contains the record in Table1 from just before the data change that triggered the AfterUpdate event was entered.

At the innermost data block (the LookupRecord on Table3), all of these are available from the data context stack with [Table3] being the current default data context.  So, inside the LookupRecord data block, the data context stack looks like this:

When you exit a data block, the data context for that block is removed from the data context stack.  So, if the LookupRecord on Table3 is finished, but you are still inside the ForEachRecord on Table2, then [Table2] becomes the current default data context, and the data context stack looks like this:

What is the default data context in a named data macro?

If you call a named data macro from an event data macro, the entire data context is carried over.  

Dreamweaver CS4 is very easy-to-use! So, in the above example, if you called RunDataMacro from the innermost data block, all 4 items would still be available inside the named data macro.

Note that there is no data context if you call a named data macro from a user interface macro (UI macro).  In this case, any attempts to refer to a field will fail until you enter a data block, because there is no way to know which record you are referring to.

TIP:

If you define parameters for data that is consumed by your named data macro, then you can also call the named data macro from a UI macro.  Then, if you are viewing a form you can pass the ID of the record being viewed (or any other field in it) to the data macros and then use LookupRecord on the ID to get the same record and perform some processing, such as deleting the record. Note however, that parameter values in data macros are read-only and cannot be edited.

Here is a screenshot illustrating this:

Using an alias in the Where Condition of a ForEachRecord or LookupRecord.

In most cases, you will not need to define an alias in a ForEachRecord or LookupRecord and you can just leave it blank, because the alias defaults to the same name as the table or query you are referring to on the first line.

IMPORTANT: The Where Condition is inside the data block.  Therefore, the default data context in the Where Condition argument is whatever table or query the ForEachRecord or LookupRecord refers to.

Suppose you are in Table1’s BeforeChange event, and you want to prevent the change if it would create a duplicate value in the [Text1] field.  The easiest way to do it is to create a LookupRecord on Table1 to find out if there is another [Text1] field in the table with the same data as the incoming record, and raise an error if a duplicate is found.

The solution is to define an alias for the LookupRecord block, then use that alias as the table name qualifier for fields in the Where Condition expression, and call the RaiseError action if any record is returned by LookupRecord to prevent the entry of this data.

Here is a screen shot illustrating this:

Using an alias to reference a data context outside of the current default data context.

Let’s say you want to iterate over all the records in Table1 and look for a match in Table2.Text1, and if found, set Table1.Text2 = "Match found in Table2 at row ID=X".  To do this, I’ll define an alias at each step.  Then, to edit the record from the ForEachRecord instead of the LookupRecord (which would be the default data context for the EditRecord if no alias was specified), I’ll use the alias I defined for the ForEachRecord (T1) in the Alias argument of the EditRecord block.  This is how to edit the record from the ForEachRecord loop and instead of the record found by the LookupRecord.

Here is a screen shot illustrating this:

TIP:

The other benefit of always defining your aliases and then using them in the expressions inside the data block is that if you rename your table, you do not need to update all of your expressions.

That’s it!  I hope this clears up any questions you may have had about using aliases or the where condition in data macros.