Recents
Base Transaction in For Each commandOfficial Content

An optional clause in the For Each command allows us to define the Transaction (or Transaction level) that we want to use as Base Table for the For Each.

See Base Transaction clause  for the general aspects.

Examples#

For Each Invoice
    Print CustomerName, InvoiceDate
Endfor

For Each Invoice.Line
    Print ProductName, InvoiceLineQuantity, InvoiceLinePrice, InvoiceLineTotal
Endfor

We call the mentioned transaction (or transaction level) the 'base TRN' of the For Each.
When we define the base TRN of the For Each, GeneXus directly infers its Base Table.

The base TRN also applies to Data Providers and grids. See Base Transaction in Data Providers for more details.

Scenarios#

Since this functionality implies improved expressive capacity of the For Each, some navigations that are hard to express in the traditional way may be declared in a more intuitive manner. Below are some scenarios where the declaration of the navigation results much more intuitive when using the base TRN clause of the For Each.

I. For each over M-N relations#

Let's taking into account the following transaction structures:

User
{
   UserId*
   UserName
}

Role
{
   RoleId*
   RoleName
   User
    {
      UserId*
      UserName
    }
}

Physically, 3 tables are created (User and Role, related through a third table – RoleUser – whose primary key consists of the PK of the first two tables, and has no secondary attributes).

We want to display, for each role, the related users. So, we have defined the following nested For Eachs:

For Each
   Print RoleName
   For Each
      Print UserName
   Endfor
Endfor

Although apparently, GeneXus will determine for the internal For Each, the User table as its base table (and the result seems to be a Cartesian Product between Role and User), GeneXus detects that there is a table that relates Role (the main For each base table) and User, so, GeneXus choose RoleUser as the base table of the internal For Each (and the result will be the Join we wanted).

Now, lets suppose we want to define a Cartesian Product between Role and User. We can achieve this, defining the base TRN for the internal For Each, like the following code shows:

For Each
   Print RoleName
   For Each User
      Print UserName
   Endfor
Endfor

 

II. Ease the expression for navigations over different tables#

Sometimes you need to solve queries which imply the navigation over different tables, and are difficult to express using n For Eachs. It may also happen that without the base TRN clause these queries cannot be solved in a single SQL sentence. 

In such circumstances, it is possible to add more than one base TRN in a for each command, as the following example shows.

Let's taking into account the following transaction structures and code:

User
{
   UserId*
   UserName
   UserType ("Common","Administrator", etc.)
}

Program
{
   ProgramId*
   ProgramName
   User
    {
      UserId*
      UserName
      UserType 
    }
}

For Each User, Program
   where UserType = "Administrator"
   Print UserName, ProgramName
Endfor

 

In this case a cartesian product is done, listing all the possible combinations of users and programs (for the "administrator" user). Note that in the same line is printed the UserName and the ProgramName, for each combination.

See Multiple Base Transactions in a For Each command for more specific examples of this scenario.

How is the base table determined#

If one base TRN is declared in the For Each, the table associated to that base TRN is considered as the base table, and the attributes inside the body of the For Each, conditions, orders, etc. have to be included in the extended table.

If more than one base TRN is declared, if it's possible, a JOIN is made between the tables associated to these trns. Otherwise a cartesian product is done. The attributes of the For Each have to be in the extended table of any of these tables.

 #

Advantages of using base TRN in the For Each#

In addition to the scenarios presented above, the advantages of using the base TRN in the For each include:

• Improved capacity to understand existing KBs. If the For Each has base TRN then a developer facing a new KB will not have to see the navigation to determine the table on which the iteration is taking place.

• Enhanced expressive capacity of the For Each and more simplicity.

• Improved specification time.
Time for specification is improved because it isn't needed to do the calculation of the For Each’s base table.

• Reference in the KB between base TRNs and their For Each.
The KB stores the reference between the TRN and the For Eachs that use it as base TRN, so, if a TRN changes, we can directly see the For Eachs that may be affected by such change.

 #

Availability#

As of GeneXus X Evolution 3.

 #

Compatibility Notes#

In order to avoid ambiguity in the For Each Order clause, the Order clause is not optional any more.
In case you program the following, you will get an error: error: Program 'AttributeX' does not exist:

For Each AttributeX

Endfor

You need to change it to:

For Each Order AttributeX

Endfor

Kbs which are converted from previous versions of GeneXus#

In case of KBs which are converted to GeneXus X Evolution 3, or xpz files which where generated in a previous version, an automatic conversion is performed when the object is opened, or specified.

Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.