Some considerations on the Defined by clause in For each or Data Provider

Unofficial Content
This documentation is valid for:

There may be a case in which there are several minimum extended tables containing the For Each command (X Evolution 2) 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.