CODEWITHSUNDEEP
pythonmysqlweb2py
dlang
dlang

Reputation: 45

Sorting table reference dropdown when adding/editing entries using SQLFORM.grid

This is how I have my tables defined in db.py:

db.define('other_other_table1',
          Field('other_other1_field1', 'string',
                length=30, required=True,
                format='%(other_other1_field1)s'),
                singular='Other Other Stuff 1',
                plural='Other Other Stuff 1')


db.define('other_other_table2',
          Field('other_other2_field1', 'string',
                length=40, required=True,
                format='%(other_other2_field1)s'),
                singular='Other Other Stuff 2',
                plural='Other Other Stuff 2')


db.define_table('other_table',
                Field('other_field1',
                      'reference other_other_table1'),
                Field('other_field2', 'double'),
                Field('other_field3', 
                      'reference other_other_table2'),
                format=lambda r: '%s %s %s' % \
                             (r.other_field1.other_other1_field1,
                              str(r.other_field2),
                              r.other_field3.other_other2_field1),
                singular='Other Stuff',
                plural='Other Stuff')


db.define_table('main_table',
                Field('main_field1',
                      'reference other_table'),
                singular='Stuff',
                plural='Stuff')

In my controller, for main_table I have a simple action:

def main_table():
    grid = SQLFORM.grid(db.main_table, maxtextlength=100)

    return dict(grid=grid)

This works well and gives the functionality I was hoping for (which was to be able to easily add, edit, view, & delete entries in the database), but the problem I'm running into comes when adding/editing new entries in main_table.

When I click on the +Add Record button and go to the form for adding an entry into main_table, it shows a dropdown. The problem is that the choices of the dropdown menu are sorted by the ID's of the entries in other_table, whereas instead I'd prefer for the items to be sorted alphabetically based on the format specifed in the definition of other_table.

Is there an way to get it to sort alphabetically like I want?

Upvotes: 1

Views: 222

Answers (1)

Anthony
Anthony

Reputation: 25536

Reference fields get a default IS_IN_DB validator whose label argument is based on the format attribute of the referenced table (the label argument is used to generate labels in <select> widgets in forms). When the format attribute is a string, web2py inspects the string, extracts the field names, and uses them to build an orderby argument for the database query (which results in the records being ordered based on the generated labels). However, when the format attribute is a function, as in this case, this does not work.

Instead, you can manually specify the IS_IN_DB validator and set sort=True. This tells it to explicitly order the options by the labels.

db.main_table.main_field1.requires=IS_IN_DB(db, 'other_table.id',
                                            label=db.other_table._format, sort=True)

Upvotes: 1

Related Questions