# using addTableFilterParam with IN

**URL:** <https://forum.servoy.com/t/using-addtablefilterparam-with-in/6276>\
**Category:** Classic Servoy\
**Created:** [July 26, 2006, 2:36pm UTC](https://forum.servoy.com/t/using-addtablefilterparam-with-in/6276 "2006-07-26T14:36:15Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![sbutler](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/sbutler/32/4393_2.png) [@sbutler](https://forum.servoy.com/u/sbutler)\
**Post date:** [July 26, 2006, 2:36pm UTC](https://forum.servoy.com/t/using-addtablefilterparam-with-in/6276/1 "2006-07-26T14:36:15Z")

</div>

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…

```auto
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

```auto
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?

---

<div class="post-metadata">

**Author:** ![sbutler](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/sbutler/32/4393_2.png) [@sbutler](https://forum.servoy.com/u/sbutler)\
**Post date:** [July 26, 2006, 7:15pm UTC](https://forum.servoy.com/t/using-addtablefilterparam-with-in/6276/2 "2006-07-26T19:15:50Z")

</div>

Update…  
I’ve notices that this works:

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

```

But this does not

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

```

Is this a bug I should report?

---

<div class="post-metadata">

**Author:** ![bcusick](https://avatars.discourse-cdn.com/v4/letter/b/ed8c4c/32.png) [@bcusick](https://forum.servoy.com/u/bcusick)\
**Post date:** [July 27, 2006, 1:29pm UTC](https://forum.servoy.com/t/using-addtablefilterparam-with-in/6276/3 "2006-07-27T13:29:44Z")

</div>

Scott,

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

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

```

---

<div class="post-metadata">

**Author:** ![sbutler](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/sbutler/32/4393_2.png) [@sbutler](https://forum.servoy.com/u/sbutler)\
**Post date:** [July 27, 2006, 4:07pm UTC](https://forum.servoy.com/t/using-addtablefilterparam-with-in/6276/4 "2006-07-27T16:07:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sbutler](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/sbutler/32/4393_2.png) [@sbutler](https://forum.servoy.com/u/sbutler)\
**Post date:** [July 28, 2006, 1:57pm UTC](https://forum.servoy.com/t/using-addtablefilterparam-with-in/6276/5 "2006-07-28T13:57:17Z")

</div>

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?

```auto
/ ******************************************
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?

---

<div class="post-metadata">

**Author:** ![bcusick](https://avatars.discourse-cdn.com/v4/letter/b/ed8c4c/32.png) [@bcusick](https://forum.servoy.com/u/bcusick)\
**Post date:** [July 29, 2006, 4:25pm UTC](https://forum.servoy.com/t/using-addtablefilterparam-with-in/6276/6 "2006-07-29T16:25:01Z")

</div>

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).