custom valuelists and adding an empty value

Classic Servoy
1

Hello Everybody,

I`ve got a question about how to add an empty value to an array which I want to use in a custom valuelist.

Tried a lot of combinations but all of them seem to screw up the array format.

This is basicly the code I wanted to use.

var query = 'SELECT DISTINCT initials, usersid FROM users WHERE department LIKE(\'%it%\') AND users_status = 1 ORDER BY initials asc'
var dataset = databaseManager.getDataSetByQuery(forms.users.controller.getServerName(), query, null, 1000)
var Array_Initials = new Array(dataset.getColumnAsArray(1),'') //'' is supposed to add an empty value at the end
var Array_UsersID = new Array(dataset.getColumnAsArray(2),'')  //'' is supposed to add an empty value at the end
application.setValueListItems('Users_Initials_IT', Array_Initials, Array_UsersID)

See also:

Can somebody enlighten me?

With kind regards Rene

2

hmm. I have never tried something like

new Array(anArray, 'abc', 'def')

and don’t know if that works (JavaCript wise). What should work is

var query = 'SELECT DISTINCT initials, usersid FROM users WHERE department LIKE(\'%it%\') AND users_status = 1 ORDER BY initials asc'
var dataset = databaseManager.getDataSetByQuery(forms.users.controller.getServerName(), query, null, 1000)
var Array_Initials = dataset.getColumnAsArray(1)
Array_Initials[Array_Initials.length] = ''
var Array_UsersID = dataset.getColumnAsArray(2)
Array_UsersID[Array_UsersID.length] = 0 // or try: null
application.setValueListItems('Users_Initials_IT', Array_Initials, Array_UsersID)
3

Yo rene,

var query = 'SELECT DISTINCT initials, usersid FROM users WHERE department LIKE(\'%it%\') AND users_status = 1 ORDER BY initials asc'
var dataset = databaseManager.getDataSetByQuery(forms.users.controller.getServerName(), query, null, 1000)
var Array_Initials = dataset.getColumnAsArray(1),'');
Array_Initials.push('')
var Array_UsersID = dataset.getColumnAsArray(2)
Array_UsersID.push('');

Used both, never used the combination

hth

Bart

4

Thanks Guys,

The code Patrick gave works excellent.

The Code from Odysseus also returns an empty string but doesn`t save it to the database.

Patricks code however I dont understand.

Can you explain it to me?

var Array_Initials = dataset.getColumnAsArray(1)
Array_Initials**[Array_Initials.length]** = ‘’ :?:
var Array_UsersID = dataset.getColumnAsArray(2)
Array_UsersID**[Array_UsersID.length]** = 0 :?:

Thanks very much for your quick response Guys

Rene[/b]

5

At the point where you want to add your extra entries, you don’t know the index of the array (you need to know the last position). So you could do

var Array_Initials = dataset.getColumnAsArray(1)
// now you have an array of length x, where x is currently unknown
// if you want to add an item you need to know at what index
// to add an extra item, you could use push as noted by Odysseus
// or figure out the last position manually by doing
var arrayLength = Array_Initials.length
// since Arrays are zero based, you can now add an item using
Array_Initials[arrayLength] = ...
// What I wrote was just a shortcut without the extra variable arrayLength:
Array_Initials[Array_Initials.length] = ''
6

Odysseus code not working :roll:

unacceptable

Instead of pushing ‘’ in the idarrays, you probably need to push null

Database will expect integer probably

hth

7

Thanks Patrick,

I figured your code out myself at 4 AM this morning (clear head I think).
When the code was something like

Array_Initials[arrayLength + 1]

I think I understood it right away but since an array starts with 0 no need to increment it by one.

Odysseus,

Sorry to have offended you. (I don`t want to walk next time to SNUG meetings :wink: ).

After setting the usersID to null your code also workes like a charm.

For all others the working codes

//Patrick
var query = 'SELECT DISTINCT initials, usersid FROM users WHERE department LIKE(\'%it%\') AND users_status = 1 ORDER BY initials asc' 
var dataset = databaseManager.getDataSetByQuery(forms.users.controller.getServerName(), query, null, 1000) 

var Array_Initials = dataset.getColumnAsArray(1) 
Array_Initials[Array_Initials.length] = ''

var Array_UsersID = dataset.getColumnAsArray(2) 
Array_UsersID[Array_UsersID.length] = null

application.setValueListItems('Users_Initials_IT', Array_Initials, Array_UsersID)


//Odysseus
var query = 'SELECT DISTINCT initials, usersid FROM users WHERE department LIKE(\'%it%\') AND users_status = 1 ORDER BY initials asc' 
var dataset = databaseManager.getDataSetByQuery(forms.users.controller.getServerName(), query, null, 1000) 

var Array_Initials = dataset.getColumnAsArray(1)
Array_Initials.push('')

var Array_UsersID = dataset.getColumnAsArray(2) 
Array_UsersID.push(null)

application.setValueListItems('Users_Initials_IT', Array_Initials, Array_UsersID)

I think I have a slight preference for Odysseus code
(this might be a little pollitically founded.)

Thanks both for your explanation.

Rene