Find FormulaOfficial Content

Find is an Aggregate Formula.


Find ( <aggregateExpression>, <aggregateCondition>, <defaultValue>) [ if <triggeringCondition> ];



Is the expression whose resultant value will be returned according to the first record found that matches the <aggregateCondition>. It may contain attributes (even formula attributes), constants and variables (user variables are allowed only in inline formulas).


Is a combination of a search condition with a Data Selector invocation. Both parts are optional:

[<SearchCondition>] [USING <DataSelector> '(<Parameter>1, <Parameter>2, <Parameter>n ')']]


Is the condition that records must verify to be considered in the aggregation. It may contain attributes, constants and variables (user variables are allowed only in inline formulas, GeneXus standard variables in global formulas and inline formulas). 


Is the returned value when no records match the <aggregateCondition>. It is optional and only constant values are accepted (if the formula is being used with Date attributes, see this article too).


Is the condition that determines if the formula must be triggered or not. It is optional. The only attributes allowed are those belonging to the contextual table (that the formula attribute would belong to if it were stored) and its extended.



    FlightInstanceCurrencyValue = Find(CurrencyValue, CurrencyId = "Dollar" and CurrencyDate = FlightInstanceDate, 0) if FlightId=1;


The above example shows a defined Find global formula.

The following image shows the FlightInstance transaction being edited (using the GeneXus transaction editor) and the FlightInstanceCurrencyValue global formula attribute being edited (using the GeneXus formula editor):

Find Formula

FLIGHTINSTANCE is the contextual table of the FlightInstanceCurrencyValue global formula attribute.

CURRENCYVALUE is the table navigated by the Find formula in order to make the calculation.

The find calculation only triggers for the FlightId = 1. 

The tables involved in the formula definition don't store common attributes, so GeneXus will not automatically apply any additional filter (only the defined <aggregateCondition> will be considered to find the searched record).  Thus, the first record found in the CURRENCYVALUE table that fulfills the <aggregateCondition> will be selected, and the corresponding defined expression will be returned (in this example: a CurrencyValue is returned by the formula).

Note that in this example the defined <aggregateCondition> contains a filter by the primary key of the navigated table, so the navigated table may have a unique record that fulfills the <aggregateCondition>. This is the the most common use of Find formulas, meaning that we usually define Find formulas to search for a certain value, filtering by the primary key of the navigated table (so that a unique record will fulfill the <aggregateCondition>). When filtering by secondary attributes or foreign key attributes, since many records may fulfill the <aggregateCondition>, the first record found that fulfills the <aggregateCondition> will be selected.