In a previous blog post, you learned how to build sophisticated and complex search criteria by applying formulas in an ORDA query. To further provide you with complete and powerful development tools, a new ORDA method available: orderByFormula(). With the help of formulas, you can now order an entity selection using complex criteria in a project method or 4D expression.
HDI: Examples to order an entity selection with a formula
The order criteria is evaluated when the formula is given as a string or a Formula object to the orderByFormula() method.
Order with a formula as a String
Here we have a dataClass, Companies, and we order all of the companies based on their profits (profits = revenue – costs).
Note that the entity is accessed via the This command.
C_OBJECT($companies;$orderedCompanies)
$companies:=ds.Companies.all()
$orderedCompanies:=$companies.orderByFormula ("This.revenue - This.costs")
Order with a formula object
Here we order the companies using the same criteria, but instead of a string, we’re using a Formula object and we’re sorting by descending order.
C_OBJECT($companies;$orderedCompanies;$formula)
$formula:= Formula(This.revenue - This.costs)
$companies:=ds.Companies.all()
$orderedCompanies:=$companies.orderByFormula ($formula;dk descending)
Notice that using a Formula object provides some advantages (such as autocompletion, colors in the code editor, etc.) over using a formula as a string.
The orderByFormula() method has even more options. Check out the documentation and download the HDI above for details.
about Ordering an entity selection
You can now insert an order by statement inside the query() member method itself. This is a huge benefit when working in client/server mode because you can combine the query and the ordering action in the same request, reducing the number of requests sent to the server and your network traffic.
C_OBJECT($companies)
$companies:=ds.Companies.query("revenue> :1 order by costs desc";5000)