two or more addTableFilterParam

Classic Servoy
1

I have the following two filters on a table for two separate columns, newccc and oldccc. The filter works when the newccc = searchccc new AND oldccc = oldccfilter. Is there any way to make the filters work if EITHER newccc OR oldccc meet the condition??

Thank you,
Rodrigo

if (SearchCCCnew > ’ ')
{
var success=databaseManager.addTableFilterParam(globals.cServername, ‘gains_loses’, ‘newccc’, ‘=’, SearchCCCnew, ‘newccfilter’);

}

if (SearchCCCold > ’ ')
{
success=databaseManager.addTableFilterParam(globals.cServername, ‘gains_loses’, ‘oldccc’, ‘=’, SearchCCCold, ‘oldccfilter’);
}

2

jaimerodrigo,

You cannot create a filter that says ‘where newcc = ? or oldcc = ?’

You can use the ornull-modifier (^||) in the filter condition:

if (SearchCCCnew > ' ')
{
   var success=databaseManager.addTableFilterParam(globals.cServername, 'gains_loses', 'newccc', '^||=', SearchCCCnew, 'newccfilter');
}

if (SearchCCCold > ' ')
{
    success=databaseManager.addTableFilterParam(globals.cServername, 'gains_loses', 'oldccc', '^||=', SearchCCCold, 'oldccfilter');
}

Which results in ‘where (newcc = ? or newcc is null) and (oldcc = ? or oldcc is null)’

Hope this helps,

Rob

3

Thank you Rob. It does answer my question, however it does not solve my issue. I tried using

var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), query, args, maxReturnedRows);

where query=’ select * from ctable where newccc=? or oldccc=?’
and args[0]=123 and args[1]=456

Unfortunately I can’t get dataset to replace the datasource. Any suggestions??

4

jaimerodrigo,

You can create a filter with a custom sql:

var success=databaseManager.addTableFilterParam(globals.cServername, 'gains_loses', 'cid', 'in', 'select cid from ctable where newccc=123 or oldccc=456', 'ccfilter');

Unfortunately,this does not support prepared statement arguments (the ?s), so beware of sql injection.

Alternatively, you can load the foundset with the custom query:

foundset.loadRecords('select cid from ctable where newccc=? or oldccc=?', [123, 456])

Rob

5

Rob,
It works. Thank you very much. Please explain what is the difference between the command you sent me, foundset.loadRoadrecords() and the one I was using, currentcontroller.loadRecords. After doing different selections, the resulting grid (foundset) changed when I clicked on the column headers to change the order…

With the It is now working corrrectly using foundset.loadRecords.
Thank you,
Rodrigo

eraseme1.jpg
eraseme1.jpg809×619 45.4 KB

6

jaimerodrigo,

Are you saying that when you load a table view via controller.loadRecords(sql) and you sort using a sort column the previous data (not from the sql) is shown?
That should not happen.

The difference between controller.xxx() and foundset.xxx() is that controller is about UI and foundset is about data.

Rob

7

Rob:
The data that should show DOES NOT SHOW consistently. I am attaching a similar test but selecting REG instead of CCC (I hope the screen shots are clear). The data that shows initially is for REG=‘EA’ whereas I requested SO to be selected instead. At the end you will see the code and the content of the data string that is being used in the query. I have used both controller.xxx and foundset.xxx with the same results. The table has approx. 290,000 rows.

eraseme.pdf (59.6 KB)

8

jaimerodrigo,

Clicking on the header should sort the foundset without changing it.
Maybe you can show it in a a very small sample (1 form, few records).

Rob

9

Rob,
I created a new form that accepts the selection criteria and executes a formset.loadRecords(). After making several different selections, the same problem pops up. Clicking the column headers fixes the view. I reduced the size of the table from 290K to 3000 records.
Can this be a bug??
Rodrigo