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
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: Pledge for more power to the developer :) - Classic Servoy - Servoy Community
Thanks Patrick, I suspected it wasn’t working. Too bad, it would be really nice.
ngervasi:
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
'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:
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
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
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:
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:
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?
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:
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).
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:
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)
Assuming IN operator is supported in databaseManager.addTableFilterParam and so I do not need a previous Select statement, I tried the following :
var success = databaseManager.addTableFilterParam(‘cotizaciones’, ‘clientes’, ‘provincia’, ‘IN’, (1,6), ‘listaPcias’)
and also
var success = databaseManager.addTableFilterParam(‘cotizaciones’, ‘clientes’, ‘provincia’, ‘IN’, (6,1), ‘listaPcias’)
in both cases it shows the records that match the last parameter mentioned in the IN list (in the first example it shows records in which provincia is = 6; in teh second, those in wich provincia is = 1).
I did not find anything in the documentation to clarify how to properly write the IN list.
I am using 4.1 beta 4 .
That didn’t work.. too many arguments.
Any other ideas?
-David
This is how I setup multiple filters using the IN operator, it should give you a good starting point:
// Add table filters
var filter = arguments[0]
var _query = 'select partner_filters.id_partner from partner_filters where partner_filters.parent_id = '+ filter;
// Filter the partners table
var _success = databaseManager.addTableFilterParam(controller.getServerName(), 'partners', 'partners_id', 'IN', _query);
if(!_success) return 1;
// Filter the contacts table
var _success = databaseManager.addTableFilterParam(controller.getServerName(), 'contacts', 'id_partner', 'IN', _query);
if(!_success) return 2;
// Filter the loginslog table
var _success = databaseManager.addTableFilterParam(controller.getServerName(), 'login_logs', 'id_partner', 'IN', _query);
if(!_success) return 3;
Finally I made it work :
var success = databaseManager.addTableFilterParam(‘cotizaciones’, ‘clientes’, ‘provincia’, ‘IN’, ‘select provincia_id from provincias where provincia_id IN (1,6)’, ‘listaPcias’)
When the operator is “IN” , it seems that the value is the result of a query (that can be prepared previously, as Nicola showed, or written as above).
My question is : is there a way not to use a query result as the value argument in the case of operator IN ?
Thx Achiary, but it’s still too many arguments for Servoy 3.5.
And I can’t seem to get ngervasi solution to work either.
So to start again: I need to limit records based on a field (jobcode) for a particular user (larivera).
the jobcode i want viewable are: 3440,4958,3465,4959,0000 for user name larivera.
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:
SELECT emplid,jobcode FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')
So my question remains: how can i pass addTableFilterParam a list of parameters as opposed to just one in Servoy 3.5?
-David