I have a valuelist that currently displays a persons first and last name (both are their own fields from the DB). so in my valuelist definition i have Show in Field / List checked and name_first and name_last selected in the first two boxes of my Definitions in the Define Valuelists window. the last has the field for the id of that person selected and Return In Dataprovider checked so my dataprovider knows what id im working with. what i want to do though is display another field in the valuelists so when the user types in the persons name they see first and last name displayed and the organization they’re associated with at the end (in case we have 3 john smiths, they know which is which). However, I only see the three selection areas in the definitions window. Is there another way to go about this because i dont see a place to add calculated fields into the definitions section.
Thanks for the solution, I also suffer from this 3 column limitation. Would it be possible to put more than 3, may be 5 columns for a value list as a feature request? To have the columns is quite much easier than to program around this limitation.
Thanks a lot and best regards, Robert
maarten:
you could make your calculation that’s concatenating strings STORED, in order to make it selectable in the valuelist dialog.
you can fill your valuelist using custom queries. This will allow you to basically put anything inside your valuelist.
I had a situation where I needed this type of functionality as well. SO, what I did was return a delimited string as the return value.
Example:
DisplayItem|val1;val2;val3;val4;val5
I did a SQL query to load the display value, and then “built” the return value string using other columns in the (complex) query. Then on the onDataChange event (where the user can choose an option) - I was able to parse the data stored in the field.
I had a situation where I needed this type of functionality as well. SO, what I did was return a delimited string as the return value.
Example:
DisplayItem|val1;val2;val3;val4;val5
I did a SQL query to load the display value, and then “built” the return value string using other columns in the (complex) query. Then on the onDataChange event (where the user can choose an option) - I was able to parse the data stored in the field.
I hope you are well and all day in Servoy development .-) Thanks for your ideas and hints! These sort of solutions are ok for a short term, BUT please remember I (and I assume lot of other developers) are/were looking at Servoy because we don’t want spend all the time in finding a workaround to a given problem. If Servoy goes this route (and the chance is there as for any tool after a while, because the creator forgets in the daily business about what’s the overall goal and just adds here and there some things.
Jan Aleman wrote me an eMail after my first interest in Servoy that started like this: (and it looked good to me, because I don’t like a tool to find workarounds - there a too many such tools out there!)
Sind Sie work around-muede?
Liebe Application-Professionals,
darauf haben Sie gewartet!
Wir bieten Ihnen eine exzellente Alternative zu …
<
And, yes I am workaround tired!
Best regards, Robert
PS: I hope you still reply on my future questions and give me (and others) good hints
Robert Huber:
Test
bcusick:
Hi Robert,
I had a situation where I needed this type of functionality as well. SO, what I did was return a delimited string as the return value.
Example:
DisplayItem|val1;val2;val3;val4;val5
I did a SQL query to load the display value, and then “built” the return value string using other columns in the (complex) query. Then on the onDataChange event (where the user can choose an option) - I was able to parse the data stored in the field.
Thanks for the solution, I also suffer from this 3 column limitation. Would it be possible to put more than 3, may be 5 columns for a value list as a feature request? To have the columns is quite much easier than to program around this limitation.
Again, the alternative to the valuelist dialog where you’ve got 3 parameters available , really is application.setValueListItems(), and that’s definitely NOT a workaround.
In fact it’s far more efficient to use that method instead of the standard 3-box stuff. You’re talking about a need for 5 columns right now, but what if you need 6 columns in a couple of months? Seems a bit silly to just keep adding boxes.
You can also build a global script that handles the application.setValueListItems() and feed it with your own arguments.
Build once, use it forever.
Thanks for the input, so I will look into the application.setValueListItems().
maarten:
Hi Robert,
Thanks for the solution, I also suffer from this 3 column limitation. Would it be possible to put more than 3, may be 5 columns for a value list as a feature request? To have the columns is quite much easier than to program around this limitation.
Again, the alternative to the valuelist dialog where you’ve got 3 parameters available , really is application.setValueListItems(), and that’s definitely NOT a workaround.
In fact it’s far more efficient to use that method instead of the standard 3-box stuff. You’re talking about a need for 5 columns right now, but what if you need 6 columns in a couple of months? Seems a bit silly to just keep adding boxes.
You can also build a global script that handles the application.setValueListItems() and feed it with your own arguments.
Build once, use it forever.
You surely are right about the number of columns, the need can change any day .-) so your proposal seems to be much more flexible. Is there somewhere an example for this?
To follow up on your question regarding examples of custom valuelists with unlimited columns:
Is there somewhere an example for this?
First set up a custom value list with no values in it. We’ll call it ‘patient_info’.
Then set up your query to compile your valuelist (this can be dynamic, i.e. based on the form, record, column, whatever or it can be static and launched at application start up). In this case we’ll do it like this:
var patient_info_q = "SELECT LastName + ', ' + FirstName + ' : ' + Address1 + ', ' + City, medrec from patient"
So in this case my valuelist choices will be made up of four columns: patient last name, first name, address and city. What will be returned is the medical record number (medrec). The valuelist itself is then set up by running the query:
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), patient_info_q, null, 100);
and then filling the up the valuelist - patient_info - using the information from the dataset.
How many columns or items you concatenate into your valuelist is up to you. You can also use arrays to make up your valuelists in this way but I find it handier to always use queries.
Thanks a lot for your example. I’ll try it, looks good for displaying many columns. I am wondering how to do that if one has to write back more than one column, i. e. the primary key is a concatenated key (made up of more than one column)?
Best regards, Robert
PS: I agree with you that a SQL query is handy!
john.allen:
Hi Robert,
To follow up on your question regarding examples of custom valuelists with unlimited columns:
Is there somewhere an example for this?
First set up a custom value list with no values in it. We’ll call it ‘patient_info’.
Then set up your query to compile your valuelist (this can be dynamic, i.e. based on the form, record, column, whatever or it can be static and launched at application start up). In this case we’ll do it like this:
var patient_info_q = "SELECT LastName + ', ' + FirstName + ' : ' + Address1 + ', ' + City, medrec from patient"
So in this case my valuelist choices will be made up of four columns: patient last name, first name, address and city. What will be returned is the medical record number (medrec). The valuelist itself is then set up by running the query:
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), patient_info_q, null, 100);
and then filling the up the valuelist - patient_info - using the information from the dataset.
How many columns or items you concatenate into your valuelist is up to you. You can also use arrays to make up your valuelists in this way but I find it handier to always use queries.
I am wondering how to do that if one has to write back more than one column, i. e. the primary key is a concatenated key (made up of more than one column)?
There are (as with all things Servoy it seems!) many ways to do that. Perhaps the simplest is just to use a relationship to that primary, concatenated key without ‘entering’ the values in another table. But probably the simplest if you do want to write back those values in a table is to use the ‘lookup’ feature with a relationship based on that primary, concatenated key.
I would probably have the valuelist show whatever was necessary as discussed earlier and write back the primary, concatenated key to a global. Make a relationship linking that global to the primary, concatenated key. Then when the user selects from the valuelist, have the other, individual values written back to the database table based on that relationship using the ‘auto-enter, lookup’ property for dataproviders. To be honest I haven’t ever used it myself because I haven’t had any need for it yet. But basically you set it up as a Servoy property of the columns you want ‘filled’. Go to the Dataproviders dialog box and click on one of the columns and then properties.