If you benefit from web2py hope you feel encouraged to pay it forward by contributing back to society in whatever form you choose!

The SQLFORM.grid is the best thing that has happened to Web2py recently. Though it is still in its infancy, it is very powerful and commands its own slice. I’ve tried to get some details for preparing the slides from Google Group threads and help from code and web2py group people. Should work with web2py1.99.2 and above.


The SQLFORM.grid Syntax- derived from code.

SQLFORM.grid(query, fields=None, field_id=None, left=None, headers={}, orderby=None,   searchable=True,  sortable=True,  paginate=20,   deletable=True, editable=True, details=True, selectable=None, create=True, csv=True, links=None, links_in_grid=True, upload = <default>'  args=[], user_signature = True, maxtextlengths={}, maxtextlength=20, onvalidation=None, oncreate=None, onupdate=None, ondelete=None, sorter_icons=('[^]','[v]'),  ui = 'web2py',  showbuttontext=True,  _class="web2py_grid",  formname='web2py_grid', search_widget='default',   ignore_rw = False,  formstyle = 'table3cols', ):

Real World Example of Usage: Consider a db table as below- in your model - (db.py)

db.define_table(‘contact’,

Field('first_name', length=128), Field('last_name', length=128), Field('date_of_birth', ‘date’) )

#To create a grid for this table, add the following code in your controller –  (default.py)
#Assuming you are exposing the grid in contact.html view – 

def contact():
        db.contact.id.readable=False # Since we do not want to expose the id field on the grid

    #Define the query object. Here we are pulling all contacts having date of birth less than 18 Nov 1990
    query=((db.contact.date_of_birth < “1990-11-18”))

    #Define the fields to show on grid. Note: (you need to specify id field in fields section in 1.99.2
    # this is not required in later versions)
        fields = (db.contact.id, db.contact.first_name, db.contact.last_name, db.contact.date_of_birth)

    #Define headers as tuples/dictionaries
    headers = {'contact.id':   'ID',
           'contact.first_name': 'First Name',
           'contact.last_name': 'Last Name',
           'contact.date_of_birth': 'Birth Date' }

    #Let's specify a default sort order on date_of_birth column in grid
    default_sort_order=[db.contact.date_of_birth]

    #Creating the grid object
    form = SQLFORM.grid(query=query, fields=fields, headers=headers, orderby=default_sort_order,
                create=False, deletable=False, editable=False, maxtextlength=64, paginate=25)

    return dict(form=form)

Note:

create= False -  will not show the Add button to the grid,
    deletable=False - will not show the delete button on grid
    editable=False - will not show the edit button on grid
    maxtextlength =64  - will restrict column text lenght to 64 characters and
    paginate=25  - will provide pagination to grid. here 25 records in the grid.

More parameters from the syntax can be added as per your requirement. Links could be added to the grid by adding this line. Here default is your controller name and view is your function name.

links = [lambda row: A('View Post',_href=URL("default","view",args=[row.id]))]

These could be called by adding 
links=links to the SQLFORM.grid() function as a parameter.

Finally the view (contact.html)

In the view simply write the following code. You can add more to this file depending on your requirements

{{extend 'layout.html'}}
{{=form}} <! This will call the contact function -->

That's it start your web2py application and point your browser to contact.html it should work!

Some things to consider for proper working of SQLFORM.grid - -------- 1. As of this writing, css files in a plugin_layout / layout should be manipulated manually to show the SQLFORM.grid else sometimes it messes the UI of the grid. In this case you have to make sure that you include below files in layout.html of your application

{{response.files.append(URL('static','jquery-ui/js/jquery-ui-1.8.16.custom.min.js'))}}
{{response.files.append(URL('static','jquery-ui/css/sunny/jquery-ui-1.8.16.custom.css'))}}

SQLFORM.grid other permissions

  SQLFORM.grid(..., editable=auth.has_membership('editor_group'), deletable=auth.has_membership('delete_group'), user_signature=True)

Some button links with css3buttons

Note for using css3buttons, you need to download the library and configure it for your web2py installation

  links = [lambda row: A(SPAN(_class='icon magnifier'),'button_name,_class='button',_title='View  Post',_href=URL(args=["view", db.table, row.id]))]

Custom Theming

CSS files typically go in your app's /static/css folder, and you then have to make sure the CSS file gets linked in the page head (like any other CSS file). An easy way to include the CSS file is to do:

  response.files.append(URL('static', 'cs/your_theme_file.css'))

in the controller action that needs the jquery-ui theme (or in your layout.html file if needed on most/all pages).

Related slices

Comments (7)

  • Login to post



  • 0
    всеволод-иванов-11015 3 days ago

    Hi,

    Was anybody able to successfully implement a custom "search_widget=" ? I sure would like to see an example that works.


  • 0
    rem 10 months ago

    The line which begins:

    form = SQLForm.grid(query=query, fields=fields,.......

    generates an error because who knows what the heck an SQLForm.grid is....?

     

    It should be written as:

    form = SQLFORM.grid(query=query, fields=fields,.....

     

    Thanks!

    replies (1)
    • rahuld 9 months ago

      Done. Thanks!


  • 0
    teddynyambe-78553 1 year ago

    Just an observation, on the fields to show in the grid the should be:

    fields = [db.contact.id, db.contact.first_name, db.contact.last_name, db.contact.date_of_birth]

     


  • 0
    rahuld 2 years ago
    @telloroberto, I think you need to get your hands dirty with JQuery code. Not really sure if we have this feature in sqlformgrid implemented natively.

  • 0
    telloroberto 2 years ago
    How can i click on a cell of the grid and rescue the values?? is that posibble, i spent 2 days searching for that on web2py and cant find info.
show more comments

Hosting graciously provided by:
Python Anywhere