Perform a SELECT statement on a foundset

Classic Servoy
1

Hi, is it possible to perform a SELECT statement on an existing foundset with data?

I currently have 2 foundsets. The first foundset has 1,2,3,4,5 records, while 2nd foundset has 1,3,7 records. How Can I perform the SQL statement of the 2nd foundset to the first foundset and retrieve 1 and 3 values only? The database has 1,2,3,4,5,6,7,8,9, and 10 values.

My workaround is to convert both foundsets to datasets and manually check if the 2nd dataset exists in the first dataset. If I have thousands of records, this runs very very slow. Is it possible to perform this through a SELECT statement applied on a foundset?

2

Hi,

you can retrieve the current SQL from a foundset with

var _sql = databaseManager.getSQL();
var _args = databaseManager.getSQLParameters();

You could use that to construct a new query.

Most easy way (maybe not most efficient) is something like this:

var _newQry = 'SELECT myPk FROM myTable WHERE someArg = ? AND myPK IN (' + _sql + ')';
var _qryArgs = ['mySomeArgValue'].concat(_args);

forms['myTableBasedForm'].foundset.loadRecord(_newQry, _qryArgs);

Hope this helps