using addTableFilterParam with IN

Classic Servoy
1

I have a table that contains department ID’s, and I would like to filter the table such that a specific user can see only records where deptID IN(1,2).

So, if I do…

databaseManager.addTableFilterParam(ddcinfo, recordings, deptID, IN, 1)

that works fine to show only deptID of 1, but I can’t get 1 OR 2 working.

I’ve tried

databaseManager.addTableFilterParam(ddcinfo, recordings, deptID, IN, '1,2')
databaseManager.addTableFilterParam(ddcinfo, recordings, deptID, IN, 1,2)
databaseManager.addTableFilterParam(ddcinfo, recordings, deptID, IN, ('1,2'))
databaseManager.addTableFilterParam(ddcinfo, recordings, deptID, IN, (1,2))

Is IN just not supported like the SQL IN statement is?

2

Update…
I’ve notices that this works:

var myDepts = new Array(1,2)
controller.addFoundSetFilterParam('deptid', 'IN', myDepts)

But this does not

var myDepts = new Array(1,2)
databaseManager.addTableFilterParam(ddcinfo, recordings, 'deptid', 'IN', myDepts)

Is this a bug I should report?

3

Scott,

Did you try to make the arguments an array? i.e:

databaseManager.addTableFilterParam(ddcinfo, recordings, deptID, IN, new Array(1,2))
4

You are the man Mr. Cusick! That worked great. Thank you.

5

One last question…

I have now gotten this working in a non-static context. So, we have a table with these fields…

etf_serverName, etf_tableName, etf_dataProvider, etf_operator, etf_value, etf_empShortName

And a module with a method (code below) to run on startup for our solutions to filter the tables based on the user name they log in with. Will this slow things down as solutions grow, or is it a good idea to easily manage security accross tables this way?

/******************************************
SB 7/24/06

Filters tables based on user security setup

Parameters:
NA

Returns:
NA
******************************************/

var shortName = security.getUserName(); // gets the current loggedIn username

var maxReturnedRows = 100;
var query = 'SELECT etf_serverName, etf_tableName, etf_dataProvider, etf_operator, etf_value, etf_empShortName FROM EmpTableFilters ' +
			'WHERE etf_empShortName = ?';
			
var args = new Array();
args[0] = shortName
var dataset = databaseManager.getDataSetByQuery("ddcinfo", query, args, maxReturnedRows);

for(var i=1; i<=dataset.getMaxRowIndex(); i++)
{
		var myOperator = dataset.getValue(i, 4);
		var myValue = dataset.getValue(i, 5);
		if(myOperator == 'IN')
		{
			//create array of comma separated values
			var myValArray = myValue.split(',')
			var success = databaseManager.addTableFilterParam(dataset.getValue(i, 1), dataset.getValue(i, 2), dataset.getValue(i, 3), myOperator, myValArray)
		}
		else
		{
			var success = databaseManager.addTableFilterParam(dataset.getValue(i, 1), dataset.getValue(i, 2), dataset.getValue(i, 3), myOperator, myValue)
		}
}

At this point it is working well, but the average user only has 2 tables that are being filtered. As we add more solutions, I would expect the average user to have about 50 tables being filtered. So…if 50 tables are filtered on the startup of a solution, is there much performance degradation?

6

LOVE IT!

Great technique. I LOVE using data-driven parameters for AS MUCH as possible. It has saved my a** more times than I can count.

Regarding speed - depends on what you’re putting in there. As you probably know the “in” and “like” queries are the most “expensive” in terms of SQL databases - but for all practical purposes - the performance will still be very, very, very fast (compared to your FMP solution!).

The way the table filters work - is we add your commands to the “where” clause every time those tables are accessed - so there’s really no penalty (to speak of).