Field set the default value +1 on insert

[Bart van Berkel]

Hello web2py freaks,

In my first try to make an application with web2py i have a little struggle how to accomplish as what is would like to see.

In db1.py i created a field like this:

db.define_table('jobticket',
                Field('ticket', 'integer', default = +1) # ticket needs to be ticket +1 when inserted
               )

I need to have something called "ticket" what represent a number where my other tables are linked to.

I know that the default id is auto increment, and i can reference it, so that's not the point.

The ticket is a number what i needs to be set like : 150000001. And that needs to be inserted +1 if there is a new job.

15 is a year, the rest is just a number.

So, i have 2 problems:

- if there is a new year like 1 january 2016, the first two numbers needs to be changed from 15 to 16?

- And how can i  get the max number out of the database and insert that with a new value( +1)

Does anybody have a good insperation or an good idea to get me on the right track?

Many thanks.

Bart

[Niphlod]

if you want to have a number that strictly:

- is incremental (no gaps, always sequential, etc)

- the first two digits are the year

I'd suggest to have a "last_tickets" table  (year(int) ,     ticket_no(int))

 holding the current ticket.

then, you can use _before_insert callback on the "tickets" table to:

- fetch the last ticket_no for the current year

- compute the next integer, update last ticket_no

- use the computed value in the ticket_no of the "tickets" table

[Massimo Di Pierro]

Also you would have to requires the Field('ticket',...,unique=True) because this method it not thread safe and you should be prepared that inserts may fail.

[Craig Matthews]

The following is something that I have used in MySQL.

MySQL statement:
CREATE TABLE IF NOT EXISTS autonumbering
  ( Year          char(4)  not null default '',
    Type      varchar(10)  not null default '',
    ID            int(11)  not null auto_increment,
    Notes     varchar(64)  not null default '',
    CreatedOn   timestamp  not null default current_timestamp,
    PRIMARY KEY (Year, Type, ID)
  ) ENGINE=MyISAM AUTO_INCREMENT=1;

Code in a model:
autoNumbering.define_table('autonumbering',
  Field('Year',      'string',   length=4,  notnull=True, default='',                      required=True, label='Year',       readable=True, writable=True),
  Field('Type',      'string',   length=10, notnull=True, default='',                      required=True, label='Type',       readable=True, writable=True),
  Field('id',        'id'),
  Field('Notes',     'string',   length=64, notnull=True, default='',                      required=False,label='Notes',      readable=True, writable=True),
  Field('CreatedOn', 'datetime',            notnull=True, default=datetime.datetime.now(), required=False,label='Created On', readable=True, writable=True),
  migrate=False
)

Code in a module:
def getWorkOrderNumber(Year):     # Year is 4-digits, as in 2015
    r = autoNumbering.autonumbering.insert(Year=Year, Type='WorkOrder')
    autoNumbering((autoNumbering.autonumbering.Year == Year) & (autoNumbering.autonumbering.Type == 'WorkOrder') & (autoNumbering.autonumbering.id == str(r - 1))).delete()
    return Year[2:] + str(r).rjust(5, '0')

This returns a unique, sequential 5-digit number prefixed by the last two digits of the provided year.

[Bart van Berkel]

Hello my friends,

Thanks for the inspiration.

The answer from Craig, looks to me a good idea how i can do it.

many thanks,

Bart