Increment the value of a field + 1 or how to auto-increment a field value ?

[Leandro - ProfessionalIT]

Hi,
I need create a field in my table that receive the last value + 1
when I create a new record. For example to a questions table. In this
table I have a ID and a number of question field.

[Joe Barnhart]

You need this in addition to the field "id" which is automatically added to every table in web2py, and holds an integer that is auto-incremented?

[mdipierro]

db(db.table.id==3).update(db.table.field=db.table.field+1)

This syntax does not work on GAE.

Massimo

On Sep 26, 4:07 pm, Joe Barnhart <joe.barnh...@gmail.com> wrote:
> You need this in addition to the field "id" which is automatically added to
> every table in web2py, and holds an integer that is auto-incremented?
>
> On Sat, Sep 26, 2009 at 12:53 PM, Leandro - ProfessionalIT <
>

[ProfessionalIT]

Joe,

I have a table(in my ER) 'questionary' with fields: (id, name,
created_date, description) a table 'questions' with the fields (id,
id_questionary, number_question, question, answer). Well, in the field
number_question I want that in a "create operation" it receive your
last value + 1, for example:

Questionary:
id = 01
Name = "blablabla"
Created = 2009-09-30
Description = 'more blablabla'

Questions:
id = 01
Questionary = 01
Question Number = 01
Question = "How to do this ?"
Answer = "I am a dummie !."

id = 02
Questionary = 01
Question Number = 02 (get the last(max) value of this field +
1)
Question = "How to do this again ?"
Answer = "Oh ! my good !, really !, I am a dummie !."

Well I think that can't be the value of field id because it's the
primary key of the table, then this is my problem !.

ps: Sorry by my terrible english, I don't speak or write in
english. Google Tradutor is my friend.

[ProfessionalIT]

>         Question Number = 02 (get the last(max)(where the questionary_id=1) value of this field + 1)

Well, in a crud create form I have this questionary_id, it is shown
how a html select field.

[mdipierro]

You have to do it in two steps

max_id=db(db.table.questionary_id==1).select
(db.table.question_number,orderby=~db.question.number,limitby=
(0,1)).first().question_number
db.table.insert(....,question_number=max_id+1)

and make sure that one user at the time is editing this one
questionary.

[ProfessionalIT]

mdipierro,

But, how to get this value in a create crud form ?

Two questions, that can resolve my problem:

1) The create crud method has a "pre-action" where I can manipulate
this value ?
If yes:
2) How to set this value in a form generated by a
crud.create method ?


My main problem is: I have that show this "incremented value" in a
create form. This is my problem !.

[mdipierro]

not sure I understand. perhaps this:

max_id=db(db.table.questionary_id==1).select
(db.table.question_number,orderby=~db.question.number,limitby=
(0,1)).first().question_number


db.table.question_number.default=max_id+1
db.table.question_number.writable=False
form=crud.create(db.table)

[ProfessionalIT]

Work's fine !
Thanks Massimo.

-- Leandro.

[Web2py-SuperFan]

Hi Massimo,

I have a similar problem, and will deploy to gae

I want to do a crud.create form on table1.dollars and then when it is
accepted, automatically update table2.total with a running total

How do I do this?

Here's what I did but it just accepts the form data, inserts to table
1 then doesn't redirect and doesn't update table 2, i just remain at
the form view with the values blanked out

def personal_loan_commit():
loans_id = request.args(0)
db.commitments.lender_id.default = user_id
db.commitments.loan_id.default = loans_id
form = crud.create(db.commitments,next=URL
(r=request,f='personal_loans_listed',args=loans_id),message='Loan
Commitment Made')
if form.accepts(request.vars, session):
db(db.loans.id == loans_id).update(lender_members_committed =
db.loans.lender_members_committed +
1,lender_committed_amount=db.loans.lender_committed_amount +
request.vars.committed_amount)
loan = dict(id=request.args(0), title=request.args(1))
return dict (form=form,loan=loan)


Also, I saw you mention that certain syntax wont work on GAE, which
matches the syntax i have right after the form accepts line. What
should that change to as well?

On Sep 26, 4:31 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> not sure I understand. perhaps this:
>
> max_id=db(db.table.questionary_id==1).select
> (db.table.question_number,orderby=~db.question.number,limitby=
> (0,1)).first().question_number
>
> db.table.question_number.default=max_id+1
> db.table.question_number.writable=False
> form=crud.create(db.table)
>
> On Sep 26, 6:01 pm, ProfessionalIT <lsever...@gmail.com> wrote:
>
>
>
> > mdipierro,
>

> >    But, how to get this value in a createcrudform ?

>
> >    Two questions, that can resolve my problem:
>

> >    1) The createcrudmethod has a "pre-action" where I can manipulate

> > this value ?
> >         If yes:
> >              2) How to set this value in a form generated by a
> >crud.create method ?
>
> >     My main problem is: I have that show this "incremented value" in a

> > create form. This is my problem !.- Hide quoted text -
>
> - Show quoted text -

[Rudy]

Hi Massimo,

I have a multi tenant invoicing system serving multiple companies, all invoices from different companies are stored in a single table flagged with company_id. I would like each company being able to have their own invoice_number starting from 1 and increment up. 

db.define_table('invoice',

                Field('invoice_number', 'integer'),

                Field('customer', 'reference customer'),

                Field('total', 'double', default=0),

                Field('company_id', 'integer', readable=False, writable=False))

If i do (auth_user table has an additional field company_id) .... 

max_id=db(db.invoice.company_id==auth.user.company_id).select (db.invoice.invoice_number, orderby=~db.invoice.invoice_number, limitby= (0,1)).first().invoice_number

db.invoice.invoice_number.default = max_id + 1

form=SQLFORM(db.invoice).process()

It works but like you said it can't support multiple user creating invoices at the same time. Any suggestion what I can do to support multiple users creating invoices for a specific company at the same time? Thanks! Rudy

[Massimo Di Pierro]

I guess what I meant is that you have a possible concurrency problem:

max_id=db(db.invoice.company_id==auth.user.company_id).select (db.invoice.invoice_number, orderby=~db.invoice.invoice_number, limitby= (0,1)).first().invoice_number

db.invoice.invoice_number.default = max_id + 1

form=SQLFORM(db.invoice).process()

since two users may compute the same max_id and then submit two invoices.

You have to make sure the invoice_number is defined as unique