Servoy Forum

Sorry to mislead you, my code has an error in it. I wrote it by hand and had SQL query format in my head.

The upshot is that the value parameter of the addTableFilterParam either needs to be one value (string or number) or an array if passing multiple values. So my code above corrected:

Code: Select all

var NestedIN = "SELECT jobcode FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')"
var values = databaseManager.getDataSetByQuery(controller.getServerName(), NestedIN, null, 10000000)
valuesArray = values.getColumnAsArray(1)
databaseManager.addTableFilterParam('rcs_billing_sharedcore', 'core_rso_emp', 'jobcode', 'IN', valuesArray)

Hey David, maybe you were typing in a hurry but beware of this:

Code: Select all

var values = databaseManager.getDataSetByQuery(controller.getServerName(), NestedIN, null, 10000000)

Always use -1 unless you are 100% sure that the query will give you a predefined amount of records back, I know it looks stupid but if in the future your query will return more than 10000000 records you'll be in deep trouble and debugging things like this can be a real nightmare.
So this is the correct code:

Code: Select all

var values = databaseManager.getDataSetByQuery(controller.getServerName(), NestedIN, null, -1)

Sorry to be so picky but it happened to a colleague of mine and we had our share of pain and lost one day to figure out what was wrong.

I guess that would make for a tough debugging job Great reminder.

Thanks David, for clarifying the format of 'value' as an array when the operator is 'IN', it is not in the manuals.

So I could make it work also in the following way :

Code: Select all

var arrayprov = new Array(1,6,15);
   application.output(arrayprov  )
   databaseManager.addTableFilterParam( 'cotizaciones',  'clientes',  'provincia',  'IN',  arrayprov)


So, when the IN values are already known (in my case provincias 1,6 and 15) I can fill an array and I do not need to perform a query.
I guess the query must be used when the result is unknown and depends on a certain condition.

Hi,

Any news on using relations in the addFilterParam() ?
Using pk_field in (select ...) performs (still) bad (no 200 records limit in subquery).

Or any other solution ?

Regards,

Hi,

I have logged a feature request for this as SVY-7063. Please vote for it

Thanks
Steve