kabootit[Article] Valuelists management with SQL and html
by Riccardo Albieri
Morninger Soluzioni
Seroy provides the user a cospicous set of tools for valuelists management. Especially valuelists based on field values seem to be the trait d'union between efficiency and versatility. But how can a developer let the user be free to customize this kind of lists in an intuitive manner?
SQL + html: Dinamic Duo strikes back
The amazing ability to show the results of a SQL query in a properly formatted text field could gives us the help we need.
First thing: we have to create two text global fields ( gListNames and gListValues) and a valuelist (ValueList_Fields) set for showing custom values. Let's put the two globals on a form based on the table containing the field used by the value lists and set gListNames as a combobox using ValueList_Fields as valuelist; after that, set gListValues as non-editable html area.
Now, it's time to create the methods needed to manage the value lists. The first (Get_Field_Names) is the method that takes care of getting the names of all the fields present in the valueList table: it's a good idea to attach it to OnShow property, in order to get updated values every time the form is accessed.
//get a specific table and get column info
var jstable = databaseManager.getTable('yourdbname','valuelist');
var listvalues = jstable.getColumnNames();
//Fill a custom type valuelist with values from array(s) or dataset
//set display values (return values will be same as display values)
application.setValueListItems('ValueList_Fields',listvalues);
If the table contains fields that have to be omitted from the list (e.g.: a primary key), we'll need some extra code to strip those unwanted columns from the listvalues array (using a parsing procedure for instance).
Once you have all the columns listed, the next step to complete our suite of tools is a method that gets all the values present in the selected column.
In this case, good ol' SQL becomes very handy: with a simple query linked to that little jewel named getDatasetByQuery, SQL will give us all the values we need. Here's the code (Get_Column_Values):
globals.gListValues = ""
//Get a dataset based on query
var maxReturedRows = 1000;// choose a max number of items for the valuelist
var query = 'select valuelistid, ' + globals.gListNames + ' from valuelist where ' + globals.gListNames + ' != null order by ' + globals.gListNames +' asc'; // warning: certain dbs may require the expression NULL instead of null
var dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, null, maxReturedRows);
for(var i=0 ; i = dataset.getMaxRowIndex() ; i++)
{
dataset.rowIndex= i
//omits null values
var column2 = dataset[2]
if(column2 != null)
globals.gListValues = globals.gListValues + '<html><tr class="normal"><td><a href="javascript:Change_Item_Value('+"'"+dataset[2]+"'"+')">' + column2+'</a>></td></tr></html>'
}
As you can see, the code contains a link to a method (Change_Item_Value), which can be invoked directly from html. This method is intended to let the user easily modify or remove a value from the list. The code (Change_Item_Value):
var oldvalue = arguments[0]
var query = "select valuelistid from valuelist where " + globals.gListNames + " = '" + arguments[0] +"'"
var dataset = databaseManager.getDataSetByQuery (controller.getServerName(), query, null, 100);
var answer = plugins.dialogs.showQuestionDialog( "Liste valori", "Vuoi eliminare il valore " + oldvalue + " dalla lista valori " + globals.gListNames + "?", "Elimina", "Modifica", "Cancella")
if ( answer == "Elimina" )
{
controller.loadRecords(dataset)
controller.deleteRecord()
controller.saveData;
}
else
{
if ( answer == "Modifica" )
{
var newvalue = plugins.dialogs.showInputDialog('Modifica valore','Nuovo valore');
//updates the foundset
controller.loadRecords(dataset)
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
fsUpdater.setColumn(globals.gListNames,newvalue)
fsUpdater.performUpdate();
}
}
//refresh the value list items
Get_Column_Values();
In order to complete the suite, a last component is missing: the capability to add items to a value list.
Servoy offers several ways to accomplish this task; one of them could use the following code (Add_Item):
var selectedcolumn = globals.gListNames
var newvalue = plugins.dialogs.showInputDialog('Add a value to list ' + globals.gListNames ,'');
controller.newRecord()
forms.list_manager[selectedcolumn] = newvalue
forms.list_manager.controller.saveData();
controller.saveData();
Get_Column_Values();
Using this set of methods with a couple of globals and the powerful couple html + SQL it's possible to build a complete and reusable valuelists management system inside Servoy.
Have fun.
Comments (3)