# Valuelist of text returning integer **URL:** \ **Category:** Classic Servoy\ **Created:** [August 16, 2004, 10:10pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492 "2004-08-16T22:10:32Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![john.allen](https://avatars.discourse-cdn.com/v4/letter/j/dec6dc/32.png) [@john.allen](https://forum.servoy.com/u/john.allen)\ **Post date:** [August 16, 2004, 10:10pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/1 "2004-08-16T22:10:32Z")
If one has a field defined as integer in the backend database and one wants to attach a valuelist to it with the selections for the user in ‘text’ but returning integers to the back end database will that work? I imagined I could define the valuelist selections as, for example, ‘positive internal control | 0’ or ‘positive external control | 2’ and that would work, showing the first part to the user but then returning the second part after the ‘|’ to the database. But it doesn’t seem to. It shows the values with a combobox but doesn’t return anything to the database. The value stays as null and nothing is displayed in Servoy. The output using stacktrace returns: > Format Error in field pos\_cntrl null java.lang.IllegalArgumentException: Cannot format given Object as a Number Is there a way to accomplish this just within the Valuelist setup? ---
**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\ **Post date:** [August 17, 2004, 7:00am UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/2 "2004-08-17T07:00:13Z")
The way you do it as as a custom valuelist, it is TEXT! Also the 0 and 1 is text in this case! If you have a seperated table with for example: valuelistid INTEGER valuename TEXT than base your valuelist on this table and show the TEXT and return the INTEGER, and it than it works! ---
**Author:** ![john.allen](https://avatars.discourse-cdn.com/v4/letter/j/dec6dc/32.png) [@john.allen](https://forum.servoy.com/u/john.allen)\ **Post date:** [August 17, 2004, 7:18am UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/3 "2004-08-17T07:18:31Z")
I’m sorry. I didn’t explain it very well. I realize that I could attach the valuelist to another table, show the text value from one column and return the integer value from another column. But I was hoping within the ‘custom value list’ there was a way to return the part after the separator directly as an integer. But I guess it automatically treats it as ‘text’ and there is no way around it. I will probably instead just get the ‘integer’ value via a method on inserting/updating the column. Thanks for your help! John ---
**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\ **Post date:** [August 17, 2004, 7:25am UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/4 "2004-08-17T07:25:53Z")
I did some testing myself and it works also with a custom valuelist, the way you describe! Which version are you using of Servoy? which database? ---
**Author:** ![john.allen](https://avatars.discourse-cdn.com/v4/letter/j/dec6dc/32.png) [@john.allen](https://forum.servoy.com/u/john.allen)\ **Post date:** [August 17, 2004, 3:24pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/5 "2004-08-17T15:24:36Z")
Very interesting! I am using 2.1rc3 on OS X 10.3. The backend database is MS SQL Server although I have also tried it unsuccessfully with ASA Anywhere after hearing your reponse. I have a column ‘pos\_cntrl’ defined as Integer. I have attached a valuelist ‘pos\_cntrl’ with these values: positive internal control|1 positive external control|2 no positive control|0 I have attached the valuelist to the column which displays in a portal and use the display type of ‘Combobox’. On selecting the column the valuelist shows correctly but on selecting a value nothing gets accepted to the database with the error described earlier. But for you it works? Any thoughts as to what I am doing wrong? ---
**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\ **Post date:** [August 17, 2004, 7:20pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/6 "2004-08-17T19:20:43Z")
as HJK also tested, this should work fine. Can you send a simple example to me so that i can see what you or servoy does wrong in youre case? ---
**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\ **Post date:** [August 17, 2004, 7:41pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/7 "2004-08-17T19:41:43Z")
just a question, something that happened to me once: do you have a calculation of the same name defined on that table? I mean does something else just ovewrite your value… ---
**Author:** ![john.allen](https://avatars.discourse-cdn.com/v4/letter/j/dec6dc/32.png) [@john.allen](https://forum.servoy.com/u/john.allen)\ **Post date:** [August 17, 2004, 8:35pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/8 "2004-08-17T20:35:54Z")
Hi, I have attached a simple example here. The field valuelistInt is defined as an integer in the table valuelist (the server is user\_data). That field has a display of combobox and a valuelist called valuelistint. The custom values are ‘x|0’, ‘y|1’ and ‘z|2’. The x, y and z display but on selecting one of them, the corresponding values 0, 1 and 2 are not returned to the database (with the error as described earlier). John P.S. There isn’t a calculation that could be causing any problems ---
**Author:** ![Harjo](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/harjo/32/4873_2.png) [@Harjo](https://forum.servoy.com/u/Harjo)\ **Post date:** [August 17, 2004, 8:39pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/9 "2004-08-17T20:39:40Z")
You did’nt attached the example! ---
**Author:** ![john.allen](https://avatars.discourse-cdn.com/v4/letter/j/dec6dc/32.png) [@john.allen](https://forum.servoy.com/u/john.allen)\ **Post date:** [August 17, 2004, 9:14pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/10 "2004-08-17T21:14:27Z")
I will try again! Actually I see I am getting this message in the browser: Upload Error: Could not upload Attachment to ./files/valuelists.servoy. I don’t know why. I have tried both Safari and IE. ---
**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\ **Post date:** [August 17, 2004, 9:55pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/11 "2004-08-17T21:55:40Z")
when using different display then realvalues as you do with youre custom valuelist the combo must be NON editable. Because it is of no use that you can type in a field that has a completely different return value to the database which you can’t even see. ---
**Author:** ![john.allen](https://avatars.discourse-cdn.com/v4/letter/j/dec6dc/32.png) [@john.allen](https://forum.servoy.com/u/john.allen)\ **Post date:** [August 17, 2004, 10:39pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/12 "2004-08-17T22:39:04Z")
OK! Thanks a lot because I don’t think I would have ever figured that out on my own! Might be something to include in the documentation concerning value lists sometime as it doesn’t seem obvious, at least to me! ![:o]( "Surprised") ---
**Author:** ![mnorman](https://avatars.discourse-cdn.com/v4/letter/m/87869e/32.png) [@mnorman](https://forum.servoy.com/u/mnorman)\ **Post date:** [August 18, 2004, 4:11pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/13 "2004-08-18T16:11:00Z")
> john.allen: > OK! Thanks a lot because I don’t think I would have ever figured that out on my own! Might be something to include in the documentation concerning value lists sometime as it doesn’t seem obvious, at least to me! ![:o]( "Surprised") John, Thank you for the excellent suggestion. Servoy 2.1 online Help, Servoy Developer Edition 2.1 Volume 1: User’s Guide, and the corresponding pdf file have been updated to include the following information - added as a note in the section, Types of valuelists, Custom values: IMPORTANT: When using a pipe “|” character in a custom value list to display data that is different than what is read into the backend database, make sure that the “editable” property checkbox is unchecked in the field Properties panel. For more information on design time properties, see Servoy concepts; and for more detail about field properties, see the section on Fields. * * * Marc Norman Servoy ---
**Author:** ![john.allen](https://avatars.discourse-cdn.com/v4/letter/j/dec6dc/32.png) [@john.allen](https://forum.servoy.com/u/john.allen)\ **Post date:** [August 18, 2004, 11:10pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/14 "2004-08-18T23:10:09Z")
As a follow up to this I am wondering about a similar problem I am now having with a global field and value list. I am running the following code to first display an ‘on-the-fly’ value list for the user to select from and then to store their selection as a variable to pass on to a stored procedure. The query runs fine directly but for some reason, instead of returning two columns it only seems to return one. At first I thought it was a similar problem where the user sees the first column but the second column (which is the value that should be ‘stored’) is not being kept. However when I use the debugger and copy and paste the output of the ‘dataset’, only the first column (‘label’) is being selected. There is no sign of the second column values (‘rec\_id’) which are the values I really want to get for the later stored procedure. I have done a lot with the databaseManager but normally I am just getting one pk value to populate a form. But the code seems pretty straightforward for this and I can’t see anything I am doing wrong. Any suggestions? Running on OS X 10.3 Servoy 2.1rc3 Also do I need to set the valuelist back to null after using it for the user? I tried to do it with application.setValueListItems(‘v\_rec\_idVL’,‘’,‘’) but that didn’t seem to work. On going to ‘Designer’ Mode it would appear that the value list is not kept but would like to know for sure if there are potential problems. > var maxReturedRows = 100; > var query = "SELECT label, rec\_id " + > "FROM vwsu\_spec\_block\_info " + > "WHERE accession\_id = 2118532 " + > “ORDER BY sort\_order”; > var dataset = databaseManager.getDataSetByQuery(‘powerpath\_test’, query, null, maxReturedRows); > var labelArray = dataset.getColumnAsArray(1); > var rec\_idArray = dataset.getColumnAsArray(2); > application.setValueListItems(‘v\_rec\_idVL’,labelArray, rec\_idArray); ---
**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\ **Post date:** [August 20, 2004, 8:51am UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/15 "2004-08-20T08:51:31Z")
This really works fine. Just set a breakpoint on application.setValueListItems and look at youre labelArray and rec\_idArray You see the names in the labelArray and the id’s in the rec\_idArray. ---
**Author:** ![john.allen](https://avatars.discourse-cdn.com/v4/letter/j/dec6dc/32.png) [@john.allen](https://forum.servoy.com/u/john.allen)\ **Post date:** [August 20, 2004, 9:30am UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/16 "2004-08-20T09:30:21Z")
> jcompagner: > This really works fine. > Just set a breakpoint on application.setValueListItems and look at youre labelArray and rec\_idArray > You see the names in the labelArray and the id’s in the rec\_idArray. I’m sorry I just seem to be missing something really obvious here but I can’t figure out what - maybe it is just too late at night! 1. When I look at the contents of the var ‘dataset’ using the debugger there is only the contents of the first column of the ‘select’ statement in it. Nor is there anything in ‘var rec\_idArray = dataset.getColumnAsArray(2);’ which I guess is to be expected if there isn’t a second column of data in ‘dataset’. But I think I have followed the examples exactly from the manual. 2. what you do you mean by ‘set a breakpoint’ on application.setValueListItems? I don’t see anything about that. Sorry to be so thick… What am I doing wrong? John P.S. I also emailed to you yesterday a similar example/solution I did using the ‘crm’ solution as an attachment[/i] ---
**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\ **Post date:** [August 20, 2004, 9:44am UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/17 "2004-08-20T09:44:08Z")
1\> The dataset just displays the first column in the debugger (it just print the first column), But it holds everything you select. 2\> Just set a breakpoint on applications.setvaluexxxx and if the debugger breaks just look at the 2 array objects you made by: var companiesidArray = dataset.getColumnAsArray(1); var compNameArray = dataset.getColumnAsArray(2); then if you collapse the nodes you will see that the id array contains all the numbers and the name the names. ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [November 11, 2004, 10:08pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/18 "2004-11-11T22:08:11Z")
application.setValueListItems(‘memberid’, new Array(‘a’,‘b’,‘c’), new Array(1,2,3)) i have tried this (an example from your documentation). it displays a b c in the value list drop down ok but does not assign the corresponding values 1 2 3 back into the form field??? i.e. from this example if i select b it should assign 2 to the form field - yes? i like others are selecting 2 cols in ms sql statement. the first col is the name and the second col is the id - i want to show the name in the value list drop down - but assign the id to the form field. why does this not work? ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [November 11, 2004, 11:17pm UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/19 "2004-11-11T23:17:23Z")
In addition to my previous comment, here is an extract from the method code. var maxRows = 100 var query = “select (firstname + ’ ’ + lastname), id from dbo.Members with (nolock) where leaderId = " + memberId + " order by firstname, lastname” var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxRows) var labelArray = dataset.getColumnAsArray(1); application.output(‘labelArray=’+labelArray) var idArray = dataset.getColumnAsArray(2); application.output(‘idArray=’+idArray) application.output(‘recs=’+dataset.getMaxRowIndex()) dataset.rowIndex = 1 application.output(‘1=’+dataset[1]) if (dataset.getMaxRowIndex() \> 0) { application.setValueListItems(‘memberid’, labelArray, idArray) } The above is supposed to work but it does not assign the id to the form field. I have also tried these examples but they do not work either? application.setValueListItems(‘memberid’, new Array(‘a|7’,‘b|8’,‘c|9’)) application.setValueListItems(‘memberid’, new Array(‘a’,‘b’,‘c’), new Array(1,2,3)) Can someone please offer some help on this! ---
**Author:** ![john.allen](https://avatars.discourse-cdn.com/v4/letter/j/dec6dc/32.png) [@john.allen](https://forum.servoy.com/u/john.allen)\ **Post date:** [November 12, 2004, 1:42am UTC](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492/20 "2004-11-12T01:42:32Z")
Hi, In fact it is easier than that. All you have to do is have the two items you want to be in your value list (1st item the one you want to ‘show’, the 2nd item the one you want ‘returned’) be the same two items that you are ‘selecting’ in your query. So for example I have this as my query in MS SQL Server: ```auto var q_Stains = "SELECT code, id AS stain_id FROM vw_tmplt_procedure_ipox " + "where ((acc_categories is null) or (charindex('S', acc_categories) > 0)) and " + "(active = 'Y') and ((id <> -1) or (profile_type <> 'T')) AND ((code LIKE '%')) " + "ORDER BY code"; var Stains = databaseManager.getDataSetByQuery(globals.g_server, q_Stains, null, maxNoStains); ``` In the above query, the first ‘select’ item - “code” - is what I want my my users to see in the value list and the second ‘select’ item - “id” - is what will actually be returned to the column simply like this: ```auto application.setValueListItems( 'stainOrdering', Stains); ``` So the valuelist name is “stainOrdering” and the valuelist itself is the dataset which in this case I have called “Stains”. In the description of the valuelist it is simply described as a Custom Valuelist of course as you have done. You don’t have to redefine it as an array per se as the ‘dataset’ is already an array. You might have to give a name to your concatenated first column (select NAME=first + last) although I doubt it. Haven’t tried it though. Likewise, I don’t think the space you have between the first and last names would cause a problem but again I don’t know for sure. You can just experiment. But the above for sure works as we are using it in production already. HTH, John [Next page](https://forum.servoy.com/t/valuelist-of-text-returning-integer/2492.md?page=2)