How to create a value list from an SQL query in Servoy.

How to create a value list from an SQL query in Servoy.

One annoying limitation in Servoy, is the way you cannot filter a value list with an SQL query within the IDE. You can create a Relationship (‘Relations’ in Solution Explorer) and have that limited by another table, or even a global variable

But wouldn’t it be nice to just write some SQL to filter the records in some table and have the result of that appear in a value list? After all, some of the value lists we may need to create could require some very complex filtering.

Well, it is possible, but only in code.

And here’s how I achived it:

Here is the table I want to use for my value list.

And here are the table contents.

I want (as a test) to filter out all records from my value list, where field [user_id] is null – i.e.: the last three ‘test’ records.

Step 1: Create a dummy value list:

First I created a value list called vlst_tmp_customers_filtered that contains one manually entered dummy row of data.

Enter a dummy row of data into the list – we’ll replace these values in code later.

Warning: You can only replace an existing value list if it contains manually entered data (similar to above). Nothing happens if the value list you want to replace in code references a database table as it’s data source… And application.setValueListItems will not return an error to say it cannot change the value list, it will just quietly do nothing…

Step 2 – Setup the field element on the form that will display the value list:

I then set property valuelist for field element customer_id to our newly created value list vlst_tmp_customers_filtered.

Step 3: Create an onShow event to recreate the value list from a data set:

I then created an onShow event with code to change the values in vlst_tmp_customers_filtered to new ones generated by a JSDataSet data set.

 * Callback method for when form is shown.
 * @param {Boolean} firstShow form is shown first time after load
 * @param {JSEvent} event the event that triggered the action
 * @private
function onShow(firstShow, event) {
    // Create a data set that contains the filtered version of [customers] we want to show in our value list.
    var qry = "SELECT firstname + ' ' + secondname, id FROM customers WHERE user_id IS NOT NULL";
    var ds = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), qry, null, -1);

    // Dump out result of query.
    // Replace value list with the filtered data set above.
    application.setValueListItems('vlst_tmp_customers_filtered', ds);

And that’s it!

A combo box style drop down list where the list is generated from an SQL query! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *