Is it possible to make a SQL-calculated Decimal field sortable

Support Developer
1

Dear all,

I need some help with the following problem:

In an aggregated model, I want to calculate a percentage, like for example:

Text     |    Number | percent |
--------------------------------
Black    |  1.000,00 |  50,00% |
Green    |    250,00 |  12,50% |
[...]                     ^^^

When I use a Function field for “percent”, and calculate it in Python, it works fine.
But then the column is not sortable.

I need the column to be sortable. My idea was to do the calculation in the query.
So I tried to define the formula in the query with this variable definition:

        percent = fields.Float("percent", (3, 2))

and this query definition:

        query = x.select(
            [...]
            Count(
                Literal('*')).as_('number'),
            Mul(
                Div(
                    Count(Literal('*')), total), 100).as_('percent'),
            where=where,
            group_by=[...])
        return query

My problem:
the result of the “percent” column from the query is of type decimal.Decimal, not float .
In the UI, the field shows “#Error”.

When I convert it to float using a Function field and a temporary variable, I get the correct values (so the query works fine), but then the column, as in the beginning, is no longer sortable.

My Question:
Is it possible to store the value of a sql calculated “Decimal” column directly into a field.Float variable in an aggregated model, without using a Function field?
Or is there a possibility to make a Function field sortable in a tree view?

Thanks for any info.

2

I think you may be looking for this: Fields — Tryton server

3

I guess total is a Numeric column. So if you want to have a Float you must CAST it. You can use field.sql_cast() to build for you the expression.

To make a Function field sortable, you must define a ordering function.