Can't sort or search on calculated fields

User e05b1833aa

09-02-2011 13:23:05


I am aware that the Calculated Fields are in experimental stage. However, it seems that right now it is not possible to sort on this type of column, nor is it possible to use it in a query, which to me seems to be basic functionality that should be field-independent. It wasn't mentioned as a restriction in the release notes as fa as I can tell.


ChemAxon fa971619eb

09-02-2011 15:56:39

Calculated fields results are not stored in the database, only calculated in memory as needed. So they can't be used for sort or search as these operations table place in the database.

There are two approaches that can be used if sort/search is needed.

1. materialise the calc field results into the database using the 'Convert type' function for a field. This creates a standard field using the results from the calculation and do can be searched or sorted. However the results in the DB will not be dynamically udpated when data changes or new rows are added.

2. uses triggers to perform the calculation in the database. Here is a guide: