In a controller in Web2Py I'm trying to populate a dropdown in a form with the results of a query. When the user submits the form I want to extract the value rather than the option name. I just can't seem to get the option value to appear in the dropdown.
I have this query:
course_list = external_db.executesql("SELECT course_id, course_title FROM course ORDER BY course_id")
and I have this table row:
form=FORM(TABLE(TR('course list: ' , SELECT(course_list, _name='courses', requires=IS_IN_SET(course_list, course_list_id, zero='- choose -'))),
but I can't find a way of only getting the value back from the option dropdown, rather than the option name.
@dido has the right idea. course_list is a list of (value, label) tuples, so you cannot simply pass it directly to SELECT to get the result you want. Another option is to construct the form using SQLFORM.factory, which is often easier than building a custom form manually via the FORM helper:
form = SQLFORM.factory(Field('courses', 'integer',
requires=IS_IN_SET(course_list, zero='- choose -')))
The above will automatically generate the SELECT with the course titles as labels and the course IDs as values.