First of all, at the risk of asking a silly question - is there actually a
reason to store this secondary ID in the database rather than just have it
calculated on the fly as-needed using a virtual field? Assuming that you've
got a created_date field already in the table that'll give you the month
portion and then there's definitely an id field to give you that so just
let web2py figure it out for you on-the-fly
Field.Virtual('human_id', lambda row: int(row.your_table.created_date.
strftime('%y%m00000')) + row.your_table.id)
The above would cause a record created today (2017-03-05) that had id = 123
to return a human_id of 170300123 which is I think what you want. Note the
extra zeros tacked on to the end of the strftime they are important so that
when you add the ID you don't accidentally increment your month number - be
sure to include enough to ensure that you can cover the highest realistic
record ID (and then add an extra zero :D). Alternatively, you may wish to
consider changing your human ID format to something like yymm-###### so
that you don't have to worry about inadvertently messing up your date
related portion and it is perhaps slightly easier for humans to understand
which is presumably important because I don't get why you'd want to include
the year and month if it isn't supposed to mean anything to the user.
db.your_table.human_id = Field.Virtual('human_id', lambda row:
'{0}-{1}'.format(row.your_table.created_date.strftime('%y%m') +'-' + row.
your_table.id), table_name = 'your_table')
If you need to actually store it in the database then you'll have to work
more. Sadly, web2py's computed fields won't work because they don't know
the ID before the insert. That leaves you with doing it DB side. MySQL
appears to only lets you have one auto-increment per table so unfortunately
you can't have both your actual primary key ID and a second human readable
monthly ID (that would intentionally be reset to something like 1703000000
this month and 1704000000 next). What I'd consider is creating a DB
trigger that would take care of automatically populating your secondary ID
for you all within the database so that there's nothing for you to manage.
So on insert the database could automatically look at the date (or use an
existing created_date field) and the current auto-increment number and
combine as needed and store it for you.
~Brian
On Sunday, March 5, 2017 at 11:26:51 AM UTC-6, Paul Ellis wrote:
>
> I want to have a numbering system which is 2 digit year, 2 digit month and
> then an autoincrement number which resets each month. 1703#####
>
> Currently using SQLite with a view to move to MYSQL in future. The MYSQL
> examples I have found suggest using a composite primary key, which doesn't
> seem to fit too well with web2py.
>
> The reason I am trying for autoincrement is so the database ensures the
> numbers are unique. I am willing to look at another way if I am sure I
> won't end up with 2 identical numbers.
>
> At the moment I have it working with
> datetime.date.today().strftime('%y%m') + id. So I am half way there, but
> can't see how to reset each month. I guess I can check for the highest
> number in the database programmatically, but I am worried about duplicate
> numbers with a high number of users. This also seems like if the document
> with the highest number was deleted (but possibly already printed), then
> the number will be reused.
>
> The program uses the actual primary key for all backend work. This is just
> an identifier for humans, but the documents produced by the program can't
> afford to have the same number as another document.
>
> Can someone give me a nudge in the right direction, I am a bit stuck?
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
