addFoundSetFilterParam

[mparsons]

Hi,

I am trying to use addFoundSetFilterParam using a variable as the value. It doesn't seem to work. Is it possible to use a variable? Or does it only accept strings?

Here is my code:

controller.addFoundSetFilterParam('biz_manager_id', '=', globals.gUserID)

The foundset returns no records. Thanks in advance for your help.

Thank you,
Matt

[rgansevles]

Matt,

The controller.addFoundSetFilterParam() call also supports numbers and other types.
Did you check the boolean result and call loadAllRecords() to make the filter effective (see the sample code for foundset.addFoundSetFilterParam)?

Btw, controller.addFoundSetFilterParam() is deprecated, the preferred way is foundset.addFoundSetFilterParam()

Rob

[paulc]

Does this functionality apply to related tables?

I am able to add the filter when the solution loads, (i've tried both of the following both return success as true)
success = databaseManager.addTableFilterParam(server, null, 'dimension', 'IN', args, 'filter1') ;
success = databaseManager.addTableFilterParam(server, 'seis_line', 'dimension', 'IN', args, 'filter1') ;


To see if the filter is being applied I run a find. At the time of search, I can see that the following returns an array of numerous records from all of the tables that contain "dimension", or just the one item in the array if I specify the table name.
databaseManager.getTableFilterParams(server)

The datasource of the form is "SEIS_SET". I have fields being displayed which are based on a 1-to-1 relationship of "SEIS_SET-to-SEIS_LINE".

Should the filter parameter not filter those records for me or is this functionality only applicable to the source table (as stated in the Servoy Wiki: "Adds a filter to all the foundsets based on a table.")?

Servoy Version: 5.2.7 - build 1013
Windows XP/7
Firefox Version 3.6.16

[rgansevles]

Paulc,

If databaseManager.addTableFilterParam(server, 'seis_line', 'dimension', 'IN', args, 'filter1') returns true, all queries that touch the seis_line table will add a condition 'where dimension in (args)'.
That makes all other records in that table invisible for that client session.

This is not a filter on related tables, If you search only in seis_set the filter does not apply.
But if you do a related search or related sort over relation SEIS_SET-to-SEIS_LINE, the filter will be used in the join that is created.

Hope this helps,
Rob

[eKelman]

when using databaseManager.addTableFilterParam is there some other method I need to execute for it to become active?

var success = databaseManager.addTableFilterParam(server, 'tableNameLowerCase, 'column1', '=', null);

i know the addFoundSetFilterParam() requires a clear() or loadAllRecords but since I am looking to apply this solution wide, how do I go about activating the filter?

Does it matter if I am using form.controller.search()?

I am also using the switch database after a user logs in, but am applying the filter after the database connection has been switched. Could this be the issue?

would someone be able to provide some working examples of this particular functionality?



Thanks

[rgansevles]

eKelman,

The filter will be active immediately.
Even tables you are viewing when you add the filter will be refreshed with the filtered data.

You say you are using switch_server.
Note that the filter is transparent to switch_server which means that you always use the original server name, not the switched-to server name:

databaseManager.addTableFilterParam('crm', 'customers', 'column1', '=', null)

and

databaseManager.switchServer('crm','crm1')

Can be called in any order.

Rob

[eKelman]

Thank you rgansevles

That helped and works for a single filter parameter.

One last question I have is how do I go about applying multiple filter values for a column

For example. I have to filter a table based on active_ind. The values can be 'Y', 'N', null

The values I need to filter are where active_ind = 'Y' or active_ind is null.

How would I go about doing that?

I have tried the following, but none of the following provide the correct results.
var args = new Array('Y', null);
success = databaseManager.addTableFilterParam(server, tableName, 'active_ind', 'IN', args, 'active');
and
success = databaseManager.addTableFilterParam(server, tableName, 'active_ind', '!=', 'N', 'active');

I have also tried the subquery method which works, but i think performance wise, this is going to be a killer. Is there any other way to do this?


Thanks

[rgansevles]

The values I need to filter are where active_ind = 'Y' or active_ind is null.

Ernest,

Try this:

Code: Select all

success = databaseManager.addTableFilterParam(server, tableName, 'active_ind', '^||=', 'Y', 'active');

We have 2 modifiers you can use for an operator:
^|| or-null
# case-insenstive

Rob

[eKelman]

Hi Rob,

That worked for me.

Thanks for all of your help!!

Cheers

[bobcusick]

Hey Rob,

The tooltip/sample for this is:

Code: Select all

databaseManager.addTableFilterParam( server_name,  table_name,  dataprovider,  operator,  value,  [filter_name])

Great solution! Are you saying that "value" can take a comma-separated list of values?

Does that also mean the the "name" of the param will be the last item?

Shouldn't you be able to add 2 parameters like this as well (using paulc's sample above):

Code: Select all

databaseManager.addTableFilterParam(server, tableName, 'active_ind', '^||=', 'Y', 'Filter1');
databaseManager.addTableFilterParam(server, tableName, 'active_ind', '^||=', 'Y', 'Filter2');

That way you would have a filter called Filter1 and one called Filter2 that you could disable?