Populating valuelists at runtime

Classic Servoy
1

How can I narrow the contents of valuelists at runtime? It seems arrays are one possibility, but I have found no documentation on how to do it. I want to show a list of customers based on one or several criteria, and I don’t want to have to depend on the form’s record source. Dynamic combo boxes are a very useful and important tool that I use thoroughly in all my apps, I really need as much runtime control as possible of the values in these objects.

2

Have a look at: http://forum.servoy.com/viewtopic.php?t=618&highlight=

And here’s some code:

var maxReturedRows = 3;
var query = ‘select company_name, id from companies’;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);
var nameArray = dataset.getColumnAsArray(1);//put 1st column in array
var idArray = dataset.getColumnAsArray(2);//put 2nd column in array

application.setValueListItems( valuelistName, nameArray , idArray )

Paul

3

That works well, thank you. However, I am thinking now that if I use this technique heavily and then we decide to migrate to a different SQL server, we may encounter the typical problems linked to client-server apps, that of applications breaking down because of incompatible SQL languages. Is there any way to bypass having to use native SQL code? Ideally, the design screen for valuelists would include some interaction with global variables or objects to avoid having to create these queries.

4

Offcourse the incompatible SQL languages can be an issue, but simple select… from… where… statements can be written in a standard way, so they work on pretty much every SQL DB.

Offcourse you do not have to fill the arrays by running an SQL query. If you have the values you want in the ValueList available in memory somehow, you can put them in as well.

You can store the values you want into one field in a DB record, which the (super) user can edit and you dynamically load into the ValueList etc.

Many different options… As long as you can get the values into an Array, you can do what you want.

Paul

5

The combobox does not clear previous values when the latest query returns no records. I have tried to clear them by using null values as in

application.setValueListItems(‘Entities’, nameArray , idArray )

where the two arrays are null, and also

application.setValueListItems(‘Entities’, null, null)

but the only way so far to reset the values is with a successful query. How can I clear the valuelist without having to run a query first?

thanks

6

Mmm, good question. Maybe create the arrays with one entry and the entry being ‘’?

If that doesn’t work, I wouldn’t know. Then it’s up to the Servoy people :-)

Paul

7

Thanks, Paul. That works. I tried it before and failed because I used two columns, one text and one numeric, and the arrays have to be initialized to empty string and 0 instead of just empty strings as I did on the first attempt.