2. PHPRunner General questions
  3. [SOLVED]  Concatenate current date + autonumber
This topic is locked
[SOLVED]

 Concatenate current date + autonumber

 		6/23/2010 1:46:54 AM
PHPRunner General questions
romaldus author

I have a table called PURCHASE where PURCHASE_NUMBER is the primary key field:
How to generate PURCHASE_NUMBER based on current date + sequence number/auto number?

For Example : Current date is 2010-23-06
Generated values should be:
201023061

201023062

201023063
Where the last number is a sequence number / auto increment when each record inserted

If it is possible, the sequence number will restart from 1 on the next month..
How i configure it in PHPRUNNER add event?

I have search this on the entire forum and i found nothing. Please help.
A
 		ann 6/23/2010

Hi,
use Before record added event on the Events tab to calculate PURCHASE_NUMBER.

Here is a sample:

$sql="select max(substr(PURCHASE_NUMBER,9)) as mx from TableName where substr(PURCHASE_NUMBER,5,2)=month(now())";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$str=date("Ymd");

$str.=($data["mx"]+1);

$values["PURCHASE_NUMBER"]=$str;



where TableName is your table name.
romaldus author 6/23/2010



Hi,
use Before record added event on the Events tab to calculate PURCHASE_NUMBER.

Here is a sample:

$sql="select max(substr(PURCHASE_NUMBER,9)) as mx from TableName where substr(PURCHASE_NUMBER,5,2)=month(now())";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$str=date("Ymd");

$str.=($data["mx"]+1);

$values["PURCHASE_NUMBER"]=$str;



where TableName is your table name.



Thank You very much Ann..... <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=50705&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' />
romaldus author 6/23/2010

I have another question based on the code above. How to modify the code above so that each transaction number started with "TN"

For Example :
TN201023061

TN201023062
A
 		ann 6/24/2010

Hi,
just concat "TN" to the variable $str.

here is a sample:

$str="TN";

$str.=date("Ymd");

...
romaldus author 6/24/2010



Hi,
just concat "TN" to the variable $str.

here is a sample:

$str="TN";

$str.=date("Ymd");

...



I have added the code above this way :

$sql="select max(substr(PURCHASE_NUMBER,9)) as mx from TableName where substr(PURCHASE_NUMBER,5,2)=month(now())";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$str="TN";

$str.=date("Ymd");

$str.=($data["mx"]+1);

$values["PURCHASE_NUMBER"]=$str;


the string TN generated successfully but the auto increment number not works (it was stuck on 1) when i generate the application:
For example :
The first transaction number : TN201023061

The second transaction nuumber : TN201023061 (produce error due to duplicate primary key)
The second transaction number should be : TN201023062
A
 		ann 6/25/2010

Hi,
you also need to modify query:

$sql="select max(substr(PURCHASE_NUMBER,11)) as mx from TableName where substr(PURCHASE_NUMBER,7,2)=month(now())";



If it doesn't help please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error. 'Demo Account' button can be found on the last screen in the program.
romaldus author 6/25/2010

Thanks ann but the number only increment until 10 and stuck...

$sql="select max(substr(PURCHASE_NUMBER,11)) as mx from TableName where substr(PURCHASE_NUMBER,7,2)=month(now())";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$str="TN";

$str.=date("Ymd");

$str.=($data["mx"]+1);

$values["PURCHASE_NUMBER"]=$str;


TN201006251

...........

TN2010062510
can not generate TN2010062511
romaldus author 6/25/2010



Hi,
use Before record added event on the Events tab to calculate PURCHASE_NUMBER.

Here is a sample:

$sql="select max(substr(PURCHASE_NUMBER,9)) as mx from TableName where substr(PURCHASE_NUMBER,5,2)=month(now())";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$str=date("Ymd");

$str.=($data["mx"]+1);

$values["PURCHASE_NUMBER"]=$str;



where TableName is your table name.


[size="3"]This one is also stop the increment in number 10[/size]
B
 		bangke 6/25/2010

[size="3"]This one is also stop the increment in number 10[/size]

[/quote]
I have the same problem with event code above..

Auto Number stop increase on 10

is it a phprunner bug?
B
 		bangke 6/27/2010



[size="3"]This one is also stop the increment in number 10[/size]



The problem might happen in this line of code:

$sql="select max(substr(PURCHASE_NUMBER,9))...........



PHPRUNNER generated application reads only the 9th number which is 9 in 201023069. So after 9 it generates 10.

10 cannot read as 10 to generate 11, but read as 1.

Since the greatest number is 9, PHP always generate 2010230610 and cause error due to duplicate primary key....
B
 		bangke 6/27/2010



The problem might happen in this line of code:

$sql="select max(substr(PURCHASE_NUMBER,9))...........



PHPRUNNER generated application reads only the 9th number which is 9 in 201023069. So after 9 it generates 10.

10 cannot read as 10 to generate 11, but read as 1.

Since the greatest number is 9, PHP always generate 2010230610 and cause error due to duplicate primary key....


Please help for this problem... i really need this...
J
 		Jane 6/28/2010

Please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error.
romaldus author 6/28/2010



Please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error.



[size="4"]I found the solution now :[/size] <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=50843&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' />
The code below will concat CUSTOM STRING + CURRENT YEAR + CURRENT MONTH + SEQUENCE NUMBER to produce transaction number

The Sequence number will always start from 00001 when start each month.
SO20100600001

SO20100600002

SO20100600003

............

SO20100600011

SO20100600012
[color="#FF0000"]Sample for transaction in month July :

SO20100700001

SO20100700002

SO20100700003

............

SO20100700011

SO20100700012
The code is :

$sql="select max(substr(PURCHASE_NUMBER,9)) as mx from TABLENAME where substr(PURCHASE_NUMBER,7,2)=month(now()) order by mx";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$str="SO";

$str2=date("Ym");

$str3=($data["mx"]+1);

$values["PURCHASE_NUMBER"]="$str$str2".str_pad($str3, 5, 0, STR_PAD_LEFT);


where SO is my custom string. you can replace it with your own...