Working with valuelists

Classic Servoy
1

Hi,

If I have a valuelist that gets its values from a database relationship, can I use that valuelist on a table that isn’t the parent in the relationship?

For example, I have a “locations” form nested within a “customers” form. “Customers” is related to “locations,” it is also related to “contacts.” I would like to place a valuelist that shows the values from “customers” to “contacts” on the nested location form.

Doesn’t seem to be working.

Can I do this? if not, what’s the best way to handle it (in FM I would have used a two layer relationship)?

2

Probably the best way to accomplish this is creating value list based on a database query.

//Fill a custom type valuelist with values from array(s) or dataset
//set display values (return values will be same as display values)
application.setValueListItems(‘my_en_types’,new Array(‘Item 1’, ‘Item 2’, ‘Item 3’));
//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);

3

I have found you can do a lot with valuelists without resorting to setValueListItems(). You might want to look at my tutorial on this subject in Servoy Magazine.

Let me make sure I understand the relationships in your solution so I can give you more help. Your relations are:

One Customer to Many Locations
and
One Customer to Many Contacts

I assume that the Locations nested form is based on the Locations table, and the tab-panel is based on a Customers_to_Locations relation in Servoy.

Assuming all that is correct, then what you could do is create the relations required for Servoy to get from the current Location to its parent Customer and then back down to that Customer’s contacts. For that you would need the following relations:

Locations_to_Customers
and
Customers_to_Contacts

then you create a value list based on those two relations chained together. In the valuelist editor window you will click “Database Values” and then “Related Values” and provide Locations_to_Customers as the first relation and Customers_to_contacts as the N-M Relation. The 3 lists of dataproviders will now be from the Contacts table, ready to present in your valuelist.