Auto Increment including Year and Month

[CMcCoun]

I have a table called Project with an auto increment field for project estimate bid number called Project_ID. This field is auto incremented. I have created it as an 8 digit character field which carries the field rule for the Data Entry Tab as a Simple default expression value of �P0000001� . I set with a check mark, that the value is to be entered at the beginning of data entry along with a Skip Rule written as Project_ID>=�P0000001�. This all works fine. However what I now need it to do is a little more complicated. I need it to auto increment as a two number year, two number month, include a hyphen, and then a number starting at 001 for first record in that time period. An example for the month of April 2012 would be 1204-001 for the first record, 1204-002 for the 2nd and etc. then when the month of May rolls around the Project_ID would change to 1205-001.
What I�ve been trying to write is as follows, I kept it as a simple default expression with a default value of Cyear(date()) + (month()) + �-� + �001� and a skip value written as Project_ID>=�1204-001�. I get the warning message Too few parameters. I�m not seeing what I might be missing, any help would be appreciated.
I�ve attached a screen shot with the warning message.

[Stan Mathews]

Re: Auto Increment including Year and Month

Month_Number as N = MONTH( Date as D )

Month_Number as N = MONTH( Date_Time as T )

Month() requires a parameter you haven't supplied.

[CMcCoun]

Re: Auto Increment including Year and Month

Thanks for the quick reply.
Still a bit confused as a novice would be.
How do I apply your response into the Expression Builder for my Default Value?

[Stan Mathews]

Re: Auto Increment including Year and Month

I kept it as a simple default expression with a default value of Cyear(date()) + (month(date_value)) + “-“ + “001” and a skip value written as Project_ID>=”1204-001”.

Put some date value in the month function. I'm unclear whether you want to use the current date

Cyear(date()) + (month(date())) + “-“ + “001”
or another field in the current record

Cyear(date()) + (month(date_field_name)) + “-“ + “001”

[Allen Klimeck]

Re: Auto Increment including Year and Month

I don't believe that the built in auto increment can do a complex increment you will need to write your own.

Here is one way at learn alpha dot com:

http://www.learn alpha.com/autoincre...oincrement.htm

If this is a "key" field it isn't good programming to make an auto increment meaningful you had it done correctly to begin with

[Stan Mathews]

Re: Auto Increment including Year and Month

I kept it as a simple default expression with a default value of Cyear(date()) + (month()) + “-“ + “001” and a skip value written as Project_ID>=”1204-001”.

I think the term auto-increment was used loosely. could be wrong.

[SNusa]

Re: Auto Increment including Year and Month

Here's an older (similar thread) where Stan provided a great "howto" along with another code example:
http://msgboard.alphasoftware.com/al...crement-number

.... I thought it might be "relevant."

[CMcCoun]

Re: Auto Increment including Year and Month

I tried Stan's recommendation with his change for my code.
Cyear(date()) + (month(date())) + “-“ + “001”
and a kept the skip value written as Project_ID>=”1204-001”.
I also changed the field length from 8 to 9 characters and this is what I get "20124-001"
it's close but I was shooting for "1204-001" a format which I need to use for work.
Can I somehow drop the century indentification of "20" and add a zero for the nine months that are only one digit long?

[SNusa]

Re: Auto Increment including Year and Month

I tried Stan's recommendation with his change for my code.
Cyear(date()) + (month(date())) + “-“ + “001”
and a kept the skip value written as Project_ID>=”1204-001”.
I also changed the field length from 8 to 9 characters and this is what I get "20124-001"
it's close but I was shooting for "1204-001" a format which I need to use for work.
Can I somehow drop the century indentification of "20" and add a zero for the nine months that are only one digit long?

This line of code formats the date function: DATE_FORMAT(date(),"MMdd") It returns the "0411" portion (returns type C)

http://wiki.alphasoftware.com/DATE_FORMAT+Function

What I don't necessarily "like" about this sequencing is that unless you also have another field keeping track of record sequence, it's difficult to "see" (after the year flips) exactly what order the records were created....

[CMcCoun]

Re: Auto Increment including Year and Month

Hi Robert,
If I was able to get what I'm looking for, I'm thinking that when the year flips to 2013 my incremented Bid number would change from starting with the number 12 (for the year 2012) to the number 13 for the year 2013. Wouldn't this numbering system still be in a creatation order at least to the turn of the century?

[DaveM]

Re: Auto Increment including Year and Month

Autoincrement on any part of a date is generally a BAD idea unless you are going to enter one record per year/month/day or?
also I suggest numbers for autoincrement fields(not character). Why would it be a have to to use numbers in sql?
also record numbers(fields) that you use in sets to connect tables, should not be seen by users. It confuses them.

[SNusa]

Re: Auto Increment including Year and Month

Hi Robert,
If I was able to get what I'm looking for, I'm thinking that when the year flips to 2013 my incremented Bid number would change from starting with the number 12 (for the year 2012) to the number 13 for the year 2013. Wouldn't this numbering system still be in a creatation order at least to the turn of the century?

MY mistake. I thought you were trying to use MMDD as the beginning, and not YYMM. And yes, it should work as expected for the next 87 years or so......
~But then again: Isn't this how the Y2K bug "manifested itself?" You've just reincarnated a "Y2.1K bug!"

I can see where using a field in this nature might be very advantageous. However, I still wouldn't use it as your primary key field. I'd keep the primary index field more "normalized." (Possibly like DaveM suggested. As a very accomplished a5 user, he may have reasons unknown to me.)

I do however like character type for dbf's at least.... You can keep them all the same length, and prefix them with a "descriptor."

Chars: (easy to see what's going on)
B00001
B00002......
B00009
B00010......
B00099
B00100
etc....

Numeric: (not so easy to follow)
1
2......
9
10.....
99
100
etc....

Either way, you can use another data field to store "0401-001" as you wish.....

[CMcCoun]

Re: Auto Increment including Year and Month

We’ll all be dead and buried by the time we need to worry about the Y2.1 bug, heck the Mayan Prophecy has us going sooner anyway.
This help I’ve been seeking is for only one application I’m trying to write for myself, we have been using this numbering system for keeping track of Estimating Bids even way before I started working for my present employer. I could manually enter this field; it was just that I was just thinking this would be cool if my Alpha 5 program would do it for me.

[Al Buchholz]

Re: Auto Increment including Year and Month

Alpha can do this, but you need to tell it how.

So instead of using the autoincrement feature that is designed for a similar, but quite different result - you need to write code for Alpha to do exactly what you want.

There are examples of similar code in the code archive. A little customization and you'll be off and running..

[BOB SULLIVAN]

Re: Auto Increment including Year and Month

I use an auto increment rule that you seem to want. I use the field rules to implement the autoincrement

default values conditional

VAL(RIGHT(CYEAR( DATE() ),2)) *1000000)+(MONTH( DATE() ) *10000)+1 RIGHT(SUBSTR(STR((PREVIOUS("SUBMISSION"))),4,3),2)<>LEFT(RIGHT(CDATE(DATE()),4),2)
PREVIOUS( "SUBMISSION" )+1 (SUBSTR(STR(PREVIOUS("SUBMISSION")),5,2))=SUBSTR(DTOC(DATE()),1,2)


These are on two different lines on the data entry tab in field rules. Please note that "(date()),4,2)" under default values is really the tail end of the first conditional value.

Submission is the field and the values returned are 12040001 for the first submission in april 2012

Bob Sullivan