how calculate field total

240 views
Skip to first unread message

Alexandre Andrade

unread,
Jan 14, 2010, 9:51:57 AM1/14/10
to web2py
What is the best solution to automatically calculate a total field?

db.define_table ('order_detail',
              Field('product_name'),
              Field('qtd', 'integer'),
              Field('price', 'integer'),
              Field('total', 'integer', writable=False)
)

Where  total = qtd * price

requires and a custom validator? how to do?
onvalidation on form? how to do?



--
Atenciosamente

--
=========================
Alexandre Andrade
Hipercenter.com

mdipierro

unread,
Jan 14, 2010, 10:12:46 AM1/14/10
to web2py-users
with 1.74.5 and later you only need

db.order_detail.total.compute=lambda r: r['qtd']*r['price']

On Jan 14, 8:51 am, Alexandre Andrade <alexandrema...@gmail.com>
wrote:

Alexandre Andrade

unread,
Jan 14, 2010, 10:29:35 AM1/14/10
to web...@googlegroups.com
Thanks, very nice.



2010/1/14 mdipierro <mdip...@cs.depaul.edu>
--
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to web2py+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/web2py?hl=en.



Alexandre Andrade

unread,
Jan 14, 2010, 10:47:46 AM1/14/10
to web...@googlegroups.com
db.order_detail.total.compute=lambda r: r['qtd']*r['price']

don't work on updates


2010/1/14 mdipierro <mdip...@cs.depaul.edu>
--
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to web2py+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/web2py?hl=en.



mdipierro

unread,
Jan 14, 2010, 10:52:47 AM1/14/10
to web2py-users
Make sure Field(...,default=None, update=None)

On Jan 14, 9:47 am, Alexandre Andrade <alexandrema...@gmail.com>
wrote:


> db.order_detail.total.compute=lambda r: r['qtd']*r['price']
>
> don't work on updates
>

> 2010/1/14 mdipierro <mdipie...@cs.depaul.edu>

> > web2py+un...@googlegroups.com<web2py%2Bunsu...@googlegroups.com>

Alexandre Andrade

unread,
Jan 14, 2010, 11:04:18 AM1/14/10
to web...@googlegroups.com
still don't work. The value before still mantains.

Maybe I have to drop the column and create it again?


2010/1/14 mdipierro <mdip...@cs.depaul.edu>
To unsubscribe from this group, send email to web2py+un...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/web2py?hl=en.



mdipierro

unread,
Jan 14, 2010, 1:09:06 PM1/14/10
to web2py-users
It would not help.

I will test it again later today and, if it is a bug, release a fix by
tonight.

Massimo


On Jan 14, 10:04 am, Alexandre Andrade <alexandrema...@gmail.com>
wrote:


> still don't work. The value before still mantains.
>
> Maybe I have to drop the column and create it again?
>

> 2010/1/14 mdipierro <mdipie...@cs.depaul.edu>

> > <web2py%2Bunsu...@googlegroups.com<web2py%252Buns...@googlegroups.com>

mdipierro

unread,
Jan 14, 2010, 1:10:15 PM1/14/10
to web2py-users
Can you also try set readable=False?

Alexandre Andrade

unread,
Jan 14, 2010, 1:57:53 PM1/14/10
to web...@googlegroups.com
It don't show at form, without readable=False.

I set readable=False, but it don't show the updated value.

2010/1/14 mdipierro <mdip...@cs.depaul.edu>
To unsubscribe from this group, send email to web2py+un...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/web2py?hl=en.



Alexandre Andrade

unread,
Jan 14, 2010, 1:59:33 PM1/14/10
to web...@googlegroups.com
 Field('valor_total','double', default=None, update=None, readable=False),

db.cronograma_desembolso.valor_total.compute=lambda r: r['valor_custeio_concedente']+ r['valor_investimento_concedente'] + r['valor_custeio_contrapartida']+ r['valor_investimento_contrapartida']

(one line above)

