addFoundSetFilterParam with the IN Operator and a foundset

Classic Servoy
1

Filtering data with the IN operator and a foundset.
I would like to change the below statement to a Filter whit a foundset.
So I don’t have any SQL statement in my code.
Is this possible? When yes, how?

This works fine

var _fsTenant = databaseManager.getFoundSet('myDB', 'tenants'); 
_fsTenant.addFoundSetFilterParam('oid', 'in', 'SELECT oidtenant FROM tenantusers WHERE oiduser = ' + _userID)
_fsTenant.loadAllRecords()

This is how I would like to do it, but it doesn’t work.

var _fsTenantUser  = databaseManager.getFoundSet(‘myDB’, 'tenantusers');
_fsTenantUser.addFoundSetFilterParam('oiduser','=',_userID)
_fsTenantUser .loadAllRecords()

var _fsTenant = databaseManager.getFoundSet(‘myDB’, 'tenants'); 
_fsTenant.addFoundSetFilterParam('oid', 'in', _fsTenantUser.oidtenant)
_fsTenant.loadAllRecords()
2

ttmgybta,

You can get the current pks from the foundset and use those in a filter:

_fsTenant.addFoundSetFilterParam('oid', 'in', databaseManager.convertToDataSet( _fsTenantUser,['oidtenant']).getColumnAsArray(1))

databaseManager.getSQL(foundset) could also be an option, but this function can return something that refers to temp tables.

Also you could consider using a relation from tenants to tenantusers and do a related search:

if (_fsTenant.find())
{
     tenants_to_tenantusers.oiduser = _userID
    _fsTenant.search()
}

Rob

3

Tank you Rob

I tried the first code you suggested, but I t doesn’t make my code more clear.
So does it madders when I use SQl Query’s in my code when they are so simple, or do I run in to problems when I want to switch databases?

The SQL statement I’m talking about:

'SELECT oidtenant FROM tenantusers WHERE oiduser = ' + _userID
4

ttmgybta,

Simple queries like that work on any sql database.

Rob