Manipulating Items in DropDown List using SQLFORM

[Brian Boatright]

I'm using SQLFORM and was able to add an orderby to the original model class to sort a resulted dropdown list sqlform was generating. 

Ideally I need to limit the items in the dropdown list by id to be id > 10, as the first 10 are static types and those over id 10 are user generated and assignable. 

I've seen a few posts that suggest adding a IS_IN_DB with a query to limit what is returned for a dropdown list. I'm conserved that would change the table definition or relations globally instead of just for this one form. 

Is it possible to manipulate what is included in a dropdown list generated by SQLFORM without effecting the actual database via a query?

[Nico de Groot]

Hi Brain,

A query does not change the structure of the table. It just returns a subset.

Nico de Groot

[Brian Boatright]

That makes sense but doesn't IS_IN_DB create foreign keys in database or other relationships between the tables that might be effected?

[Nico de Groot]

There is an option to add records to the table: auto_add (but it defaults to false). Otherwise AFAIK no changes to the tables are made. You can check this by looking at the source code if you really want to be sure. See http://web2py.readthedocs.io/en/latest/validators.html?highlight=is_in_db#gluon.validators.IS_IN_DB. Or experiment on your dev machine and database.

Nico

 

 

[Brian Boatright]

In my models.py where I setup the database tables I have a row 

db.define_table('item',

                Field('parentid','integer', label='Part of'),

                Field('itemtypeid', db.itemtypes, required=True, requires=IS_IN_DB(db,db.itemtypes,'%(name)s',orderby='id',error_message='Select a Type for this new item (use misc if not sure)'), label='Item Type'),

Using the IS_IN_DB to connect the db.itemtypes.name to create the drop downs in the forms.

If in a controller for a page I add another IS_IN_DB to filter the rows returns for the dropdown, does it change the table relations for other pages or forms that may be using the IS_IN_DB from the models.py?