In GeneXus X formulas are much easier to define and use, and the generated SQL code is much better. Formulas using complex expressions that include other formulas and functions can be defined without restrictions. This will have a great impact in the way you create applications with GeneXus, as you will be able to define a great deal of existing procedural code as formulas, adding knowledge to the GeneXus KB. If you have a calculation inside a procedure, GeneXus does not actually 'know' anything about it. If you define it as a formula attribute you can do much more with it, like reuse it in many places more easily while generating better code.
In addition, those formulas now can also be defined directly in the source code as inline formulas.
Below follows a list of the main improvements that have been made to Formulas in GeneXus X.
Note: This document is aimed at users who are familiar with previous GeneXus versions and want to know how Formulas have changed in GeneXus X. If you are new to the topic, please look at this document for complete information.
In previous GeneXus versions, formulas were tricky to define because they had several restrictions. For instance, a vertical formula could not have a WHERE clause, an Aggregate/Select formula could not be defined over a formula attribute, and so on.
In GeneXus X we no longer have an internal difference between Aggregate/Select and Vertical formulas, they are just “formulas”. Their definitions can include expressions that involve other formula attributes, not only as the aggregate attribute, but also in their filtering condition.
Examples:
- Their definition can involve an expression with formulas:
InvoiceTotal = sum(InvoiceLineTotal) * (1 + InvoiceTax)
That is the same as defining it as follows:
InvoiceTotal = sum(ProductPrice * InvoiceLineQty) * (1 + InvoiceTax)
- Using the new average formula:
Average(InvoiceTotal, InvoiceTotal > 1000, 0)
- Their search condition can also refer to other formula attributes or Data Selectors!
&NumberActiveCustomers = Count(CustomerName, USING ActiveCustomers())
Formulas can aggregate more than one level. If a relationship exists between the extended table where the formula is referenced and the formula’s navigation table, GeneXus automatically takes into account that implicit condition:
Example:
Country Transaction
CountryId*
CountryTotalInvoice = sum(InvoiceTotal)
Customer Transaction
CustomerId*
CustomerTotalInvoice = sum(InvoiceTotal)
Even though both formulas are defined in the same way, the first one will sum all the Invoice totals for each country and the second one will sum the invoiced total by customer.
In other words, the formulas can be read as follows (the implicit condition is in bold):
CustomerTotalInvoice = Sum InvoiceTotal where Invoice.CustomerID = Customer.CustomerId.
CountryTotalInvoice = Sum InvoiceTotal where Invoice.CustomerId = Customer.CustomerId and Country.CountryId = Customer.CountryId.
Here you can see the expressive power of the new formulas.
You can define formulas directly inside For Each statements or Data Providers, and also outside the For Each, because in some situations you may need a special calculation that doesn't require a global formula (as an attribute).
For Each CustomerName
&CustomerInvoiceTotal = sum(InvoiceTotal)
Endfor
As mentioned above, implicit conditions are considered to evaluate the formula; they relate the For Each’s base table with the formula’s base table.
In the previous example, the CustomerId is considered as an implicit condition to retrieve the amount corresponding to each Customer.
Notes:
- This also applies to the Load Event in Web Panels with a base table (there is an implicit for each in those cases).
- Attributes mentioned in the formula definition do not participate in the For Each’s base table determination.
Wherever you need a calculation, just write down the formula. There are some situations where you need a general calculation outside the context of a For Each or Transaction, for instance, to know at any time the amount of customers, find a special value, etc.
Following the previous example, if you want the invoice total percentage per each customer, you can do as follows:
&TotalInvoiced = sum(InvoiceTotal) ----> Outside Formula
For Each CustomerName
&CustomerInvoiceTotal = sum(InvoiceTotal) / &TotalInvoiced ---> Inside Formula
Endfor
Note that the first formula cannot be written inside the For Each group because the result would fetch only the customer’s invoices instead of all invoices. This is the reason it was written ouside the For Each.
See Inline Formulas for more information.
Although inline formulas are very practical, some calculations have to be globally defined.
In those cases, try to define the formula expression directly in the attribute formula, instead of calling a procedure (udp) to make the calculation. This has many advantages:
- It’s easier to understand and maintain.
- Better code is generated, which leads to better performance.
- GeneXus has the knowledge to calculate the formula whenever the attribute is mentioned.
Once the formula is defined in the Transaction, that attribute can then be used in other objects as another Transaction’s attribute. It is evaluated considering the formula definition without taking into account the context information as it happens with inline formulas.
For Each groups can be ordered by any formula attributes; and it is possible to filter in a For Each by any formula (global or inline).
See Global Formulas for more information.
When you need a calculation, you can use a global formula (already defined in a Transaction) o define it inline (directly in the code). Previously, we mentioned the benefits and drawbacks of having the formulas at the knowledge base level, but there is another significant difference.
Let’s see an example:
Customer Transaction
CustomerId*
CustomerName
CustomerInvoicesAmount = Count(InvoiceId)
For Each InvoiceId CustomerName
&CustomerInvoicesAmount = Count(InvoiceId)
Endfor
For Each CustomerName
&CustomerInvoicesAmount = CustomerInvoicesAmount
Endfor
Note that Invoice is the base table in both cases.
The result will be different in each For Each statement even though they use the “same” formula. The first example retrieves the value “1” for all lines, because the For Each’s base table is the same as that of the formula. The evaluation depends on the For Each context which has two instantiated attributes: CustomerId and InvoiceId. The second example will retrieve the amount of invoices by customer because “CustomerInvoicesAmount” is considered as another secondary attribute whose value (evaluation) is the same wherever you reference it.
Almost all formula attributes are being evaluated on the server side.
That means that a formula is resolved within the SQL statement defined by the base table where the attribute formula is implied, instead of being evaluated on the client side by the program.
As a result, formulas can now be used in places that were not possible before, such as conditions, order clauses, combined with other formulas, etc. In the cases that were possible before, performance will be improved because most formulas are evaluated directly in the server.
Moreover, when the Transaction or For Each Group contain more than one formula attribute whose evaluation implies a similar SQL execution, all of them are combined in the same SQL statement, minimizing the access to the DBMS server.
When you run a Web Transaction that has formulas in the Form, whenever the user modifies the information required to recalculate that formulas, all of them are dynamically evaluated using Ajax regardless of its complexity.
The Average formula was added to the list of available aggregate formulas (sum, count, max, min, find).
It returns the average value of the expression considering all the records that match the conditions.
Its syntax is the same as that of the other formulas:
Average (<Expression>, < Condition>, <Default value>)
The only restriction is that <Expression> must evaluate as numeric.
- spc0142: The value of %1 will be the same for all rows. No relationship found between attribute %1 and attributes in its expression.
The message indicates that the value returned by the formula will be the same for all rows, as no relationship has been found between the base table defined by the formula and the base table where it is being referenced.
- spc0139: Formula %1 cannot be evaluated in server.
%1 is a formula that cannot be evaluated in the server by the DBMS. Therefore, it cannot be used in that part of the code.
For example the error is triggered when a formula attribute belongs to the For Each's Order Clause and it uses some function or expression that cannot be resolved by the DBMS.
- spc0149: Condition %1 found in %2 does not evaluate to Boolean.
The expression of the search condition does not evaluate to boolean in the formula definition.
- spc0146: Formula %1 (directly or indirectly) references itself.
- spc0147: Formula %1 references an invalid formula (%2).
Some changes that can be noticed when converting your KB to GeneXus X:
- Most formulas have been optimized and this can be noticed in the navigation diagram because now it is quite different.
Formulas that cannot be optimized for any reason (it calls a UDP, references a function that cannot be evaluated in the server, etc) are easy to detect because they are shown in the navigation diagram as in previous versions.
- “Vertical” formulas cannot be used in objects other than the Transaction where they were defined. This internal classification does not exist anymore and the formula can be used globally so this warning disappears:
spc0026: Formula ' InvoiceSubTotal ' cannot be evaluated in this program.
- Besides, some of the new specifications controls mentioned above could appears during the building process.