Hi Ian,
So you want to have any combination of the 2 tables in TableAct_SurfTypes, right ? I assume the an_code and act_code are unique values in their respective tables.
If so then you can use the following methods that you trigger AFTER you insert or update data in the 2 tables.
function onInsertTableActivities() {
var _sActCode = arguments[0],
_sServer = "myServerName",
_sQuery = "INSERT INTO TableAct_SurfTypes (an_code,act_code,detail) SELECT an_code,?,surf_type_detail FROM TableSurfTypes";
databaseManager.startTransaction();
if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery, [_sActCode]) ) {
application.output("Error: " + plugins.rawSQL.getExceptionMsg());
databaseManager.rollbackTransaction();
return false;
}
databaseManager.commitTransaction();
return true;
}
function onUpdateTableActivities() {
var _sOldActCode = arguments[0],
_sNewActCode = arguments[1],
_sServer = "myServerName",
_sQuery;
databaseManager.startTransaction();
// remove all records that have this act_code value
_sQuery = "DELETE FROM TableAct_SurfTypes WHERE act_code=?";
if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery,[_sOldActCode]) ) {
application.output("Error: " + plugins.rawSQL.getExceptionMsg());
databaseManager.rollbackTransaction();
return false;
}
// Lets re-add the records with the new information
_sQuery = "INSERT INTO TableAct_SurfTypes (an_code,act_code,detail) SELECT an_code,?,surf_type_detail FROM TableSurfTypes";
if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery, [_sNewActCode]) ) {
application.output("Error: " + plugins.rawSQL.getExceptionMsg());
databaseManager.rollbackTransaction();
return false;
}
databaseManager.commitTransaction();
databaseManager.flushAllClientsCache(sServer, "TableAct_SurfTypes");
return true;
}
So when you insert a new record in TableActivities:
controller.newRecord();
// do your thing
if ( databaseManager.saveData() ) {
if ( !onInsertTableActivities(act_code) ) {
// something went wrong
}
} else {
// couldn't save the data
}
And to do an update in TableActivities:
var _sOldActCode = act_code;
// do your thing
if ( databaseManager.saveData() ) {
if ( !onInsertTableActivities(_sOldActCode,act_code) ) {
// something went wrong
}
} else {
// couldn't save the data
}
And here are the insert/update methods for the TableSurfTypes:
function onInsertTableSurfTypes() {
var _sAnCode = arguments[0],
_sServer = "myServerName",
_sQuery = "INSERT INTO TableAct_SurfTypes (an_code,act_code,detail) SELECT ?,act_code,surf_type_detail FROM TableActivities";
databaseManager.startTransaction();
if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery, [_sAnCode]) ) {
application.output("Error: " + plugins.rawSQL.getExceptionMsg());
databaseManager.rollbackTransaction();
return false;
}
databaseManager.commitTransaction();
return true;
}
function onUpdateTableSurfTypes() {
var _sOldAnCode = arguments[0],
_sNewAnCode = arguments[1],
_sServer = "myServerName",
_sQuery;
databaseManager.startTransaction();
// remove all records that have this act_code value
_sQuery = "DELETE FROM TableAct_SurfTypes WHERE an_code=?";
if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery,[_sOldAnCode]) ) {
application.output("Error: " + plugins.rawSQL.getExceptionMsg());
databaseManager.rollbackTransaction();
return false;
}
// Lets re-add the records with the new information
_sQuery = "INSERT INTO TableAct_SurfTypes (an_code,act_code,detail) SELECT ?,act_code,surf_type_detail FROM TableActivities";
if ( !plugins.rawSQL.executeSQL(_sServer, "TableAct_SurfTypes", _sQuery, [_sNewAnCode]) ) {
application.output("Error: " + plugins.rawSQL.getExceptionMsg());
databaseManager.rollbackTransaction();
return false;
}
databaseManager.commitTransaction();
databaseManager.flushAllClientsCache(sServer, "TableAct_SurfTypes");
return true;
}
Same deal here for the TableSurfTypes only with an_code.
Now for deleting any records in either table you simply use a relationship between the tables and the TableAct_SurfTypes table that have a cascading delete.
Hope this helps.