increment job steps

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
jimbusk
User
Posts: 9

increment job steps

Post by jimbusk »

I have a job table and a jobstep table. When I insert a jobstep I want to autonumber the step number.

job 1001
step 001
step 002
job 1002
step 001
step 002
step 003
etc.

How do I do this in PHPMaker.


mobhar
User
Posts: 11660

Post by mobhar »

Post your table schema for dicussion.


jimbusk
User
Posts: 9

Post by jimbusk »

--

-- Table structure for table job

CREATE TABLE IF NOT EXISTS job (
JobNumber int(7) NOT NULL AUTO_INCREMENT,
Description varchar(255) COLLATE latin1_german2_ci NOT NULL,
ClientName varchar(60) COLLATE latin1_german2_ci DEFAULT NULL,
EmployeeName varchar(60) COLLATE latin1_german2_ci DEFAULT NULL,
ProjectNumber int(11) DEFAULT NULL,
JobStatus varchar(15) COLLATE latin1_german2_ci DEFAULT NULL,
Issued date DEFAULT NULL,
Closed date DEFAULT NULL,
ReportsTo varchar(60) COLLATE latin1_german2_ci NOT NULL,
Notes longtext COLLATE latin1_german2_ci,
Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (JobNumber)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci PACK_KEYS=0 AUTO_INCREMENT=29112 ;

--

-- Table structure for table jobstep

CREATE TABLE IF NOT EXISTS jobstep (
StepNumber int(11) NOT NULL AUTO_INCREMENT,
JobNumber int(7) NOT NULL,
JobStep decimal(3,0) NOT NULL DEFAULT '0',
Description varchar(255) COLLATE latin1_german2_ci NOT NULL,
Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (StepNumber)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;


mobhar
User
Posts: 11660

Post by mobhar »

Which fields do you want to use from those 2 tables for your business-logic above?


jimbusk
User
Posts: 9

Post by jimbusk »

JobNumber is the primary key of the job table.

JobNumber will be selected from a dropdown list when adding a jobstep.
I am looking for the best solution for incrementing the JobStep field in the when the jobstep row is inserted.

JobNumber and JobStep would result in a unique combination for an index on the jobstep.

Thanks...


mobhar
User
Posts: 11660

Post by mobhar »

The closest approach is by using "Row_Inserted" server event. Just get all the detail records based on "JobNumber" field and hold them into a recordset variable. Then, loop through the recordset, and simply update the "JobStep" field inside that loop.

Use ew_Execute() function to get the recordset using SELECT SQL, and also to execute the UPDATE SQL in the separated command.


jimbusk
User
Posts: 9

Post by jimbusk »

Solution:
1) Added a Master/Detail relationship to the job table. Under the Table Specific Options
'job','JobNumber' = 'jobstep','JobNumber'

2) Server Events, Table Specific, Common, Row_Inserted Added the following code:
// Row Inserted event

function Row_Inserted(&$rsold, &$rsnew) {
$this->setSuccessMessage("Record Inserted. The ID of the new record is " . $rsnew["JobNumber"]);
$MyCount = ew_ExecuteScalar("SELECT COUNT(*) from jobstep WHERE JobNumber = " . $rsnew["JobNumber"] . ";");
$this->setSuccessMessage("Step is " . $MyCount);

// Update record
// NOTE: Modify your SQL here, replace the table name, field name and field values
$MyResult = ew_Execute("UPDATE jobstep SET JobStep=$MyCount WHERE StepNumber= " . $rsnew["StepNumber"] . ";");

}


jimbusk
User
Posts: 9

Post by jimbusk »

Improved Solution:

// Row Inserted event

function Row_Inserted(&$rsold, &$rsnew) {
$this->setSuccessMessage("Record Inserted. The ID of the new record is " . $rsnew["JobNumber"]);
$MyCount = ew_ExecuteScalar("SELECT MAX(JobStep) from jobstep WHERE JobNumber = " . $rsnew["JobNumber"] . ";");
// $this->setSuccessMessage("Step is " . $MyCount);

// Update record
// NOTE: Modify your SQL here, replace the table name, field name and field values
$MyCount = ($MyCount + 1);
$MyResult = ew_Execute("UPDATE jobstep SET JobStep=$MyCount WHERE StepNumber= " . $rsnew["StepNumber"] . ";");
// $this->setSuccessMessage("Step is " . $MyCount);

}


Post Reply