This topic is locked

Generate a custom Transaction Number, for example : SO20100700001

6/30/2010 9:26:08 PM
PHPRunner Tips and Tricks
romaldus author

In this scenario, we have a TABLE called SALES_ORDER

SO_ID is the primary key which generated automatically when each record inserted
===================================================

SO_ID............PART_NUMBER......QTY

===================================================

SO20100700001....PART1.............10

SO20100700002....PART2.............20

SO20100700003....PART3.............11

SO20100700004....PART4.............41

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

SO20100799999....PART8.............41

===================================================
SO : Custom string, you can replace with your own

2010 : current year

07 : current month (july)

000001-99999 : sequence number
use phprunner Before record added event on the Events tab to calculate SO_ID:

$sql="select max(substr(SO_ID,9)) as mx from SALES_ORDER where substr(SO_ID,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["SO_ID"]="$str$str2".str_pad($str3, 5, 0, STR_PAD_LEFT);


NOTE :

The above code will reset automatically in the next month (August)

If you start a new SALES ORDER on August the SO_ID value:
SO2010

SO2010[b][color="#0000FF"]08

[color="#FF0000"][size="5"]Tested on PHPRUNNER 5.2 and MySQL 5.0[/size]

M
mumtaz 7/12/2010

I've tried the tricks that you provide to create the appropriate table to the one in the example. But I have not been able to prove what you write. The following table specification and function I was at phprunner.
SALES_ORDER

SO_ID - VARCHAR(13)

PART_NUMBER - VARCHAR(13)

QTY - NUMBER
MY FUNCTION SCRIPT
$sql="select max(substr(SO_ID,9)) as mx from sales_order where substr(SO_ID,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["SO_ID"]="$str$str2".str_pad($str3, 5, 0, STR_PAD_LEFT)
[size="4"]Please show me where the fault lies?[/size]



In this scenario, we have a TABLE called SALES_ORDER

SO_ID is the primary key which generated automatically when each record inserted
===================================================

SO_ID............PART_NUMBER......QTY

===================================================

SO20100700001....PART1.............10

SO20100700002....PART2.............20

SO20100700003....PART3.............11

SO20100700004....PART4.............41

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

SO20100799999....PART8.............41

===================================================
SO : Custom string, you can replace with your own

2010 : current year

07 : current month (july)

000001-99999 : sequence number
use phprunner Before record added event on the Events tab to calculate SO_ID:

$sql="select max(substr(SO_ID,9)) as mx from SALES_ORDER where substr(SO_ID,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["SO_ID"]="$str$str2".str_pad($str3, 5, 0, STR_PAD_LEFT);


NOTE :

The above code will reset automatically in the next month (August)

If you start a new SALES ORDER on August the SO_ID value:
SO2010

SO2010[b][color="#0000FF"]08
00002

romaldus author 7/13/2010

mumtaz,

make sure you use the latest version of PHPrunner .

In example above, i use MySQL 5 as the database. I haven't test it in other database..

M
mumtaz 7/18/2010

I've been using phprunner 5.2 (trial version) with mysql database.

its stil not running.
Can u give me your db structure for this tutorial. maybe I was using the wrong table structure



mumtaz,

make sure you use the latest version of PHPrunner .

In example above, i use MySQL 5 as the database. I haven't test it in other database..

romaldus author 7/19/2010



I've been using phprunner 5.2 (trial version) with mysql database.

its stil not running.
Can u give me your db structure for this tutorial. maybe I was using the wrong table structure


Mumtaz, no matter you use the trial version or the licensed one, this custom code should work.
Here is the example from MySQL dump :

CREATE TABLE IF NOT EXISTS `sales_order` (

`SO_ID` varchar(20) NOT NULL,

`PART_NUMBER` varchar(20) DEFAULT NULL,

PRIMARY KEY (`SO_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;


If you don't mind please send me your sample project file along with your sample database to romaldus@gmail.com

M
mumtaz 7/19/2010

Romaldus

thx for your respon
email was sent

M
mumtaz 8/6/2010

I have tried this trick. But when I try to change the year. Numbering continues to not generate from scratch. I use this script :

$sql="select max(substr(SO_ID,9)) as mx from SALES_ORDER where substr(SO_ID,7,2)=month(now()) and substr(SO_ID,3,4)=year(now()) order by mx";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$str="SO";

$str2=date("Ym");

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

$values["SO_ID"]="$str$str2".str_pad($str3, 5, 0, STR_PAD_LEFT);
R
rjr003 8/8/2010

Hello,
I have tried this code in the 'before record added' on the 'add page' of a purchase order form. It has worked well for a while but then i must have changed something in the structure of my database and now an error occurs.
The error description on the browser states 'Record was NOT added. Column 'po_number' specified twice'.
The code I am using is the following:
$sql="select max(substr(po_number,9)) as mx from purchase_orders where substr(po_number,7,2)=month(now()) and substr(po_number,3,4)=year(now()) order by mx";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$str="PO";

$str2=date("Ym");

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

$values["po_number"]="$str$str2".str_pad($str3, 5, 0, STR_PAD_LEFT);
Is someone able to help me?
Regards,

Ricardo

R
rjr003 8/8/2010

Hello,
I have found the solution. I added an 'Alias' to the 'po_number' field on the query builder and the reference to the field changed accordingly.
Regards.

Ricardo

A
ashumak 8/29/2010



Hello,
I have found the solution. I added an 'Alias' to the 'po_number' field on the query builder and the reference to the field changed accordingly.
Regards.

Ricardo


Tried this, but it isn't working. The information gets emailed to me with the same number each time, and the database is never updated.

A
ashumak 8/29/2010

$sql="select max(substr(Heat,9)) as mx from customer_service_log where substr(Heat,7,2)=month(now()) order by mx";$rs=CustomQuery($sql);$data=db_fetch_array($rs);$str="HT";$str2=date("Ym");$str3=($data["mx"]+1);$values["Heat"]="$str$str2".str_pad($str3, 5, 0, STR_PAD_LEFT);
This is what I used. While I get an email showing HT20100800001 as the Heat Ticket, the dtatbase is incresing the number up by one from 75, 76, 77, etc... and the email always shows the same as above...

A
Abul 1/30/2014



$sql="select max(substr(Heat,9)) as mx from customer_service_log where substr(Heat,7,2)=month(now()) order by mx";$rs=CustomQuery($sql);$data=db_fetch_array($rs);$str="HT";$str2=date("Ym");$str3=($data["mx"]+1);$values["Heat"]="$str$str2".str_pad($str3, 5, 0, STR_PAD_LEFT);
This is what I used. While I get an email showing HT20100800001 as the Heat Ticket, the dtatbase is incresing the number up by one from 75, 76, 77, etc... and the email always shows the same as above...


I have the same problem but I fixed it by using following:
$sql="select max(substr(ordno,12,5)) as mx from ord where substr(ordno,8,2)=month(now()) order by mx";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$str="OR-";

$str2=date("Ymd");

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

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

emendoza 4/4/2014

It works!

I tried It With PHPRunner 7.0 and it works perfectly!

Thank you

romaldus author 7/28/2014



hi,

can someone translate this code to be use on ASPR 7.1?

thanks.


tested in phprunner 7.1 and no problem