Servoy Forum

Is it possible to filter records with the addTableFilterParam using more than one value? The user should see his own foundset AND a common foundset, same records for all users.

It is possible to set more than one filter (using AND). It is not, however, currently possible to create an OR-statement using this functionality. I have requested that several times, because I have the same problem. A user should see everything that is his or is public. There is only one column "user_id". If it is empty, it's a public record. So I would like to set a filter that does

user_id = xy OR
user_id IS NULL

But as far as I see it, you cannot do this right now using addTableFilterParam .

Maybe you can do it if you sue the operator "IN". Just from the top of my head...

Smart idea, but something like

Code: Select all

select id from table where user_id IN (4711, NULL)

doesn't work. Databases want IS NULL...

Sorry. A quick question: does addTableFilterParam also work on related foundsets? I'm looking trough the forum but can't seem to find an answer...

No. I have made at least 10 feature requests around the matter of foundsetFilters.

I have tried to describe how I'd like to see that work here: http://forum.servoy.com/viewtopic.php?t=3758

Thanks Patrick, I suspected it wasn't working. Too bad, it would be really nice.

ngervasi wrote:Thanks Patrick, I suspected it wasn't working. Too bad, it would be really nice.

Has anything in this scenario changed with the release of V3? Notably the ability to filter for a specific value OR Nulls? And the ability to provide filtering of records through relations?

Thanks
Lee

I think the way to extend the addTableFilterParam in a very simple way that would meet pretty much all of our requirements is simply to make the 'IN' operator be able to take the normal 'IN' parameters that it takes in regular SQL queries. By this I mean of course to be able to have a nested, subquery be used as in

Code: Select all

'SELECT id FROM messages WHERE id IN (SELECT message_id FROM user_messages where user_id = current_user_id'

So using the addTableFilterParam language it would be something like this:

Code: Select all

var current_user_id = security.getUserID()
var inQuery = 'SELECT message_id FROM user_messages WHERE user_id = ' + current_user_id
var success = ('admin', 'messages', 'message_id', 'IN', inQuery)

An 'IN' query is not as fast as a join but I think it should be simpler to implement within Servoy. In fact I'm not sure why I can't get this to work currently. I've played around with the syntax but I can't seem to get it to work. The good thing about this from Servoy's point of view though is that they wouldn't have to worry about having other tables in the FROM clause, making sure all the joins were correct, etc. Rather that any time that 'messages' table is accessed from anywhere there is simply an extra 'IN' subquery added to it. Shouldn't that work?

Searched the Forum and couldn't see if this idea was developed/adopted.

I'm trying and failing to set addTableFilterParams that will allow records to show if the required information is in one of two fields.

Have an addTableFilterParam that allows Sales staff to only see records where the Addr_Country is in their list of approved Countries

Code: Select all

   var vSuccess = databaseManager.addTableFilterParam('Server', 'contacts', 'addr_country', 'IN', vArray )

Client now wants the list Sales staff to also see Contacts where the POBox Country is in their list - so I tried adding a second line

Code: Select all

   var vSuccess = databaseManager.addTableFilterParam('Server', 'contacts', 'pob_country', 'IN', vArray )

Each line works correctly on its own, but having the two lines results in no Filtering at all. Tried changing the 1st line to show both dataproviders but results in no filtering:

Code: Select all

var vSuccess = databaseManager.addTableFilterParam('Server', 'contacts', 'addr_country, pob_country', 'IN', vArray )

Having successfully gone round in ever-decresing circles not sure which way is up - can anyone shed light please?

Thanks

Graham Greensall
Worxinfo Ltd

Version 3.1.4-build 408
Java version 1.5.0_07-87 (Mac OS X)

Hi Graham,

I think the way I outlined above actually does work. That is if you put a nested subquery that passes back the 'allowed' IDs that meet your criteria you can have as many different filters as will reasonably work. So in your case it would be something like:

Code: Select all

var NestedIN = "SELECT contacts_id FROM contacts
WHERE addr_country IN ('UK', 'USA', 'Germany')
OR pob_country IN ('Ireland', 'Ethiopia', 'Singapore')"
var vSuccess = databaseManager.addTableFilterParam('Server', 'contacts', 'contacts_id', 'IN', NestedIN)

I ran this by Jan Blok back in August 2006 and it was working as of 3.0rc2 as I tested it at the time. However for the world of me I can't remember which solution I had that working in...
Would that work for you?

Hi John

Many thanks for the suggestion - feel it's close but getting an error message 'Unknown column 'vArray' in 'where clause'.

vArray is an Array of Country Names built from a join table j_users_countries.

After some considerable trial & error using following real code - can you see an obvious mistake?

Code: Select all

      var NestedIN = "SELECT idct FROM contacts   WHERE addcountry IN vArray OR pob_country IN vArray"
      var vSuccess = databaseManager.addTableFilterParam('proptek04', 'contacts', 'idct', 'IN', NestedIN)

addFilterParam accepts the variable vArray, but the SQL query is choking on it - I've tried a variety of single/double quote markes, brackets etc.


Thanks again - hope you are coming to SW so that I can show gratitude in traditional manner


Graham

Hi Graham,

So I guess vArray is not an actual db column but an array built in Servoy? You don't need an array in this case you need the actual column. So if I understand it right, you have a table called j_users_countries and you want the table filter to be such that it only shows those contacts who countries are in that subset of countries linked to the current user. In other words I'm presuming you have a user_id column and a country column in j_users_countries. In this case your query and statement would be something like this:

Code: Select all

var NestedIN = 'SELECT idct FROM contacts  WHERE addcountry IN
(SELECT country FROM j_users_countries WHERE user_id = current_user) OR
pob_country IN
(SELECT country FROM j_users_countries WHERE user_id = current_user)'
var vSuccess = databaseManager.addTableFilterParam('proptek04', 'contacts', 'idct', 'IN', NestedIN)

It's quite possible I have some of the column names wrong of course so this might need some fiddling around. What I am calling 'current_user' is just to indicate that presumably this is limited by whoever the actual 'current user' is and that you would somehow use his actual user_id in that spot, whether from a global variable or whatever. You'll just need to get your quotations and all that going correctly of course but you can always check in the debugger what the contents of the 'NestedIN' is to make sure it is grammatically correct (in an SQL sense).

In a nutshell, I find the easiest way to work with any type of filter is to write the actual query that correctly limits or shows the data/IDs that I want. When you have that, then you just fill in the blanks with the Servoy variables. With Servoy now allowing the use of views instead of tables it makes it even more powerful and cool and you don't even necessarily need to use the filter parameter in many cases. (Although there are arguments pro and con, in many cases it makes more sense and it is faster to use a view to limit your data being displayed to the user).

Hi John

MANY thanks for this. En-route to visit a Client and couldn't get it to work first time but will do some tweaking later.

Masterclass much appreciated

All the best

Graham

I'm trying to do something similar.

I'm trying to limit records based on a field (jobcode) for a particular user (larivera).

Code: Select all

if (security.getUserName() == 'larivera'){
var NestedIN = "SELECT emplid FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')";
databaseManager.addTableFilterParam('rcs_billing_sharedcore', null, 'jobcode', 'IN', NestedIN);


I have this in a Global Method that launches at start up

However, no records appear. And yes, there are records in the DB -- it works just fine by running this sql query against the db:

Code: Select all

SELECT emplid,jobcode FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')


Any help would be appreciated!

Thanks,
David

Details:
Servoy Developer
Version 3.5.7-build 520
Java version 1.6.0_03-b05 (Windows XP)