set valuelist method

[pbakker]

Hi,

Is there anyone who played around with this function?

What I'm trying to do is do a getdatasetbyquery, which gets met an ID and the matching description, per record. I would like the description to show in the valuelist dropdown and the ID to be writen into the dataprovider. Does anyone know if this can be done?

If not, can this be put on "the list"?

Maybe it's an idea to have the option in the screen where you define a valuelist, to enter your own sql statement?

[maarten]

try this

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 )

note:
your valuelist should be of type "custom" valuelist.

[pbakker]

Tnx.

Maybe an idea to put this in the documentation. BTW: in the Help in Servoy, in the explanation for this function, there is a strange "/n" in the code for filling a valuelist by a query...

[maarten]

Hi Paul,

Seems that the "\n" shouldn't be there.
I'll ask our editor if he can sqeeze in above example.


Thanks for feedback.

[RobertMeyer]

What I'm trying to do is do a getdatasetbyquery, which gets met an ID and the matching description, per record. I would like the description to show in the valuelist dropdown and the ID to be writen into the dataprovider.

i'm trying to do the very same thing, but it does not
seem to work. the data stored in the backend is always
zero.

is this broken?

using: rc3 build 263
os-x 10.3

rm

[jcompagner]

above code should work fine.
what are you exactly doing?
What kind of field is using that valuelist, do you see the values in the combo? ect ect.

[RobertMeyer]

above code should work fine.
what are you exactly doing?
What kind of field is using that valuelist, do you see the values in the combo? ect ect.

the field in the database is a number (mysql:mediumint).

yes, i see the string values in the combo. when i select
one, and save the record, only zero is stored.

also, if i try to look at the current value of the combo
with a test function, the combo's value is always the
string, not the 'backend' number that i'm interested in.

perhaps i'm not understanding how to do this correctly?

i've also tried this with a custom valuelist, where i enter
the text for the combo, a vertical bar, then a number to
be stored, for each item in the list. this does not work
for me either.

[jcompagner]

if you see the string as a value
then you don't set the returnarray, you only have the display array (which is then also the value it will return.)

it sets to 0 because it can't convert youre string that you display to a number.

[RobertMeyer]

if you see the string as a value
then you don't set the returnarray, you only have the display array (which is then also the value it will return.)

it sets to 0 because it can't convert youre string that you display to a number.

i'm sorry; i'm not understanding what you mean.
if you could explain more, i'd be greatful; i'm working
on this code right now, and would like to get this to work.

thank you.
rm.

[jcompagner]

you really have to set both vallues
what you want to display and what you want to return

look at the code above:

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 )

so the nameArray is what will be displayed and the idArray what will be returned.
The name array should contain the Strings and the id array the Integers

[RobertMeyer]

The name array should contain the Strings and the id array the Integers

that's exactly what i did; the field in the backend database
is a number, which is what i want to store. this does not
work for me. the field in the database always gets a zero
value.

shall i send you the solution i put together? or would you
prefer to send me one that demonstrates how to do this?

thanks.

[jcompagner]

send me an example

[jcompagner]

the combobox can't be editable !!
because if it is editable then we can only insert the display value (so it has to be a string)

[RobertMeyer]

(so it has to be a string)

that made it work - changing the field datatype to a string.
however, that's not what i want to store. i'll be storing
large quantities of these, and i want them stored as
numbers, not strings

[jcompagner]

then make the combobox not editable..

the dataprovider can be an integer just fine.

fill youre valuelist with

a name (string) as display and id (integer) as return type

set the combobox non editable on a integer datatype.