Hi Dean,
Servoy supports relationships over different database connections to load related data but (sadly) doesn't support
searching over them. That is what that warning is all about.
So if you want to load all the items of a group of contacts (in this case of a single company) you need to fetch the PK's of these contacts first and then use that result to search in the items table using a separate search. Sadly you can't just load the PK's in the foundset of the items table since these are contact PK's and not Items PK's so you need to use SQL to solve this.
So your code could look like this:
- Code: Select all
// running on the contacts form (no real need to do this from companies)
var _ds,
_sSQL = "SELECT item_id FROM items WHERE contact_id IN ({$ARRAY})";
if (controller.find()) {
company_id = 1;
controller.search();
// get the contact PK's
_ds = databaseManager.convertToDataSet(foundset, ['contact_id']);
// replace the placeholder with the values in the array (turned back into a string joined by a comma)
_sSQL = utils.stringReplace(_sSQL, '{$ARRAY}', _ds.getColumnAsArray(1).join(","));
forms.items.controller.loadRecords(_sSQL);
}
If you prefer to use prepared statements instead of putting the PK's straight into the SQL then you could use the following code:
- Code: Select all
var _ds,
_sSQL = "SELECT item_id FROM items WHERE contact_id IN ({$ARRAY})",
_aPlaceholders;
if (controller.find()) {
company_id = 1;
controller.search();
// get the contact PK's
_ds = databaseManager.convertToDataSet(foundset, ['contact_id']);
// get an array with the correct amount of SQL placeholders (the questionmark) for each value in the array
_aPlaceholders = _ds.getColumnAsArray(1).map(function() { return '?'; });
// replace the {$ARRAY} placeholder with the string of SQL placeholders (i.e. ?,?,?,?,?, etc)
_sSQL = utils.stringReplace(_sSQL, '{$ARRAY}', _aPlaceholders.join(','));
// load the PK's with a prepared statement
forms.items.controller.loadRecords(_sSQL, _ds.getColumnAsArray(1));
}
And a more advanced option would be to use a database link (SQL/MED) in the first database that maps the items table into that database so you can treat it as a regular table inside this database. But that is a database specific DBA task and not all databases vendors support this option (but Sybase, SQL Server, Oracle, DB2 and PostgreSQL do).
Hope this helps.