# Dynamic Value List

**URL:** <https://forum.servoy.com/t/dynamic-value-list/4545>\
**Category:** Classic Servoy\
**Created:** [July 27, 2005, 1:21pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545 "2005-07-27T13:21:38Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![ebrandt](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@ebrandt](https://forum.servoy.com/u/ebrandt)\
**Post date:** [July 27, 2005, 1:21pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/1 "2005-07-27T13:21:38Z")

</div>

Before I embarked on this method I just wanted to confirm that it was possible:

All the value lists I have created so far have mostly been relationship based, this has worked well.

I have been trying to work through an issue related to portal elements at the cell level that is not possible.

In my related portal lines items I will have a field called item\_class (A, B or C in a combobox) and a field item\_code (where I want a dynamic Type Ahead Value list.

I also have three tables: table1, table2, table3

I want to load all the values from either table1, table2 or table3 into the dynamic value list that I will have assigned to the item\_code, based on the selection made in the combobox assigned to the item\_class field.

I will do the research for building these Value Lists based on a argument. I just wanted to make sure that I would not have any issues with this being in a portal.

I figured I would run this method (populate the value list), based on the (OnFocusGained) event of the Item\_Code field.

I also needed to validate the Item\_Class based on the Item\_code. I thought I could attach this method to the (OnFocusLost) event of the item\_class field and the item\_code field. This will ensure that the class will always match the the correct table values.

I will do the research for building these Value Lists based on an argument. I just wanted to make sure that I would not have any issues with the Value lists and event driven methods being in a portal.

Thanks for any help.

Erich

---

<div class="post-metadata">

**Author:** ![ebrandt](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@ebrandt](https://forum.servoy.com/u/ebrandt)\
**Post date:** [July 27, 2005, 1:59pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/2 "2005-07-27T13:59:48Z")

</div>

After doing some research on loading value lists based on Data Sets, I was courious if you would be able to display one value and return a different one , I want to display the Item Description and insert the Item ID.

Thanks,  
Erich

---

<div class="post-metadata">

**Author:** ![bcusick](https://avatars.discourse-cdn.com/v4/letter/b/ed8c4c/32.png) [@bcusick](https://forum.servoy.com/u/bcusick)\
**Post date:** [July 27, 2005, 4:25pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/3 "2005-07-27T16:25:11Z")

</div>

Hi Erich,

OF COURSE you can specify a display and return value dynamically - this is Servoy! ![:D]( "Very Happy")

Check out the sample code for application.setValueListItems().

Hope this points you in the right direction.

---

<div class="post-metadata">

**Author:** ![ebrandt](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@ebrandt](https://forum.servoy.com/u/ebrandt)\
**Post date:** [July 28, 2005, 1:17pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/4 "2005-07-28T13:17:08Z")

</div>

I have been evaulating this function as well as getting my feet wet using arrays.

In the sample code I opened via the method editor, I had a few questions

//set display values and return values (which are stored in dataprovider)  
[//application.setValueListItems](https://application.setValueListItems)(‘my\_en\_types’,new Array(‘Item 1’, ‘Item 2’, ‘Item 3’),new Array(10000,10010,10456));  
//do query and fill valuelist (see databaseManager for full details of queries/dataset)  
//var query = ‘select c1,c2 from test\_table’;  
//var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 25);  
[//application.setValueListItems](https://application.setValueListItems)(‘my\_en\_types’,dataset);

I was courious if this sample code was in any particular order.

I would think that you would first create the query variable  
Then create the data set variable and assign the query  
assign the data set / array to display and return values to value list.

I also noticed that the SetValueListItems is used twice.

I have looked through the documentation on creating arrays and could not find much, I then used some other Java sources to try to decode this sample. the syntax used brackets in some examples.

I understand that you have an array with values that can refered to my indexs.

I got this far in creating my Value list and am insure of the syntax.

var MAQuery = ‘Select item\_code, accessory\_description from MiscAccessories’;  
var MAdataset = databaseManager.getDataSetByQuery(controller.getServerName(),Query);

//Assign data set / array, set display values and return values into value list  
application.setValueListItems( My\_Value\_List, MAdataset,new Array( **‘Item 1’, ‘Item 2’** ),new Array( **10000,10010** ))

I want to display index 0 (item\_code) and index 1(accessory\_description)  
from the data set and return index 0 (item\_code).

I tried to find a close sample on the forum, but I got millions of resuts and after an hour I could not find something close with the search I did.

I also don’t understand how to assign the value list to the Field. Do I create a value list called My\_Value\_List in the valuelist creation window?

Pardon my ignorace, while I am trying to get through this array indexing syntax.

Thanks for any help.

---

<div class="post-metadata">

**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\
**Post date:** [July 28, 2005, 3:17pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/5 "2005-07-28T15:17:40Z")

</div>

Erich,

maybe it is helpful to imagine an array simply as something that holds several values. If you do

```auto
var myArray = new Array() // you get an empty array

```

Arrays are zero based, so the first entry is at position zero. So you fill the first value

```auto
myArray[0] = 'my first value'
myArray[1] = 'my second value'

```

and so on

In order to use application.setValueListItems() you can provide either one array or two arrays. If you provide two Arrays, Servoy will display the values of one Array and return the corresponding index of the second. If you only provide one Array, Servoy will display AND return those values.

So if I understand you right, you want to display accessory\_description and return the item\_code.

So you can simply do

```auto
var MAQuery = 'Select item_code, accessory_description from MiscAccessories';
var MAdataset = databaseManager.getDataSetByQuery(controller.getServerName(),MAQuery , null, 1000); // will return a maximum of 1000 items!

```

Now you have to convert your dataset to Arrays:

```auto
var MAids = MAdataset.getColumnAsArray(1); // your item_code column
var MAnames = MAdataset.getColumnAsArray(2); // your accessory_description column

```

and then

```auto
application.setValueListItems( 'My_Value_List', MAnames, MAids) 

```

Hope this helps  
Patrick

---

<div class="post-metadata">

**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\
**Post date:** [July 28, 2005, 3:20pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/6 "2005-07-28T15:20:44Z")

</div>

More for your understanding of Arrays:

Instead of

```auto
var MAids = MAdataset.getColumnAsArray(1); // your item_code column 

```

you could also do

```auto
var MAids = new Array();
for (var i = 1; i <= MAdataset.getMaxRowIndex(); i++) // loops through your dataset
{
   MAids[i-1] = MAdataset.getValue(i, 1) // Arrays are zero based, so i-1
}

```

---

<div class="post-metadata">

**Author:** ![ebrandt](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@ebrandt](https://forum.servoy.com/u/ebrandt)\
**Post date:** [July 28, 2005, 3:27pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/7 "2005-07-28T15:27:50Z")

</div>

Patrick,

Thank you for your time, this was very helpful

Erich

---

<div class="post-metadata">

**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:** [July 29, 2005, 6:39pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/8 "2005-07-29T18:39:14Z")

</div>

By the way, as I notice you are filling the Valuelist by doing a query first, this method also works showing the description and returning the code with a little less effort/typing and worrying about arrays:

```auto
var maxReturnedRows = 1000;
var query = 'select descr, code from table';
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
application.setValueListItems( 'sp_sitecodetest', dataset)

```

Funnily enough it isn’t written that way in the docs anymore although I think that is where I got it from originally (about a year ago). But it is spelled out in ‘Move SampleCode’ for ‘setValueListItems’.

---

<div class="post-metadata">

**Author:** ![ebrandt](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@ebrandt](https://forum.servoy.com/u/ebrandt)\
**Post date:** [July 29, 2005, 7:18pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/9 "2005-07-29T19:18:03Z")

</div>

Well, thats what I was wondering about, If I had the dataset why not just use extract the columns from and assign them directly to the value list.

In the sample code it gave bits and pieces of using either and array or data set.

So many options, hard to know which one in the most effiecent.

---

<div class="post-metadata">

**Author:** ![tweetie](https://avatars.discourse-cdn.com/v4/letter/t/c68b51/32.png) [@tweetie](https://forum.servoy.com/u/tweetie)\
**Post date:** [August 16, 2005, 5:21pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/10 "2005-08-16T17:21:44Z")

</div>

Hi folks,

I am doing exactly (I think) the way it should be…  
I even tried :

```auto
application.setValueListItems( 'dtzt_zaty', new Array('Item 1', 'Item 2', 'Item 3'), new Array(10000,10010,10456));

```

I also tried with an SQL-query with the description first and then the id  
I tried it with array.

I defined a empty valuelist ‘dtzt\_zaty’ with Custom Values…  
The field is an integer.

It will show Item 1 etc…but it will not show 10000, 10010

Help :?:  
I missing something

Servoy Developer  
Version R2 2.2.1rc-build 330  
Java version 1.5.0\_04-b05 (Windows 2000)

---

<div class="post-metadata">

**Author:** ![ebrandt](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@ebrandt](https://forum.servoy.com/u/ebrandt)\
**Post date:** [August 16, 2005, 5:43pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/11 "2005-08-16T17:43:55Z")

</div>

Hi tweeie, This is what I am doing to display one value and insert another in the data provider, you have to cerate 2 arrays, assign the colums indexes from one array to display and the other array to assign

//Load Dynamic Value List Product Sales Order Line Items

if (lineitemclassification == ‘Misc Parts Accessory Sales’)  
{  
//Create Misc Accessory Data Set  
var MAQuery = ‘Select item\_code, accessory\_description from MiscAccessories’;  
var MAdataset = databaseManager.getDataSetByQuery(controller.getServerName(),MAQuery, null, 1000);

//Convert data set to array  
//item\_code column  
var MAids = MAdataset.getColumnAsArray(1);  
//accessory\_description column  
var MAnames = MAdataset.getColumnAsArray(2);

//Assign array to value list  
application.setValueListItems(‘MyValueList’, MAnames, MAids)  
}

---

<div class="post-metadata">

**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, 2005, 5:59pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/12 "2005-08-16T17:59:25Z")

</div>

I’m curious. In your example above it doesn’t work if you go directly to the dataset? Using your example you can’t do this?

> //Create Misc Accessory Data Set  
> var MAQuery = ‘Select item\_code, accessory\_description from MiscAccessories’;  
> var MAdataset = databaseManager.getDataSetByQuery(controller.getServerName(),MAQuery, null, 1000);
> 
> //leave out the array building  
> application.setValueListItems(‘MyValueList’, MAdataset)  
> }

Because that works perfectly for me. I wonder if it has to keep the name ‘dataset’. I wouldn’t have thought so but maybe that is it.

---

<div class="post-metadata">

**Author:** ![ebrandt](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@ebrandt](https://forum.servoy.com/u/ebrandt)\
**Post date:** [August 16, 2005, 6:12pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/13 "2005-08-16T18:12:59Z")

</div>

I suppose I may be taking the long road. I was just trying to work through the documentation, learning arrays, trial and error and much needed help from the forum.

Your method seems much cleaner, but since it works I will move to the next challange. Maybe one day I will have time to go back and recode, after I finished learning java (if that ever happens). ![:)]( "Smile")

---

<div class="post-metadata">

**Author:** ![tweetie](https://avatars.discourse-cdn.com/v4/letter/t/c68b51/32.png) [@tweetie](https://forum.servoy.com/u/tweetie)\
**Post date:** [August 16, 2005, 6:49pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/14 "2005-08-16T18:49:31Z")

</div>

> ebrandt:  
> Hi tweeie, This is what I am doing to display one value and insert another in the data provider, you have to cerate 2 arrays, assign the colums indexes from one array to display and the other array to assign
> 
> //Load Dynamic Value List Product Sales Order Line Items
> 
> if (lineitemclassification == ‘Misc Parts Accessory Sales’)  
> {  
> //Create Misc Accessory Data Set  
> var MAQuery = ‘Select item\_code, accessory\_description from MiscAccessories’;  
> var MAdataset = databaseManager.getDataSetByQuery(controller.getServerName(),MAQuery, null, 1000);
> 
> //Convert data set to array  
> //item\_code column  
> var MAids = MAdataset.getColumnAsArray(1);  
> //accessory\_description column  
> var MAnames = MAdataset.getColumnAsArray(2);
> 
> //Assign array to value list  
> application.setValueListItems(‘MyValueList’, MAnames, MAids)  
> }

I have done that to…but to no avail  
I must be missing something else,

---

<div class="post-metadata">

**Author:** ![ebrandt](https://avatars.discourse-cdn.com/v4/letter/e/96bed5/32.png) [@ebrandt](https://forum.servoy.com/u/ebrandt)\
**Post date:** [August 16, 2005, 6:58pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/15 "2005-08-16T18:58:18Z")

</div>

well, It works perfectly for me, I know I make a condition synt ax error at least once a month"=" instead of “==” .  
hopefully, its something simple . 🙂

---

<div class="post-metadata">

**Author:** ![kazar](https://avatars.discourse-cdn.com/v4/letter/k/3be4f8/32.png) [@kazar](https://forum.servoy.com/u/kazar)\
**Post date:** [August 24, 2005, 12:41am UTC](https://forum.servoy.com/t/dynamic-value-list/4545/16 "2005-08-24T00:41:50Z")

</div>

> ebrandt:  
> Before I embarked on this method I just wanted to confirm that it was possible:
> 
> All the value lists I have created so far have mostly been relationship based, this has worked well.
> 
> I have been trying to work through an issue related to portal elements at the cell level that is not possible.
> 
> In my related portal lines items I will have a field called item\_class (A, B or C in a combobox) and a field item\_code (where I want a dynamic Type Ahead Value list.
> 
> I also have three tables: table1, table2, table3
> 
> I want to load all the values from either table1, table2 or table3 into the dynamic value list that I will have assigned to the item\_code, based on the selection made in the combobox assigned to the item\_class field.
> 
> I will do the research for building these Value Lists based on a argument. I just wanted to make sure that I would not have any issues with this being in a portal.
> 
> I figured I would run this method (populate the value list), based on the (OnFocusGained) event of the Item\_Code field.

Hi Erich –

I’m going to take 25 steps backwards here (a trick I’ve become known to do on other forums), and bring the question back to an inquiry into the structural design of this database. The replies that followed your original post have been bookmarked for my further study – the question of how to achieve this in javascript code is just way over my head right now in terms of my progress in learning Servoy! So apart from the question of what code you can use as your “sledgehammer” to build this value list, I’m wondering whether - if the blocks of wood are just stacked a bit differently - maybe you do not need any special tools at all to build the list.

In your description you refer to 3 tables as “Table 1, Table 2 and Table 3”, from which you wish to populate a dynamic value list, based upon the selection of A, B, or C in the item\_class field. If we take it out of generic “Table 1” type of names, what are the actual entities these tables represent?

It is obvious from your screenshot that A, B, and C in your description refer to the “Inventory Sales”, “Misc Parts Accessory Sales” and “Listed Parts Sales”. Sounds like the items in the three tables from which the item\_code list would be derived are all lists of things sold? There is a field in each of these tables called “item\_code”? The three tables probably share other attributes such as, for example, price, qty\_per\_unit, unit\_type (i.e., “carton” or “piece”), maybe inventory restocking level, quantity discount levels, etc.?

I have almost always found that when I discover a need to build a value list from two or more tables, a re-examination of the structure shows that the attributes of those tables are almost if not exactly the same, perhaps with the exception of one or more “status” or “type” attributes. Therefore it is possible the tables in question do not really represent distinct entities within the overall framework of the solution, but rather are sub-classes within a single entity, and can be combined into one table.

I wonder if your three tables can similarly be combined into one, with an item\_class column that, in each record, would hold one of the three “Sales” types in your combobox. If this description makes sense at all regarding the solution you are working on, you could then use a simple relational value list based on a match between the item\_class field the “Equipment Information” line-items table in the portal and the newly-combined single Products (or Equipment or whatever) table.

My apologies in advance if all of this is hopelessly obvious and perhaps does not pertain at all to your solution. Looking at your screenshot it sure looks like a well-considered design. I’m only “thinking out loud” in the same type of process I make myself go through when I find myself wondering how to perform what I think of as “acrobatic coding” to achieve the compiling of multi-tabular data into one column in another table. It sometimes helps to take it back to a question of structure, on a very basic level, one more tme before proceeding. So, I certainly am not asking you to defend or explain the structure you have designed to in response to this post … there sometimes are also very good reasons for keeping similar entities stored in separate tables, rather than lumping them together. (For example, being that this is Servoy, they could be 3 tables on 3 different back ends from 3 other solutions over which you have no structural control…)

just some food for thought,

kazar

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [August 24, 2005, 5:25am UTC](https://forum.servoy.com/t/dynamic-value-list/4545/17 "2005-08-24T05:25:15Z")

</div>

Let me just add that learning how to manipulate value lists with SQL queries will pay off in spades. What you can do with value lists this way will make regular (and even related) value lists seem like childs play. You will never go back! Here’s several examples to get you thinking of the possibilities…

(1) Adding record counts to your value lists:

(2) Use HTML to bold a value list item:

(3) Use HTML to add a graphic to value list items:

(4) And a REALLY good article on Servoy Magazine by Riccardo Albieri on a very cool way to allow users to manage the values in value lists ([link](http://www.servoymagazine.com/home/2004/11/article_valueli.html)). Includes running methods with HTML links.

I’m positive other people are doing all kinds of crazy stuff with value lists. All possible to do with Servoy if you let your imagination run free.

Cheers

---

<div class="post-metadata">

**Author:** ![kazar](https://avatars.discourse-cdn.com/v4/letter/k/3be4f8/32.png) [@kazar](https://forum.servoy.com/u/kazar)\
**Post date:** [August 24, 2005, 5:45am UTC](https://forum.servoy.com/t/dynamic-value-list/4545/18 "2005-08-24T05:45:46Z")

</div>

> david:  
> All easy to do with Servoy if you let your imagination run free.

and all really “mind-openers”. The possibilities with Servoy are immense. Thanks for the ideas (and the magazine!), david.

kazar

---

<div class="post-metadata">

**Author:** ![david](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@david](https://forum.servoy.com/u/david)\
**Post date:** [August 24, 2005, 7:22pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/19 "2005-08-24T19:22:51Z")

</div>

> tweetie:  
> I am doing exactly (I think) the way it should be…  
> I even tried :
> 
> ```auto
> application.setValueListItems( 'dtzt_zaty', new Array('Item 1', 'Item 2', 'Item 3'), new Array(10000,10010,10456));
> 
> ```
> 
> I also tried with an SQL-query with the description first and then the id  
> I tried it with array.
> 
> I defined a empty valuelist ‘dtzt\_zaty’ with Custom Values…  
> The field is an integer.
> 
> It will show Item 1 etc…but it will not show 10000, 10010
> 
> Help ![:?:]( "Question")  
> I missing something

This is the expected behavior. If you supply two arrays to setValueListItems then the first array is what is displayed in the value list and the corresponding slot in the second array is the value that is returned to the data provider. To see this happening, place a copy of the field without a value list attached next to the one with the value list attached.

If you want to display more than than one column of values, you need to build the an array with values you want displayed. Using your example:

```auto
//initial arrays (could be returned from a SQL query for example)
//display both in a value list and return the second one to the data provider
var displayArray = new Array('Item 1', 'Item 2', 'Item 3');
var returnArray = new Array(10000,10010,10456);

//combine the arrays for display
for (i in displayArray)
{
	displayArray[i] = displayArray[i] + " - " + returnArray[i]
}

//set value list
application.setValueListItems( 'dtzt_zaty', displayArray, returnArray);

```

Hope this helps.

---

<div class="post-metadata">

**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 24, 2005, 7:58pm UTC](https://forum.servoy.com/t/dynamic-value-list/4545/20 "2005-08-24T19:58:19Z")

</div>

I didn’t understand that you wanted to display both ‘columns’ and return one to the database.

I love David’s examples with arrays and Javascript because those are definitely one of my (many!) weaker areas and I learn so much! In the same vein and because I know many on this list have more of a FileMaker (i.e. non-SQL) background, as an alternative this too is very easy to do directly with your SQL query.

> var nameValueList\_q = "select customer\_id + LastName + ', ’ + FirstName, customer\_id from customer ";  
> var nameValueList = databaseManager.getDataSetByQuery(controller.getServerName(), nameValueList\_q, null, 200);  
> application.setValueListItems(‘g\_name’, nameValueList\_q);

This would show the customer\_id, last name and first name and when one is selected would return just the customer\_id.

[Next page](https://forum.servoy.com/t/dynamic-value-list/4545.md?page=2)