Auto Increment Help

[losthorn]

I am moving an Access database to a MySQL web based. The original DB has a key field that is a character field that is a 9 digit "number". That "number" is YYYY##### and is the "order" number for the client. I would like to turn this into a auto increment field that 1) increase the YYYY##### by 1 as a new order is entered. Ideally, it would reset the year (YYYY) part at the beginning of each year and also reset the counter back to 00000 (#####). My experience level is pretty low, so just getting auto increment part to work would be a big first step. For example, the last entered data for this field is 201701345, I need the next number to be 201701346.... On 1/1/2018, I want the next available number to be 201800001.

There is the possibility of multiple users entering orders at the same time, so I believe some sort of record lock is needed. I don't anticipate that there would be an instance of disconnected entry, but I should probably consider it.

One other question - This Access DB is pretty old and has multiple tables. As a general question, is MySQL's performance significantly enhanced by relational architecture or can it perform adequately with fewer, larger tables?

Any suggestions would be appreciated. Pointing me to videos or examples would also be great.

Thanks in advance.

[losthorn]

Re: Auto Increment Help

Anyone?

[johnkoh]

Re: Auto Increment Help

Kevin

you need to know about this and this is for Alpha five.
1) Get the year vYearNow = cyear(date()) which will be 2017 return as "C" type
2) Get the Last record from your table vLastno = TABLEMAX("your table name ","left(the_id,4) = "+s_quote(vYearNow),"the_id")
3) write code at some where on an event.

If TABLEMAX("your table name ","left(the_id,4) = "+s_quote(vYearNow),"the_id") = ""  then  'nothing
vMyNumber = vYearnow+"000001"     'type as you wish at here
else
vMyNumber = INCREMENT_VALUE(TABLEMAX("your table name ","left(the_id,4) = "+s_quote(vYearNow),"the_id"))   'increased by one
end if

4) change the your table Field's value with vMyNumber on the event

and I do have similar auto-increment but it is less mean than plain auto-increment and it is better for later.

[losthorn]

Re: Auto Increment Help

Kevin

you need to know about this and this is for Alpha five.
1) Get the year vYearNow = cyear(date()) which will be 2017 return as "C" type
2) Get the Last record from your table vLastno = TABLEMAX("your table name ","left(the_id,4) = "+s_quote(vYearNow),"the_id")
3) write code at some where on an event.

If TABLEMAX("your table name ","left(the_id,4) = "+s_quote(vYearNow),"the_id") = ""  then  'nothing
vMyNumber = vYearnow+"000001"     'type as you wish at here
else
vMyNumber = INCREMENT_VALUE(TABLEMAX("your table name ","left(the_id,4) = "+s_quote(vYearNow),"the_id"))   'increased by one
end if

4) change the your table Field's value with vMyNumber on the event

and I do have similar auto-increment but it is less mean than plain auto-increment and it is better for later.

Thank you. I will give that a try.