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.
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.
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.
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
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.
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.
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.
As of GeneXus X Evolution 3.
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
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.