multiple addTableFilterParam

Questions, tips and tricks and techniques for scripting in Servoy

multiple addTableFilterParam

Postby lcr159 » Sun Feb 05, 2006 8:46 pm

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.
lcr159
 
Posts: 64
Joined: Wed Mar 10, 2004 3:36 pm

Postby patrick » Sun Feb 05, 2006 9:06 pm

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 .
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Postby ngervasi » Fri Mar 03, 2006 7:09 pm

Maybe you can do it if you sue the operator "IN". Just from the top of my head...
Nicola Gervasi
sintpro.com
SAN Partner
ngervasi
 
Posts: 1485
Joined: Tue Dec 21, 2004 12:47 pm
Location: Arezzo, Italy

Postby patrick » Fri Mar 03, 2006 7:14 pm

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...
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Postby ngervasi » Fri Mar 03, 2006 7:17 pm

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...
Nicola Gervasi
sintpro.com
SAN Partner
ngervasi
 
Posts: 1485
Joined: Tue Dec 21, 2004 12:47 pm
Location: Arezzo, Italy

Postby patrick » Fri Mar 03, 2006 7:19 pm

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
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Postby ngervasi » Fri Mar 03, 2006 7:20 pm

Thanks Patrick, I suspected it wasn't working. Too bad, it would be really nice.
Nicola Gervasi
sintpro.com
SAN Partner
ngervasi
 
Posts: 1485
Joined: Tue Dec 21, 2004 12:47 pm
Location: Arezzo, Italy

Postby bubba » Wed Jun 21, 2006 9:45 pm

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
bubba
 
Posts: 227
Joined: Thu Aug 19, 2004 5:42 pm
Location: Bath, PA

Postby john.allen » Mon Aug 07, 2006 10:50 pm

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?
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Postby grahamg » Fri Apr 20, 2007 12:18 am

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)
grahamg
 
Posts: 752
Joined: Fri Oct 03, 2003 3:15 pm
Location: Midlands UK

Postby john.allen » Fri Apr 20, 2007 12:38 am

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... :oops:
Would that work for you?
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Postby grahamg » Fri Apr 20, 2007 1:18 am

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
grahamg
 
Posts: 752
Joined: Fri Oct 03, 2003 3:15 pm
Location: Midlands UK

Postby john.allen » Fri Apr 20, 2007 1:55 am

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).
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Postby grahamg » Fri Apr 20, 2007 9:29 am

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
grahamg
 
Posts: 752
Joined: Fri Oct 03, 2003 3:15 pm
Location: Midlands UK

Re: multiple addTableFilterParam

Postby davidaarong » Tue Nov 04, 2008 3:09 am

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)
davidaarong
 
Posts: 70
Joined: Wed Jan 10, 2007 12:43 am

Next

Return to Methods

Who is online

Users browsing this forum: No registered users and 5 guests