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.
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.
--
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 ;
--
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 ;
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...
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.
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"] . ";");
}
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);
}