[Komputer] Auto update value using event to create sequence number on PHP Runner

QUESTION

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. 

ANSWER : 

1. use Before record added event on theEvents 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. 

2. 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())";

3. I found the solution now :  

The code below will concate :
CUSTOM STRING + CURRENT YEAR + CURRENT MONTH + SEQUENCE NUMBER
to produce transaction number.

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

Sample for transaction in month June :
SO20100600001
SO20100600002
SO20100600003
............
SO20100600011
SO20100600012

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...

Original Link