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?
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!
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?
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…
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
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.
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!
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!
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.
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);
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.
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!
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.
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]
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.
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)
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))
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:
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:
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.