2. PHPRunner General questions
  3. Using Before record added Event to create a field value for the record
This topic is locked

Using Before record added Event to create a field value for the record

 		10/17/2006 2:17:12 AM
PHPRunner General questions
jwoker author

When an order record is added I need to create a value for Order.jobid2 (which is not on the Order table add form) using the values to be added for Order.state(2 character text field), Order.date(date field) and the count of Order.state where Order.state = (the Order.state of the order to be added).
Specifically I am looking to create a value = CA-06-934 = ST-YY-COUNT(including all existing CA orders plus the one to be added)
or maybe this should be an after record added update of the order just added?
J
 		Jane 10/17/2006

Hi,
you can do it using Before record added event.

Here is a sample code:

function BeforeAdd(&$values)

{

global $conn;
$strSQL1= "select date_format('".$values["date"]."', '%y')";

$rs1 = db_query($strSQL1,$conn);

$data1 = db_fetch_numarray($rs1);

$year = $data1[0];
$strSQL2 = "select count(*) from Order where state='".$values["state"]."'";

$rs2 = db_query($strSQL2, $conn);

$data2 = db_fetch_numarray($rs2);

$sum = $data2[0]+1;
$values["jobid2"] = $values["state"]."-".$year."-".$sum;
return true;

}
jwoker author 10/19/2006

I'm not getting the date to come through. Where the code goes:
$strSQL1= "select date_format('".$values["date"]."', '%y')";

$rs1 = db_query($strSQL1,$conn);

$data1 = db_fetch_numarray($rs1);

$year = $data1[0];
I'm not sure why it would go to the database for the date. I think date is in $values["date"] so maybe I just need the php version of select date_format('".$values["date"]."', '%y') so that i can do something like:
$year = date_format('".$values["date"]."', '%y');
Peter
J
 		Jane 10/19/2006

Peter,
there is no date_format function in the PHP.
Date in the $values["..."] variable is stored in the following format: "yyyy-mm-dd hh:mm:ss".

To convert this value to array use db2time PHPRunner function.

Here is a sample:

$tm = db2time($values["date"]);

$year = $tm[0];