LOGIsoft wrote:Something like this launched from the Clients form (based on a recent post from Robert Ivens - thanks!):
You're welcome
I believe iAnywhere doesn't support LIMIT n but uses TOP n. Also all column names should be prefixed with the table alias. I know it might work without but lets say it's the proper way to do it when you mix and match columns in subqueries.
So your code will look something like this:
- Code: Select all
var vForm = application.getMethodTriggerFormName();
var vTable = controller.getTableName();
var vServer = controller.getServerName();
var sQuery = "UPDATE TABLE clients a SET a.id_client_rep = (SELECT TOP 1 b.id_client_rep FROM invoices b WHERE b.id_client = a.id_client ORDER BY b.invoice_date desc)";
var sArg = "";
if ( !plugins.rawSQL.executeSQL( vServer, vTable, sQuery, sArg) ) {
// something went wrong
application.output( "Error: " + plugins.rawSQL.getException() );
} else {
// need to flush any cached data of this table or else your and other clients won't see it.
plugins.rawSQL.flushAllClientsCache(vServer, vTable);
}
I suggest you try the SELECT query in iSQL first to see it if it gives the required result.
Hope this helps.