2010/1/14 Alexandre Andrade <alexand...@gmail.com>

mdipierro

unread,
Jan 14, 2010, 2:34:57 PM1/14/10
to web2py-users
I see the problem. I can find a way around it but I feel it would be
wrong.

When you submit a form, what you see is what should go in the DB, even
if some fields are readonly.

If you have quantity, price and total you should not display the same
total that is in DB before submission as readonly, because has you
change quantity or price the total should change but it does not. To a
user this is confusing. To the user it looks like they submit a form
with the wrong total (the previous one, not the one reflacing the
change in the other fields).

You should not use compute.

You should use JS

<script>
jQuery("input[name='quantity'],input[name='price']").change(function()
{
jQuery("input[name='total']").val(parseFloat(jQuery("input
[name='price']").val())*parseInt(jQuery("input[name='quantity']"));
});
</script>

when use a custom validator to make sure the user did not mess up this
field somehow.
there are many options.

On Jan 14, 12:57 pm, Alexandre Andrade <alexandrema...@gmail.com>
wrote:


> It don't show at form, without readable=False.
>
> I set readable=False, but it don't show the updated value.
>

> 2010/1/14 mdipierro <mdipie...@cs.depaul.edu>

> > <web2py%2Bunsu...@googlegroups.com<web2py%252Buns...@googlegroups.com>

Alexandre Andrade

unread,
Jan 14, 2010, 3:18:40 PM1/14/10
to web...@googlegroups.com
1. The total is not showed when editing. I don't need in this moment. When I set .compute, its hide (like writable=False and readable=False) the field in the form.
2. the main problem is .compute is not inserting/updating the field on db, and neither acting as represent when showing



2010/1/14 mdipierro <mdip...@cs.depaul.edu>
To unsubscribe from this group, send email to web2py+un...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/web2py?hl=en.



mdipierro

unread,
Jan 14, 2010, 3:23:49 PM1/14/10
to web2py-users
Try Field('total','double') (compute=None, writable=True,
readable=True) and

jQuery("input[name='quantity'],input[name='price']").keyup(function()


{
jQuery("input[name='total']").val(parseFloat(jQuery("input
[name='price']").val())*parseInt(jQuery("input[name='quantity']"));
});

Use firebug in to debug the JS. compute should not be used if the
field is shown.

On Jan 14, 2:18 pm, Alexandre Andrade <alexandrema...@gmail.com>
wrote:


> 1. The total is not showed when editing. I don't need in this moment. When I
> set .compute, its hide (like writable=False and readable=False) the field in
> the form.
> 2. the main problem is .compute is not inserting/updating the field on db,
> and neither acting as represent when showing
>

> 2010/1/14 mdipierro <mdipie...@cs.depaul.edu>

> > > > <web2py%2Bunsu...@googlegroups.com<web2py%252Buns...@googlegroups.com>
> > <web2py%252Buns...@googlegroups.com<web2py%25252Bun...@googlegroups.com>

Alexandre Andrade

unread,
Jan 14, 2010, 3:42:50 PM1/14/10
to web...@googlegroups.com
Solved without jquery

       if form.accepts(request.vars, session):
             row = db.cronograma_desembolso[form.vars.id]
             total = row.valor_custeio_concedente + row.valor_investimento_concedente + row.valor_custeio_contrapartida + row.valor_investimento_contrapartida
             row.update_record(valor_total = total)

2010/1/14 mdipierro <mdip...@cs.depaul.edu>
To unsubscribe from this group, send email to web2py+un...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/web2py?hl=en.



mdipierro

unread,
Jan 14, 2010, 4:04:44 PM1/14/10
to web2py-users
OK. You can also do:

if form.accepts(request.vars, session,
onvalidation=lambda form: form.vars.valor_total =
form.vars.valor_custeio_concedente +
form.vars.valor_investimento_concedente +
form.vars.valor_custeio_contrapartida +
form.vars.valor_investimento_contrapartida):

which is 2 db queries less.

On Jan 14, 2:42 pm, Alexandre Andrade <alexandrema...@gmail.com>
wrote:


> Solved without jquery
>
>        if form.accepts(request.vars, session):
>              row = db.cronograma_desembolso[form.vars.id]
>              total = row.valor_custeio_concedente +
> row.valor_investimento_concedente + row.valor_custeio_contrapartida +
> row.valor_investimento_contrapartida
>              row.update_record(valor_total = total)
>

> 2010/1/14 mdipierro <mdipie...@cs.depaul.edu>

> > > > > > <web2py%2Bunsu...@googlegroups.com<web2py%252Buns...@googlegroups.com>
> > <web2py%252Buns...@googlegroups.com<web2py%25252Bun...@googlegroups.com>
>
> > > > <web2py%252Buns...@googlegroups.com<web2py%25252Bun...@googlegroups.com>
> > <web2py%25252Bun...@googlegroups.com<web2py%2525252Bu...@googlegroups.com>

Alexandre Andrade

unread,
Jan 15, 2010, 7:30:21 AM1/15/10
to web...@googlegroups.com
Massimo,

Maybe the problem with .compute in my table is that implements "audit trail' pattern (web2pyslices). I don't know how 'compute' works, but the fact of 'audit trail' tables the id is not unique can make it fail.


thanks for the help, but the proposed solution  returns a invalid sintax

Error traceback

1.
2.
3.
4.
5.
6.
7.
Traceback (most recent call last):
  File "gluon/restricted.py", line 171, in restricted

  File "D:/servico/web2py/applications/convenios/controllers/convenios.py", line 215

    if form.accepts(request.vars, session,onvalidation=lambda form: form.vars.valor_total = form.vars.valor_custeio_concedente + form.vars.valor_investimento_concedente + form.vars.valor_custeio_contrapartida + form.vars.valor_investimento_contrapartida):

                                                                                          ^
SyntaxError: invalid syntax

However,

             row = db((db.cronograma_desembolso.id == form.vars.id) & (db.cronograma_desembolso.active==True)).select()[0]

             total = row.valor_custeio_concedente + row.valor_investimento_concedente + row.valor_custeio_contrapartida + row.valor_investimento_contrapartida
             row.update_record(valor_total = total)        

works with audit trail



2010/1/14 mdipierro <mdip...@cs.depaul.edu>
To unsubscribe from this group, send email to web2py+un...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/web2py?hl=en.



everton rocha

unread,
Aug 17, 2017, 5:31:45 PM8/17/17
to web2py-users

Alexandre,
Desculpa pegar carona no seu post. Pode me ajudar?

Tentei adaptar seu código, mas sem sucesso.

Desculpe mais uma vez....

def ver_todos_projetos():
    db.servicos.id.readable = False
    query = ((db.servicos.id > 0))
    fields = (db.servicos.id, db.servicos.empresa, db.servicos.contrato, db.servicos.cliente, db.servicos.bacia, db.servicos.contrat_ini, db.servicos.contrat_fim, db.servicos.tempo_ctr)
    headers = {'servicos.id': 'ID',
               'servicos.empresa': 'Empresa',
               'servicos.cliente': 'Cliente',
               'servicos.contrato': 'Contrato',
               'servicos.bacia': 'Bacia',
               'servicos.contrat_ini': 'Data Início Contrato',
               'servicos.contrat_fim': 'Data do Fim do Contrato'
               'servicos.tempo_ctr'}
    grid = SQLFORM.grid(query=query, fields=fields, headers=headers, maxtextlength=64, paginate=25, deletable= False, user_signature = False)
    if form.accepts(request.vars, session):
             row = db.servicos[form.vars.id]
             total = ((row.contrat_fim).date - (row.contrat_ini).date)
             row.update_record(tempo_ctr = total)

    return dict(grid=grid)
Reply all
Reply to author
Forward
0 new messages