This is a GeneXus procedural language command used to get a dataset from the database. This command can be used in the Procedure Source, Events of Web Panels, Subroutines. When used in procedures, besides reading the database, it can also be used in order to update it.
Within a For Each command we define the information we want to read (also write if inside a Procedure object) by specifying the name of the attributes we want to use. We don’t need to define which tables they must be obtained from, nor the indexes that must be used to access them. We define which attributes are needed and GeneXus will find out how to retrieve them.
We want to display some information about our customers, so we design the following Procedure:
For each
Print Customers
Endfor
"Customers" is the name of the following printBlock defined in the Procedure Layout:
It can be easily inferred that with this for each command (which contains a Print command sentence to show customers data) we are trying to display the code, name and country of each customer in the database.
How does GeneXus infer what to do if we've only named the attributes we want to display?
Every time there is a for each command, we are specifying that we want to get some information from the database. GeneXus knows that with a for each command we want to navigate one table, and for each record of that table we want to do something with the associated information (for example: print it).
So, all for each commands will navigate one physical table, which is known as "the for each base table"
In the example above, the for each base table determined by GeneXus to be navigated is CUSTOMER. And for each customer, his/her country is obtained from the COUNTRY table.
A related and important concept that you need to learn and consider when defining a For Each is: the Extended Table of a base table.
After learning the Extended Table concept, you can understand that within any for each command GeneXus will navigate a base table, but can access all tables that belong to the extended table of that base table in order to get the necessary information. That is why in the example above the base table is CUSTOMER, but for each customer GeneXus can get its related country, because the COUNTRY table is included in the CUSTOMER's extended table.
Finally, through the attributes mentioned within the for each command code (and the attributes mentioned within the invoked print blocks), GeneXus is able to find the least extended table that has all of them, and the base table of that extended table is the one chosen by GeneXus as the for each base table.
For Each
[{[order] order_attributesi [when condi]}... | [order none] [when condx]]
[using DataSelectorName([ [parm1 [,parm2 [, ...] ])]
[{where {conditioni when condi} |
{attribute IN DataSelectorName([ [parm1 [,parm2 [, ...] ]} }...]
[defined by defined_attributes]
[Blocking NumericExpression]
[option distinct ]
code1
[when duplicate code2]
[when none code3]
EndFor
Allows to indicate the order in which the query will be returned. As the syntax indicates, you can write many conditional order clauses.
Using clause: Allows filtering according to the criteria set out in the Data Selector object DataSelectorName.
conditioni : Allows to indicate a boolean condition that must be matched by the data to be processed within the for each. It can be a compound condition, using 'and', 'or' and 'not' logical operators. Those attributes appearing in the boolean condition may be either from the for each base table or from the extended table of the for each base table.1
As shown in the syntax, you can specify n successive where clauses (each of them with a filter condition) for the same for each:
where cond1
where cond2
...
where condn
The occurrence of n where clauses is equivalent to the occurrence of only one where clause, with the boolean conjunction of the conditions. This means:
where cond1 and cond2 and … and condn
In client/server platforms, the when clause of each where clause is first evaluated, and if its condition is met, the filter specified in the where clause will be applied.
attribute IN DataSelectorName([parm1 [,parm2 [, ...]]): The Data Selector object query will return a collection of values corresponding to the same definition as the attribute which precedes the IN operator. See Data Selectors in For Each command.
1 In some cases, attributes that do not belong to the extended table can be included. See Filters As Hint for more detailes.
Defined by clause: There may be a case in which there are several minimum extended tables containing the for each attributes. Facing this ambiguity, GeneXus selects the "first" of these minimum extended tables (and the base table of that extended table is the one chosen by GeneXus as the for each base table). To solve this type of ambiguity, the for each command has the defined by clause, which allows naming attributes of the desired base table to be navigated. So, the attributes mentioned in the defined by clause are only used to provide more information for determining the for each base table.
The defined by clause must make reference to at least one attribute from the desired base table. We recommend using secondary attributes from the base table to be browsed in the defined by clause, since as we already know, secondary attributes can only be in a model table to completely avoid any possible ambiguity. Nevertheless, this is not mandatory; that is to say, you can name primary attributes in the defined by clause once you know that there is no ambiguity in the base table selection.
A common error is to believe that when a for each has the define by clause, the for each base table is exclusively determined from those attributes mentioned in the defined by clause. The attributes mentioned in this clause do not determine the for each base table by themselves. The attributes mentioned in the defined by clause belong to a specific physical table (or to several physical tables), so those tables will be candidates for becoming the for each base. But all the remaining for each attributes (in addition to the defined by clause attributes) must be included in the extended table of a candidate table, so that the candidate table could be selected as the for each base table. This means that if the rest of the for each attributes (mentioned in order clauses, where clauses, the for each body, print blocks, etc.) are not contained in the extended table of a candidate table, this candidate table will be discarded as the for each base table. Thus, if there isn't a table whose navigation gives access to all the attributes mentioned in the for each (using the extended table concept), at specification time, the navigation list will show an error explaining that it is not possible to determine a for each base table.
Summing up, the defined by clause appears to solve some problems of ambiguity in the determination of the base table (when there are several minimum extended tables containing the for each attributes) or when we want another base table, different from the one that would be determined by the attributes appearing in the rest of the for each. It is also used to improve the specification time of complex reports; however, an indiscriminate use is not recommended. The disadvantage of using this clause when it isn’t necessary is that it ties the code a little more to the design of the tables. For example, if you have not created a COUNTRY table, and as a secondary attribute you have the country each customer belongs to, if you want to list the customers and their country, you could do either of the following and have the same result:
For each
print Customers
EndFor
...where "Customers" is a printblock with the CustomerId, CustomerName and CountryName attributes.
For each
defined by CountryName
print Customers
EndFor
If you later decide to create the COUNTRY table and have CountryId as Foreign Key (FK) in CUSTOMER, while the first For Each will still be valid and will continue to do what you want it to do, the second For Each will no longer work, as there is no attribute of the base table in the defined by clause. So, in the second For Each, the defined by clause was included to improve the specification time (because at specification time, GeneXus first takes into account the attributes named in the defined by, and analyses in which tables the mentioned attributes are physically located, to consider those tables to be candidates as the for each base table); but this code was valid until CountryName was moved to another table.
Option Distinct: Returns only those records where the set of values of the referred attributes is unique.
Blocking clause: It applies when you are using the for each not only to query but to update records from the extended table or to delete records from the base table. When throughput is an issue and you need to update or delete a large number of records, reducing the number of roundtrips to the DBMS may be the answer.
When duplicate: This clause only makes sense in procedures (because it has to do with updates); it is analyzed below. This clause will be executed if, within the body of the For Each (code1), you try to update an attribute that is a candidate key (it has a unique index) and a record with that value already exists. GeneXus uses the unique index to ensure the uniqueness of that candidate key and in case it finds duplicates, if this clause is programmed in the For Each command, it will execute its code: code2. If the clause is not included, and you try to update an attribute that is a candidate key and a record with that value already exists, no code will be executed.
When none: When there is no data meeting the filter conditions, the code1 commands will not be executed; the code3 code block commands will be executed instead.
Note: Both for when duplicate and for when none: if a For Each command is included in them, no joins or filters are inferred with respect to the For Each containing the when none | when duplicate. They are considered as independent navigations (that of code1, code2 and code3).
Boolean expressions in the where clause constrain the dataset that is obtained. However, more than that is meant here. There is a great difference between a full scan table access and an index-optimized random access. If applying some rules of logic, GeneXus determines that the order clause (or the primary key order) is compatible with the where boolean expression(s), it optimizes the access to the table using indices and not doing a full table scan. This is obviously the desired behavior whenever possible.
For Each command can be used when developing applications for Native Mobile Generator but with some considerations. It can only be used in Work With pattern and Work With object and Panel object and only in the System Native Mobile Applications Events, this means Start, Refresh and Load events.
Data Base Update - For Each, Delete, New
Introduction to procedures, lists and For Each command
Simple For Each command and nested For Each command to list related info
Nested For Each command to list grouped data