For Each command

Official Content
This documentation is valid for:

Retrieves a dataset from the database. When used in Procedure objects, besides reading the database, it can also be used to update it.

Syntax

For each [<BaseTrn1>|<BaseTrn.Level1>,...,<BaseTrnN>|<BaseTrn.LevelN>]
         [SKIP <NumericExpression> COUNT <NumericExpression>]
         [order <Att1>,...,<AttN> [when <Condition>]]...
         [order none [when <Condition>]]...
         [using <DataSelectorName>([<parm1>,...,<parmN>])]...
         [unique <Att1>,...,<AttN>]...
         [where <Condition> [when <Constraint>]]...
         [where [not] <Att> in <DataSelectorName>([<parm1>,...,<parmN>])]...
         [blocking <NumericExpression>]
                   <MainCode>
         [When duplicate
                   [<CodeWhenDuplicate>]]
         [When none
                   [<CodeWhenNone>]]
Endfor

View Syntax conventions

Where:

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.

SKIP
   
 Determines the number of records omitted from the output. Skip takes positive values.

NumericExpression
     S
pecifies the number of records in each block.

COUNT
     
Determines the number of records that will go to the output. If Count takes the value 0 or less, it means no limit. 

order
     Allows indicating the order in which the query will be returned. As the syntax indicates, you can write many conditional order clauses.

Att1,...,AttN
     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.

when
     Specifies when the condition for data retrieval or the order of data displayed will be used.

Condition
      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.

using
      Allows ordering and filtering according to the criteria set out in the Data selector specified by its name DataSelectorName. 

DataSelectorName
     Is the name of the Data Selector.

parm1, …, parmN
     Are variables defined in the called object or attributes.

Unique
      Returns only those records where the set of values of the referred attributes is unique.

where
       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. 

Constraint
   
 Is a condition that specifies when the where clause will apply. 

Att
     
It is an attribute that must belong to the extended table of the Data Selector base table.

blocking
       
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.   

MainCode
      
It is the list of commands.

CodeWhenDuplicate
     
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.

CodeWhenNone
     
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.

Note: Even though the Defined By Clause from previous versions of GeneXus (located between ConditionList and BlockingGroup in syntax) is allowed, it becomes meaningless against the Base Transaction clause.

Description

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).

Samples

Consider the following Transaction objects:

Customer
{
   CustomerId*
   CustomerName
   CustomerAddress
   CustomerPhone
   CustomerEmail
   CountryId
   CountryName   
}

Country
{
   CountryId*
   CountryName
}

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.
    Print Customers 
Endfor

"Customers" is the name of the following printBlock defined in the Procedure Layout:

PrintBlock

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).

Base table

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.

Customer-Country relation

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).

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. 

Optimizations

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.

Native Mobile and Angular applications

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.

See Also

Nested For Each commands

Videos

Start Video Introduction to For each command to access the database
Start Video More on nested For eachs. Cases and navigation
Start Video Syntax of the For each command