Hey Brian,
Doing it on the fly won't work because I want the number to be set at
record creation and be a part of the dataset. Also, this is what I am
already doing.
Using a database trigger is something I don't know anything about. So thank
you for the nudge, I will research this option.
Paul
On Mon, Mar 6, 2017 at 1:03 AM, Brian M <bmere...@gmail.com> wrote:
> 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 a topic in the
> Google Groups "web2py-users" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/web2py/hH_O_mUV4rw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> web2py+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
