Hello Maarten,
I have been working with Mike on this project. Perhaps I can clarify his question. We have three tables, with faculty_area serviing as a mapping table for faculty and area:
faculty [faculty_id plus other fields]
faculty_area [faculty_id, area_id, order_id]
area [area_id, area_desc]
We want a form that enables the user to select up to three areas from a combo box (pulldown menu) for each faculty member. I expected this to be possible using relations and valuelists (using related values), but we have not had success with this. If this is possible, will you please explain how to do this?
In trying to set this up, I am a little confused over what to use for the data provider for each area field in the form (e.g., area1, area2, area3), and how that is related to the data provider in the valuelist dialog. I also suspect that the relations have to involve self-joins, but I have never done this with Servoy, and my attempts so far have not succeeded. the correct fields show up in the valuelist dialog, but we can’t get them to display in the comboboxes. To give you a better idea, here is a query that returns once record for each faculty_id, along with their names and a list of one or more areas in a concatenated field:
//query to list multiple areas for each faculty member using self-joins
SELECT f.faculty_id, f.first_name, f.last_name, CONCAT(a1.area_desc, if(a2.area_desc is null, '', CONCAT(', ', a2.area_desc)), if(a3.area_desc is null, '', CONCAT(', ', a3.area_desc))) AS AREAS FROM faculty f JOIN faculty_program fp1 on f.faculty_id=fp1.faculty_id and fp1.order_id=1 LEFT JOIN faculty_program fp2 on f.faculty_id=fp2.faculty_id and fp2.order_id=2 JOIN program p1 on fp1.program_id=p1.program_id and fp1.order_id=1 LEFT JOIN program p2 on fp2.program_id=p2.program_id and fp2.order_id=2 JOIN faculty_area fa1 on f.faculty_id=fa1.faculty_id and fa1.order_id=1 LEFT JOIN faculty_area fa2 on f.faculty_id=fa2.faculty_id and fa2.order_id=2 LEFT JOIN faculty_area fa3 on f.faculty_id=fa3.faculty_id and fa3.order_id=3 JOIN area a1 on fa1.area_id=a1.area_id LEFT JOIN area a2 on fa2.area_id=a2.area_id LEFT JOIN area a3 on fa3.area_id=a3.area_id
Is it possible to set up the comboboxes and valuelists for this form with relations in Servy, or this only possible using methods, such as the one you describe in your post?
Thank you very much! I hope this clarifies our problem and question!
Peter Weil
Laboratory of Genetics
University of Wisconsin-Madison
maarten:
Hello Mike,
Did you try using “application.setValueListItems”
With this function you can control everything in your valuelist,
including dependencies on various inputs.
Here’s a sample:
- first create a custom valuelist named eg. “action_type”
- attach this script to eg. onDatachange of a combobox field.
//get data form table “valuelists”
var vQuery = ‘select display_value1, return_value from valuelists where valuelist_name = ? order by display_value1’;
//put result in a dataset. Note that questionmark is a variable
var dataset = databaseManager.getDataSetByQuery(forms.svyVLTvaluelists.controller.getServerName(), vQuery, [‘action_type’], 500);
var displayArray = new Array() //display values
var returnArray = new Array() //return values (these go into the database)
displayArray = dataset.getColumnAsArray(1)
returnArray = dataset.getColumnAsArray(2)
displayArray.unshift(“”); //this puts a empty value in front of the array.
returnArray.unshift(“”);
application.setValueListItems(‘action_type’,displayArray, returnArray);
