Dynamic Value List

Classic Servoy
1

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

2

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

3

Hi Erich,

OF COURSE you can specify a display and return value dynamically - this is Servoy! :D

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

Hope this points you in the right direction.

4

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(‘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(‘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.

5

Erich,

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

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

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

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:

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

and then

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

Hope this helps
Patrick

6

More for your understanding of Arrays:

Instead of

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

you could also do

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
}
7

Patrick,

Thank you for your time, this was very helpful

Erich

8

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:

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’.

9

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.

10

Hi folks,

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

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)

11

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)
}

12

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.

13

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). :)

14

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,

15

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 . :slight_smile:

16

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

17

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). 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

18

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

19

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

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

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:

//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.

20

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.