Retrieves a dataset from the database. When used in Procedure objects, besides reading the database, it can also be used to update it.
For each [<BaseTrn1>|<BaseTrn.Level1>,...,<BaseTrnN>|<BaseTrn.LevelN>]
[SKIP <NumericExpression> COUNT <NumericExpression>]
[order <Att1>,...,<AttN> [when <Condition>]]...
[order none [when <Condition>]]...
[where <Condition> [when <Constraint>]]...
[where [not] <Att> in <DataSelectorName>([<parm1>,...,<parmN>])]...
View Syntax conventions
BaseTrn1|BaseTrn.Level1, ..., BaseTrnN|BaseTrn.LevelN
Is a Transaction or Transaction.Level name (or several separated by commas) to be used as the base table for the For Each navigation. See more on Base Transaction clause.
Determines the number of records omitted from the output. Skip takes positive values.
Specifies the number of records in each block.
Determines the number of records that will go to the output. If Count takes the value 0 or less, it means no limit.
Allows indicating the order in which the query will be returned. As the syntax indicates, you can write many conditional order clauses.
It is a list of attributes separated by a comma. Each mentioned attribute must be a knowledge base attribute that may be written with or without round brackets. When an order attribute appears between round brackets, it indicates a descending order for this attribute.
Specifies when the condition for data retrieval or the order of data displayed will be used.
Specifies any valid logical expression that conditions the data retrieval. It can be a compound condition, using 'and', 'or' and 'not' logical operators.
Order None clause
For cases in which you are not interested in a particular order and want it to remain undefined.
Allows ordering and filtering according to the criteria set out in the Data selector specified by its name DataSelectorName.
Is the name of the Data Selector.
parm1, …, parmN
Are variables defined in the called object or attributes.
Returns only those records where the set of values of the referred attributes is unique.
Allows indicating a list of conditions that must be matched by the data in order to be processed within the For each (inside the MainCode). The conditions are boolean expressions, simple ones or very complex nested ones.
Is a condition that specifies when the where clause will apply.
It is an attribute that must belong to the extended table of the Data Selector base table.
Available in Procedures (when updating/deleting). It indicates the number of records that will constitute a database block, when you are updating or deleting, in order to reduce the number of roundtrips to the DBMS.
It is the list of commands.
Available in Procedures, when inside MainCode certain attribute is updated (overwritten) and there exists a unique index for it. When you intend to overwrite the record with a previously existing value, this code will be executed instead of MainCode for that record. See more on When duplicate clause.
When there is no data meeting the filter conditions, the MainCode commands will not be executed; the CodeWhenNone commands will be executed instead. See more on When None Clause.
: Even though the Defined By Clause
from previous versions of GeneXus (located between ConditionList
in syntax) is allowed, it becomes meaningless against the Base Transaction clause
Throughout any application, you may need to retrieve information from the database in a procedural way.
For this, GeneXus offers the For Each command to be used to get a dataset from the database. When used in Procedures, besides reading the database, it can also be used in order to update it.
You can define For each commands in:
Within a For each command you can define the information you want to read (also write if inside a Procedure object).
Consider the following Transaction objects:
Suppose you want to display some information about the customers. To do so, you can define the following Procedure Source:
For each Customer //Customer is the name of the Transaction. So, Customer is the Base Trn of the For each.
"Customers" is the name of the following printBlock defined in the Procedure Layout:
With this For each command (which contains a Print command to show customers data) you are indicating you want to query the Customer table as the Base table and its Extended table to display the code, name and country of each customer in the database.
Every time there is a For each command, you are specifying that you want to get some information from the database and for each record retrieved you want to do something with the associated information (for example: print it).
So, all for each command by default will navigate one table, which is known as the For each Base table. It is the table associated to the Base Transaction. A special case arises when using more than one base trn.
In the example above, the For each Base table to be navigated is CUSTOMER. And for each navigated customer, his/her country is obtained from the COUNTRY table.
For 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).
Although you can specify a Base Transaction starting point for the search (it is strongly recommended to do so), it is not mandatory:
- When a base Base Transaction is specified, the Base table is directly determined. All attributes mentioned within the For each command code (including the attributes mentioned within the invoked print blocks), will have to belong to the corresponding extended table. If not, warnings will be displayed for each attribute not reachable from the Base table through the Extended table ("Att is not instantiated in group...").
- When not: considering the attributes defined inside the For each (including those mentioned within the invoked print blocks), GeneXus will find out how to retrieve them. How? Finding 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. You 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.
Specifying the Base Transaction, however, it is easier for GeneXus to understand what you want (and make it faster), and for you to be sure you have been clear and on control.
The boolean expressions in the where clause constraint the obtained dataset. However, there is a great difference between a full scan table access and an index-optimized random access.
By applying some rules of logic, GeneXus determines whether the order clause (or the primary key order) is compatible with the where boolean expression(s), optimizing the access to the table by using indexes and not doing a full table scan.
This is obviously the desired behavior whenever possible.
See Conditional Orders and Filters for further information.
This command can be used when developing applications for Native Mobile Generators or Angular but with some considerations. It can only be used in Work With object and Panel object and only in the System Native Mobile Applications Events, this means Start, Refresh and Load events.
Nested For Each commands
Introduction to For each command to access the database
More on nested For eachs. Cases and navigation
Syntax of the For each command