Value list should have option to put sql query

Classic Servoy
1

Hi all,

Whilw creating value list, there should be option for putting sql query, to fetch data.
It is possible by creating value list at run time by fetching data through query.
But this facility of putting a complex query at the time of creation of the value list will save time and will be easy to use

Regards,
Pradipta

2

Pradipta,

Have a look at application.setValueListItems() in combination with databaseManager.getDataSetByQuery()

Rob

3

Rob,

Please read the request of Pradipta again. He says he knows you can do it at runtime.

Lambert.

4

Hmmm, usually a complex query requires parameters and in my experience most of the time the parameters are not constants but variables that need to be evaluated at runtime, how would you handle that in the VL definition dialog?
If all the parameters you need are constants you can already define the valuelist using relations and global relations in the dialog.

5

I guess what would be nice is to be able to set a filter (i.e. the WHERE clause) on a valuelist. This way you don’t have to resort to using relations with globals.
Just a thought.

6

you could always create a feature request in our support system

7

I’m afraid if you make valuelists by query, that it will affect your performance, because it would execute each time you use the valuelist.

I use queries to fill most of my valuelists, using a dataset

var _id_array = _ds.getColumnAsArray(1)
var _desc_array = _ds.getColumnAsArray(2)

application.setValueListItems( 'myValuelist',  _desc_array, _id_array);

And I only refresh the valuelist in case that I think it is useful.
Data that is almost static , I load once using the onLoad of the form where it is needed. So during the time my application lives, this kind of valuelists are not reloaded again.

So even when it can be handy to have valuelists by query, I’m afraid from performance point of view, it would perhaps be better not to do it like this.

8

jcompagner:
you could always create a feature request in our support system

Feature request added under #244100 for the valuelist filter option.