Diffbook easy? diffbook

``fields``:inxx ``labels``:inxx

The signature for the ``SQLFORM`` constructor is the following:

``

**attributes)

``fields``:inxx ``labels``:inxx

The signature for the ``SQLFORM`` constructor is the following:

``

**attributes)

- ``buttons``:inxx ``buttons`` is a list of ``INPUT``s or ``TAG.button``s (though technically could be any combination of helpers) that will be added to a DIV where the submit button would go.

For example, adding a URL-based back-button (for a multi-page form) and a renamed submit button:

``

buttons = [TAG.button('Back',_type="button",_onClick = "parent.location='%s' " % URL(...),

TAG.button('Next',_type="submit")]

``:code

or a button which links to another page:

``

buttons = [..., A("Go to another page",_class='btn',_href=URL("default","anotherpage"))]

``:code

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

- ``buttons``:inxx ``buttons`` is a list of ``INPUT``s or ``TAG.button``s (though technically could be any combination of helpers) that will be added to a DIV where the submit button would go.

For example, adding a URL-based back-button (for a multi-page form) and a renamed submit button:

``

buttons = [TAG.button('Back',_type="button",_onClick = "parent.location='%s' " % URL(...),

TAG.button('Next',_type="submit")]

``:code

or a button which links to another page:

``

buttons = [..., A("Go to another page",_class='btn',_href=URL("default","anotherpage"))]

``:code

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

#### Text format validators

##### ``IS_ALPHANUMERIC``

``IS_ALPHANUMERIC``:inxx

This validator checks that a field value contains only characters in the ranges a-z, A-Z, or 0-9.

``

requires = IS_ALPHANUMERIC(error_message='must be alphanumeric!')

``:code

##### ``IS_LOWER``

``IS_LOWER``:inxx

This validator never returns an error. It just converts the value to lower case.

``

requires = IS_LOWER()

``:code

##### ``IS_UPPER``

IS_NOT_IN_DB(dbset, field, error_message='value already in database or empty', a

``:code

The following code, for example, does not allow registration of two persons with the same name within 10 days of each other:

``

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

[[validate_IS_IN_DB]]

######Synopsis:

``IS_IN_DB(db|set,'table.value_field','%(representing_field)s',zero='choose one')``

where the third and fourth arguments are optional.

#### Text format validators

##### ``IS_ALPHANUMERIC``

``IS_ALPHANUMERIC``:inxx

This validator checks that a field value contains only characters in the ranges a-z, A-Z, or 0-9.

``

requires = IS_ALPHANUMERIC(error_message='must be alphanumeric!')

``:code

##### ``IS_LOWER``

``IS_LOWER``:inxx

This validator never returns an error. It just converts the value to lower case.

``

requires = IS_LOWER()

``:code

##### ``IS_UPPER``

IS_NOT_IN_DB(dbset, field, error_message='value already in database or empty', a

``:code

The following code, for example, does not allow registration of two persons with the same name within 10 days of each other:

``

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

[[validate_IS_IN_DB]]

######Synopsis:

``IS_IN_DB(db|set,'table.value_field','%(representing_field)s',zero='choose one')``

where the third and fourth arguments are optional.

##### ``IS_NOT_IN_DB``

``IS_NOT_IN_DB``:inxx

``IS_NOT_IN_DB(db|set, 'table.field')``

Consider the following example:

``

db.define_table('person', Field('name'))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

It requires that when you insert a new person, his/her name is not already in the database, ``db``, in the field ``person.name``.

A set can be used instead of ``db``.

As with all other validators this requirement is enforced at the form processing level, not at the database level. This means that there is a small probability that, if two visitors try to concurrently insert records with the same person.name, this results in a race condition and both records are accepted. It is therefore safer to also inform the database that this field should have a unique value:

``

db.define_table('person', Field('name', unique=True))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

Now if a race condition occurs, the database raises an OperationalError and one of the two inserts is rejected.

The first argument of ``IS_NOT_IN_DB`` can be a database connection or a Set. In

A complete argument list for ``IS_NOT_IN_DB()`` is as follows:

``

IS_NOT_IN_DB(dbset, field, error_message='value already in database or empty', allowed_override=[],

ignore_common_filters=True)

``:code

The following code, for example, does not allow registration of two persons with the same name within 10 days of each other:

``

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

``IS_IN_DB(db|set,'table.value_field','%(representing_field)s',zero='choose one')``

where the third and fourth arguments are optional.

Consider the following tables and requirement:

``

db.define_table('person', Field('name', unique=True))

db.define_table('dog', Field('name'), Field('owner', db.person)

db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s',

zero=T('choose one'))

db.person.name.requires = IS_IN_DB(db(db.person.id>10), 'person.id', '%(name)s')

``:code

##### ``IS_NOT_IN_DB``

``IS_NOT_IN_DB``:inxx

``IS_NOT_IN_DB(db|set, 'table.field')``

Consider the following example:

``

db.define_table('person', Field('name'))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

It requires that when you insert a new person, his/her name is not already in the database, ``db``, in the field ``person.name``.

A set can be used instead of ``db``.

As with all other validators this requirement is enforced at the form processing level, not at the database level. This means that there is a small probability that, if two visitors try to concurrently insert records with the same person.name, this results in a race condition and both records are accepted. It is therefore safer to also inform the database that this field should have a unique value:

``

db.define_table('person', Field('name', unique=True))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

Now if a race condition occurs, the database raises an OperationalError and one of the two inserts is rejected.

The first argument of ``IS_NOT_IN_DB`` can be a database connection or a Set. In

A complete argument list for ``IS_NOT_IN_DB()`` is as follows:

``

IS_NOT_IN_DB(dbset, field, error_message='value already in database or empty', allowed_override=[],

ignore_common_filters=True)

``:code

The following code, for example, does not allow registration of two persons with the same name within 10 days of each other:

``

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

``IS_IN_DB(db|set,'table.value_field','%(representing_field)s',zero='choose one')``

where the third and fourth arguments are optional.

Consider the following tables and requirement:

``

db.define_table('person', Field('name', unique=True))

db.define_table('dog', Field('name'), Field('owner', db.person)

db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s',

zero=T('choose one'))

db.person.name.requires = IS_IN_DB(db(db.person.id>10), 'person.id', '%(name)s')

``:code

[[checkbox_validation]]

###### Checkbox validation

To force a filled-in form checkbox (such as an acceptance of terms and conditions), use this:

``

requires=IS_IN_SET(['on'])

``:code

You may also use a dictionary or a list of tuples to make the drop down list more descriptive:

``

Dictionary example:

requires = IS_IN_SET({'A':'Apple','B':'Banana','C':'Cherry'},zero=None)

[[checkbox_validation]]

###### Checkbox validation

To force a filled-in form checkbox (such as an acceptance of terms and conditions), use this:

``

requires=IS_IN_SET(['on'])

``:code

You may also use a dictionary or a list of tuples to make the drop down list more descriptive:

``

Dictionary example:

requires = IS_IN_SET({'A':'Apple','B':'Banana','C':'Cherry'},zero=None)

#### Hidden fields

#### Hidden fields

#### SQLFORM.grid signature

The complete signature for the grid is the following:

#### SQLFORM.grid signature

The complete signature for the grid is the following:

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

There is a special ``hidden`` attribute. When a dictionary is passed as ``hidden``, its items are translated into "hidden" INPUT fields (see the example for the ``FORM`` helper in Chapter 5).

``

form = SQLFORM(...,hidden=...)

``:code

causes the hidden fields to be passed with the submission, no more, no less.

``form.accepts(...)`` is not

intended to read the received hidden fields and move them into

form.vars. The reason is security. Hidden fields can be tampered with.

``

form.vars.a = request.vars.a

form = SQLFORM(..., hidden=dict(a='b'))

``:code

#### The ``process`` method

SQLFORM uses the process method (as do forms).

If you want to use keepvalues with an SQLFORM, you pass an argument to the process method:

``if form.process(keepvalues=True).accepted:``:code

#### ``SQLFORM`` and ``insert``/``update``/``delete``

``SQLFORM`` creates a new db record when the form is accepted. Assuming ``form=SQLFORM(db.test)``:code, then the id of the last-created record will be accessible in ``myform.vars.id``.

``delete record``:inxx

If you pass a record as the optional second argument to the ``SQLFORM`` constructor, the form becomes an UPDATE form for that record. This means that when the form is submitted the existing record is updated and no new record is inserted. If you set the argument ``deletable=True``, the UPDATE form displays a "check to delete" checkbox. If checked, the record is deleted.

As the grid passes through different states, such as editing a row, a new reques

### ``SQLFORM.grid``

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid object will provide access to records matching the query.

Before we dive into the long list of arguments of the grid object we need to understand how it works. The object looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the object links the same function (``manage_users`` in the above case) but passes different ``request.args``.

#### login required by default for data updates

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

``:code

but we do not recommend it.

#### Multiple grids per controller function

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

To make the default search grid work in more than one LOADed grid, please use a different ``formname`` for each one.

-----

#### Using requests.args safely

Because the controller function that contains the grid may itself manipulate the URL arguments (known in web2py as response.args and response.vars), the grid needs to know which args should be handled by the grid and which not. Here is an example of code that allows one to manage any table:

``

onupdate and oncreate are the same callbacks available to SQLFORM.process()

ui = dict(

widget='',

header='',

content='',

default='',

cornerall='',

cornertop='',

cornerbottom='',

button='button',

buttontext='buttontext button',

buttonadd='icon plus',

buttonback='icon leftarrow',

buttonexport='icon downarrow',

buttondelete='icon trash',

buttonedit='icon pen',

buttontable='icon rightarrow',

buttonview='icon magnifier')

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``_class`` is the class for the grid container.

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``, ``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

def manage():

``:code

If the ``linked_tables`` argument is not specified all referencing tables are automatically linked. Anyway, to avoid accidentally exposing data we recommend explicitly listing tables that should be linked.

The following code creates a very powerful management interface for all tables in the system:

``

@auth.requires_membership('managers'):

def manage():

table = request.args(0) or 'auth_user'

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.smartgrid(db[table],args=request.args[:1])

return locals()

``:code

#### smartgrid signature

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some caveats:

- The first argument is a table, not a query

- ``divider`` allows to specify a character to use in the breadcrumb navigator, ``breadcrumbs_class`` will apply the class to the breadcrumb element

- All the arguments but the table, ``args``, ``linked_tables`` and ``user_signatures`` can be dictionaries as explained below.

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

``:code

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

``:code

For a smartgrid we would pass a dictionary of booleans:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

``:code

In this way we made parents searchable but children for each parent not searchable (there should not be that many to need the search widget).

### grid and smartgrid access control

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

``:code

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

``:code

### smartgrid plurals

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

There is a special ``hidden`` attribute. When a dictionary is passed as ``hidden``, its items are translated into "hidden" INPUT fields (see the example for the ``FORM`` helper in Chapter 5).

``

form = SQLFORM(...,hidden=...)

``:code

causes the hidden fields to be passed with the submission, no more, no less.

``form.accepts(...)`` is not

intended to read the received hidden fields and move them into

form.vars. The reason is security. Hidden fields can be tampered with.

``

form.vars.a = request.vars.a

form = SQLFORM(..., hidden=dict(a='b'))

``:code

#### The ``process`` method

SQLFORM uses the process method (as do forms).

If you want to use keepvalues with an SQLFORM, you pass an argument to the process method:

``if form.process(keepvalues=True).accepted:``:code

#### ``SQLFORM`` and ``insert``/``update``/``delete``

``SQLFORM`` creates a new db record when the form is accepted. Assuming ``form=SQLFORM(db.test)``:code, then the id of the last-created record will be accessible in ``myform.vars.id``.

``delete record``:inxx

If you pass a record as the optional second argument to the ``SQLFORM`` constructor, the form becomes an UPDATE form for that record. This means that when the form is submitted the existing record is updated and no new record is inserted. If you set the argument ``deletable=True``, the UPDATE form displays a "check to delete" checkbox. If checked, the record is deleted.

As the grid passes through different states, such as editing a row, a new reques

### ``SQLFORM.grid``

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid object will provide access to records matching the query.

Before we dive into the long list of arguments of the grid object we need to understand how it works. The object looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the object links the same function (``manage_users`` in the above case) but passes different ``request.args``.

#### login required by default for data updates

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

``:code

but we do not recommend it.

#### Multiple grids per controller function

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

To make the default search grid work in more than one LOADed grid, please use a different ``formname`` for each one.

-----

#### Using requests.args safely

Because the controller function that contains the grid may itself manipulate the URL arguments (known in web2py as response.args and response.vars), the grid needs to know which args should be handled by the grid and which not. Here is an example of code that allows one to manage any table:

``

onupdate and oncreate are the same callbacks available to SQLFORM.process()

ui = dict(

widget='',

header='',

content='',

default='',

cornerall='',

cornertop='',

cornerbottom='',

button='button',

buttontext='buttontext button',

buttonadd='icon plus',

buttonback='icon leftarrow',

buttonexport='icon downarrow',

buttondelete='icon trash',

buttonedit='icon pen',

buttontable='icon rightarrow',

buttonview='icon magnifier')

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``_class`` is the class for the grid container.

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``, ``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

def manage():

``:code

If the ``linked_tables`` argument is not specified all referencing tables are automatically linked. Anyway, to avoid accidentally exposing data we recommend explicitly listing tables that should be linked.

The following code creates a very powerful management interface for all tables in the system:

``

@auth.requires_membership('managers'):

def manage():

table = request.args(0) or 'auth_user'

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.smartgrid(db[table],args=request.args[:1])

return locals()

``:code

#### smartgrid signature

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some caveats:

- The first argument is a table, not a query

- ``divider`` allows to specify a character to use in the breadcrumb navigator, ``breadcrumbs_class`` will apply the class to the breadcrumb element

- All the arguments but the table, ``args``, ``linked_tables`` and ``user_signatures`` can be dictionaries as explained below.

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

``:code

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

``:code

For a smartgrid we would pass a dictionary of booleans:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

``:code

In this way we made parents searchable but children for each parent not searchable (there should not be that many to need the search widget).

### grid and smartgrid access control

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

``:code

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

``:code

### smartgrid plurals

- ``buttons``:inxx ``buttons`` is a list of ``INPUT``s or ``TAG.button``s (though technically could be any combination of helpers) that will be added to a DIV where the submit button would go.

For example, adding a URL-based back-button (for a multi-page form) and a renamed submit button:

``

buttons = [TAG.button('Back',_type="button",_onClick = "parent.location='%s' " % URL(...),

TAG.button('Next',_type="submit")]

``:code

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

- ``buttons``:inxx ``buttons`` is a list of ``INPUT``s or ``TAG.button``s (though technically could be any combination of helpers) that will be added to a DIV where the submit button would go.

For example, adding a URL-based back-button (for a multi-page form) and a renamed submit button:

``

buttons = [TAG.button('Back',_type="button",_onClick = "parent.location='%s' " % URL(...),

TAG.button('Next',_type="submit")]

``:code

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

Validators are classes used to validate input fields (including forms generated from database tables).

Here is an example of using a validator with a ``FORM``:

``

INPUT(_name='a', requires=IS_INT_IN_RANGE(0, 10))

``:code

Here is an example of how to require a validator for a table field:

``

db.define_table('person', Field('name'))

db.person.name.requires = IS_NOT_EMPTY()

``:code

Validators are always assigned using the ``requires`` attribute of a field. A field can have a single validator or multiple validators. Multiple validators are made part of a list:

``

db.person.name.requires = [IS_NOT_EMPTY(),

IS_NOT_IN_DB(db, 'person.name')]

``:code

Normally validators are called automatically by the function ``accepts`` and ``process`` of a ``FORM`` or other HTML helper object that contains a form. They are called in the order in which they are listed.

IS_NOT_EMPTY(error_message='cannot be empty')

``:code

``error_message`` allows you to override the default error message for any validator.

Here is an example of a validator on a database table:

``

db.person.name.requires = IS_NOT_EMPTY(error_message='fill this!')

``:code

where we have used the translation operator ``T`` to allow for internationalization. Notice that default error messages are not translated.

Mind that the only validators that can be used with ``list:`` type fields are:

- ``IS_IN_DB(...,multiple=True)``

- ``IS_IN_SET(...,multiple=True)``

- ``IS_NOT_EMPTY()``

- ``IS_LIST_OF(...)``

The latter can be used to apply any validator to the individual items in the list.

##### ``IS_ALPHANUMERIC``

``IS_ALPHANUMERIC``:inxx

This validator checks that a field value contains only characters in the ranges a-z, A-Z, or 0-9.

``

requires = IS_ALPHANUMERIC(error_message='must be alphanumeric!')

``:code

##### ``IS_DATE``

``IS_DATE``:inxx

This validator checks that a field value contains a valid date in the specified format. It is good practice to specify the format using the translation operator, in order to support different formats in different locales.

``

requires = IS_DATE(format=T('%Y-%m-%d'),

error_message='must be YYYY-MM-DD!')

``:code

For the full description on % directives look under the IS_DATETIME validator.

For the full description on % directives look under the IS_DATETIME validator.

##### ``IS_DATETIME``

``IS_DATETIME``:inxx

This validator checks that a field value contains a valid datetime in the specified format. It is good practice to specify the format using the translation operator, in order to support different formats in different locales.

``

requires = IS_DATETIME(format=T('%Y-%m-%d %H:%M:%S'),

error_message='must be YYYY-MM-DD HH:MM:SS!')

``:code

The following symbols can be used for the format string (this shows the symbol and an example string):

``

%Y '1963'

%y '63'

%d '28'

%m '08'

%b 'Aug'

%b 'August'

%H '14'

%I '02'

%p 'PM'

%M '30'

%S '59'

``:code

##### ``IS_DATETIME_IN_RANGE``

``IS_DATETIME_IN_RANGE``:inxx

Works very much like the previous validator but allows to specify a range:

``

requires = IS_DATETIME_IN_RANGE(format=T('%Y-%m-%d %H:%M:%S'),

minimum=datetime.datetime(2008,1,1,10,30),

maximum=datetime.datetime(2009,12,31,11,45),

error_message='must be YYYY-MM-DD HH:MM::SS!')

``:code

For the full description on % directives look under the IS_DATETIME validator.

``

``:code

``

``:code

##### ``IS_EXPR``

``IS_EXPR``:inxx

Its first argument is a string containing a logical expression in terms of a variable value. It validates a field value if the expression evaluates to ``True``. For example:

``

requires = IS_EXPR('int(value)%3==0',

error_message='not divisible by 3')

``:code

One should first check that the value is an integer so that an exception will not occur.

``

requires = [IS_INT_IN_RANGE(0, 100), IS_EXPR('value%3==0')]

``:code

##### ``IS_FLOAT_IN_RANGE``

``IS_FLOAT_IN_RANGE``:inxx

Checks that the field value is a floating point number within a definite range, ``0 <= value <= 100`` in the following example:

``

requires = IS_FLOAT_IN_RANGE(0, 100, dot=".",

error_message='too small or too large!')

``:code

The ``dot`` argument is optional and allows you to internationalize the symbol used to separate the decimals.

##### ``IS_INT_IN_RANGE``

``IS_INT_IN_RANGE``:inxx

Checks that the field value is an integer number within a definite range,

``0 <= value < 100`` in the following example:

``

requires = IS_INT_IN_RANGE(0, 100,

error_message='too small or too large!')

``:code

##### ``IS_IN_SET``

``IS_IN_SET``:inxx

``multiple``:inxx

``

requires = IS_IN_SET(['a', 'b', 'c'],zero=T('choose one'),

error_message='must be a or b or c')

``:code

The zero argument is optional and it determines the text of the option selected by default, an option which is not accepted by the ``IS_IN_SET`` validator itself. If you do not want a "choose one" option, set ``zero=None``.

``

error_message='must be prime and less than 10')]

``:code

To force a filled-in form checkbox (such as an acceptance of terms and conditions), use this:

``

requires=IS_IN_SET(['on'])

``:code

You may also use a dictionary or a list of tuples to make the drop down list more descriptive:

``

requires = IS_IN_SET({'A':'Apple','B':'Banana','C':'Cherry'},zero=None)

requires = IS_IN_SET([('A','Apple'),('B','Banana'),('C','Cherry')])

``:code

##### ``IS_IN_SET`` and Tagging

The ``IS_IN_SET`` validator has an optional attribute ``multiple=False``. If set to True, multiple values can be stored in one field. The field should be of type ``list:integer`` or ``list:string``. ``multiple`` references are handled automatically in create and update forms, but they are transparent to the DAL. We strongly suggest using the jQuery multiselect plugin to render multiple fields.

------

Note that when ``multiple=True``, ``IS_IN_SET`` will accept ``zero`` or more values, i.e. it will accept the field when nothing has been selected. ``multiple`` can also be a tuple of the form ``(a,b)`` where ``a`` and ``b`` are the minimum and (exclusive) maximum number of items that can be selected respectively.

------

``

``:code

``

``:code

``

``:code

``

``:code

``

``:code

``

``:code

``

``:code

##### ``IS_IMAGE``

``IS_IMAGE``:inxx

This validator checks if a file uploaded through the file input was saved in one of the selected image formats and has dimensions (width and height) within given limits.

It does not check for maximum file size (use IS_LENGTH for that). It returns

a validation failure if no data was uploaded. It supports the file formats BMP, GIF, JPEG, PNG, and it does not require the Python Imaging Library.

Code parts taken from ref.``source1``:cite

It takes the following arguments:

- extensions: iterable containing allowed image file extensions in lowercase

- maxsize: iterable containing maximum width and height of the image

- minsize: iterable containing minimum width and height of the image

Use (-1, -1) as minsize to bypass the image-size check.

Here are some Examples:

- Check if uploaded file is in any of supported image formats:

Its arguments are:

- ``maxip`` highest allowed address; same as above

All three example values are equal, since addresses are converted to

integers for inclusion check with following function:

``

number = 16777216 * IP[0] + 65536 * IP[1] + 256 * IP[2] + IP[3]

``:code

Examples:

Check for valid IPv4 address:

``

requires = IS_IPV4()

``:code

Check for valid private network IPv4 address:

``

requires = IS_IPV4(minip='192.168.0.1', maxip='192.168.255.255')

``:code

##### ``CLEANUP``

``CLEANUP``:inxx

This is a filter. It never fails. It just removes all characters whose decimal ASCII codes are not in the list [10, 13, 32-127].

``

requires = CLEANUP()

``:code

Validators are classes used to validate input fields (including forms generated from database tables).

Here is an example of using a validator with a ``FORM``:

``

INPUT(_name='a', requires=IS_INT_IN_RANGE(0, 10))

``:code

Here is an example of how to require a validator for a table field:

``

db.define_table('person', Field('name'))

db.person.name.requires = IS_NOT_EMPTY()

``:code

Validators are always assigned using the ``requires`` attribute of a field. A field can have a single validator or multiple validators. Multiple validators are made part of a list:

``

db.person.name.requires = [IS_NOT_EMPTY(),

IS_NOT_IN_DB(db, 'person.name')]

``:code

Normally validators are called automatically by the function ``accepts`` and ``process`` of a ``FORM`` or other HTML helper object that contains a form. They are called in the order in which they are listed.

IS_NOT_EMPTY(error_message='cannot be empty')

``:code

``error_message`` allows you to override the default error message for any validator.

Here is an example of a validator on a database table:

``

db.person.name.requires = IS_NOT_EMPTY(error_message='fill this!')

``:code

where we have used the translation operator ``T`` to allow for internationalization. Notice that default error messages are not translated.

Mind that the only validators that can be used with ``list:`` type fields are:

- ``IS_IN_DB(...,multiple=True)``

- ``IS_IN_SET(...,multiple=True)``

- ``IS_NOT_EMPTY()``

- ``IS_LIST_OF(...)``

The latter can be used to apply any validator to the individual items in the list.

##### ``IS_ALPHANUMERIC``

``IS_ALPHANUMERIC``:inxx

This validator checks that a field value contains only characters in the ranges a-z, A-Z, or 0-9.

``

requires = IS_ALPHANUMERIC(error_message='must be alphanumeric!')

``:code

##### ``IS_DATE``

``IS_DATE``:inxx

This validator checks that a field value contains a valid date in the specified format. It is good practice to specify the format using the translation operator, in order to support different formats in different locales.

``

requires = IS_DATE(format=T('%Y-%m-%d'),

error_message='must be YYYY-MM-DD!')

``:code

For the full description on % directives look under the IS_DATETIME validator.

For the full description on % directives look under the IS_DATETIME validator.

##### ``IS_DATETIME``

``IS_DATETIME``:inxx

This validator checks that a field value contains a valid datetime in the specified format. It is good practice to specify the format using the translation operator, in order to support different formats in different locales.

``

requires = IS_DATETIME(format=T('%Y-%m-%d %H:%M:%S'),

error_message='must be YYYY-MM-DD HH:MM:SS!')

``:code

The following symbols can be used for the format string (this shows the symbol and an example string):

``

%Y '1963'

%y '63'

%d '28'

%m '08'

%b 'Aug'

%b 'August'

%H '14'

%I '02'

%p 'PM'

%M '30'

%S '59'

``:code

##### ``IS_DATETIME_IN_RANGE``

``IS_DATETIME_IN_RANGE``:inxx

Works very much like the previous validator but allows to specify a range:

``

requires = IS_DATETIME_IN_RANGE(format=T('%Y-%m-%d %H:%M:%S'),

minimum=datetime.datetime(2008,1,1,10,30),

maximum=datetime.datetime(2009,12,31,11,45),

error_message='must be YYYY-MM-DD HH:MM::SS!')

``:code

For the full description on % directives look under the IS_DATETIME validator.

``

``:code

``

``:code

##### ``IS_EXPR``

``IS_EXPR``:inxx

Its first argument is a string containing a logical expression in terms of a variable value. It validates a field value if the expression evaluates to ``True``. For example:

``

requires = IS_EXPR('int(value)%3==0',

error_message='not divisible by 3')

``:code

One should first check that the value is an integer so that an exception will not occur.

``

requires = [IS_INT_IN_RANGE(0, 100), IS_EXPR('value%3==0')]

``:code

##### ``IS_FLOAT_IN_RANGE``

``IS_FLOAT_IN_RANGE``:inxx

Checks that the field value is a floating point number within a definite range, ``0 <= value <= 100`` in the following example:

``

requires = IS_FLOAT_IN_RANGE(0, 100, dot=".",

error_message='too small or too large!')

``:code

The ``dot`` argument is optional and allows you to internationalize the symbol used to separate the decimals.

##### ``IS_INT_IN_RANGE``

``IS_INT_IN_RANGE``:inxx

Checks that the field value is an integer number within a definite range,

``0 <= value < 100`` in the following example:

``

requires = IS_INT_IN_RANGE(0, 100,

error_message='too small or too large!')

``:code

##### ``IS_IN_SET``

``IS_IN_SET``:inxx

``multiple``:inxx

``

requires = IS_IN_SET(['a', 'b', 'c'],zero=T('choose one'),

error_message='must be a or b or c')

``:code

The zero argument is optional and it determines the text of the option selected by default, an option which is not accepted by the ``IS_IN_SET`` validator itself. If you do not want a "choose one" option, set ``zero=None``.

``

error_message='must be prime and less than 10')]

``:code

To force a filled-in form checkbox (such as an acceptance of terms and conditions), use this:

``

requires=IS_IN_SET(['on'])

``:code

You may also use a dictionary or a list of tuples to make the drop down list more descriptive:

``

requires = IS_IN_SET({'A':'Apple','B':'Banana','C':'Cherry'},zero=None)

requires = IS_IN_SET([('A','Apple'),('B','Banana'),('C','Cherry')])

``:code

##### ``IS_IN_SET`` and Tagging

The ``IS_IN_SET`` validator has an optional attribute ``multiple=False``. If set to True, multiple values can be stored in one field. The field should be of type ``list:integer`` or ``list:string``. ``multiple`` references are handled automatically in create and update forms, but they are transparent to the DAL. We strongly suggest using the jQuery multiselect plugin to render multiple fields.

------

Note that when ``multiple=True``, ``IS_IN_SET`` will accept ``zero`` or more values, i.e. it will accept the field when nothing has been selected. ``multiple`` can also be a tuple of the form ``(a,b)`` where ``a`` and ``b`` are the minimum and (exclusive) maximum number of items that can be selected respectively.

------

``

``:code

``

``:code

``

``:code

``

``:code

``

``:code

``

``:code

``

``:code

##### ``IS_IMAGE``

``IS_IMAGE``:inxx

This validator checks if a file uploaded through the file input was saved in one of the selected image formats and has dimensions (width and height) within given limits.

It does not check for maximum file size (use IS_LENGTH for that). It returns

a validation failure if no data was uploaded. It supports the file formats BMP, GIF, JPEG, PNG, and it does not require the Python Imaging Library.

Code parts taken from ref.``source1``:cite

It takes the following arguments:

- extensions: iterable containing allowed image file extensions in lowercase

- maxsize: iterable containing maximum width and height of the image

- minsize: iterable containing minimum width and height of the image

Use (-1, -1) as minsize to bypass the image-size check.

Here are some Examples:

- Check if uploaded file is in any of supported image formats:

Its arguments are:

- ``maxip`` highest allowed address; same as above

All three example values are equal, since addresses are converted to

integers for inclusion check with following function:

``

number = 16777216 * IP[0] + 65536 * IP[1] + 256 * IP[2] + IP[3]

``:code

Examples:

Check for valid IPv4 address:

``

requires = IS_IPV4()

``:code

Check for valid private network IPv4 address:

``

requires = IS_IPV4(minip='192.168.0.1', maxip='192.168.255.255')

``:code

##### ``CLEANUP``

``CLEANUP``:inxx

This is a filter. It never fails. It just removes all characters whose decimal ASCII codes are not in the list [10, 13, 32-127].

``

requires = CLEANUP()

``:code

``

``:code

causes the hidden fields to be passed with the submission, no more, no less.

``form.accepts(...)`` is not

intended to read the received hidden fields and move them into

form.vars. The reason is security. Hidden fields can be tampered with.

So you have to do explicitly move hidden fields from the request to the form:

``

form.vars.a = request.vars.a

form = SQLFORM(..., hidden=dict(a='b'))

``:code

#### The ``process`` method

SQLFORM uses the process method (as do forms).

If you want to use keepvalues with an SQLFORM, you pass an argument to the process method:

``if form.process(keepvalues=True).accepted:``:code

#### ``SQLFORM`` and ``insert``/``update``/``delete``

If a form is created with SQLFORM, SQLFORM.factory or CRUD, there are multiple w

``

db.define_table('image',

Field('name', requires=IS_NOT_EMPTY()),

Field('source', 'upload'))

``:code

and upload action

``

def upload_image():

return dict(form=SQLFORM(db.image).process())

``:code

The simplest way to embed the form in the view for ``upload_image`` is

``

{{=form}}

``:code

This results in a standard table layout. If you wish to use a different layout, you can break the form into components

``

{{=form.custom.begin}}

{{=form.custom.end}}

``:code

where ``form.custom.widget[fieldname]`` gets serialized into the proper widget for the field. If the form is submitted and it contains errors, they are appended below the widgets, as usual.

The above sample form is show in the image below.

[[image @///image/en6500.png center 300px]]

A similar result could have been obtained without using a custom form:

``

SQLFORM(...,formstyle='table2cols')

``:code

or in case of CRUD forms with the following parameter:

``

crud.settings.formstyle='table2cols'

``:code

Or, to use no salt:

``

requires = CRYPT(salt=False)

``:code

The CRYPT validator hashes its input, and this makes it somewhat special. If you need to validate a password field before it is hashed, you can use CRYPT in a list of validators, but must make sure it is the last of the list, so that it is called last. For example:

``

requires = [IS_STRONG(),CRYPT(key='sha512:thisisthekey')]

``:code

``CRYPT`` also takes a ``min_length`` argument, which defaults to zero.

The resulting hash takes the form ``alg$salt$hash``, where ``alg`` is the hash algorithm used, ``salt`` is the salt string (which can be empty), and ``hash`` is the algorithm's output. Consequently, the hash is self-identifying, allowing, for example, the algorithm to be changed without invalidating previous hashes. The key, however, must remain the same.

#### Database validators

##### ``IS_NOT_IN_DB``

``IS_NOT_IN_DB``:inxx

Synopsis:

Consider the following example:

``

db.define_table('person', Field('name'))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

It requires that when you insert a new person, his/her name is not already in the database, ``db``, in the field ``person.name``.

As with all other validators this requirement is enforced at the form processing level, not at the database level. This means that there is a small probability that, if two visitors try to concurrently insert records with the same person.name, this results in a race condition and both records are accepted. It is therefore safer to also inform the database that this field should have a unique value:

``

db.define_table('person', Field('name', unique=True))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

Now if a race condition occurs, the database raises an OperationalError and one of the two inserts is rejected.

The first argument of ``IS_NOT_IN_DB`` can be a database connection or a Set. In the latter case, you would be checking only the set defined by the Set.

The following code, for example, does not allow registration of two persons with the same name within 10 days of each other:

``

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

Synopsis:

``IS_IN_DB(db|set,'table.value_field','%(representing_field)s',zero='choose one')``

where the third and fourth arguments are optional.

Consider the following tables and requirement:

``

ui = dict(

buttontable='icon rightarrow',

buttonview='icon magnifier')

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``showbuttontext`` allows to have buttons without text (there will effectively be only icons)

- ``_class`` is the class for the grid container.

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

``

``:code

causes the hidden fields to be passed with the submission, no more, no less.

``form.accepts(...)`` is not

intended to read the received hidden fields and move them into

form.vars. The reason is security. Hidden fields can be tampered with.

So you have to do explicitly move hidden fields from the request to the form:

``

form.vars.a = request.vars.a

form = SQLFORM(..., hidden=dict(a='b'))

``:code

#### The ``process`` method

SQLFORM uses the process method (as do forms).

If you want to use keepvalues with an SQLFORM, you pass an argument to the process method:

``if form.process(keepvalues=True).accepted:``:code

#### ``SQLFORM`` and ``insert``/``update``/``delete``

If a form is created with SQLFORM, SQLFORM.factory or CRUD, there are multiple w

``

db.define_table('image',

Field('name', requires=IS_NOT_EMPTY()),

Field('source', 'upload'))

``:code

and upload action

``

def upload_image():

return dict(form=SQLFORM(db.image).process())

``:code

The simplest way to embed the form in the view for ``upload_image`` is

``

{{=form}}

``:code

This results in a standard table layout. If you wish to use a different layout, you can break the form into components

``

{{=form.custom.begin}}

{{=form.custom.end}}

``:code

where ``form.custom.widget[fieldname]`` gets serialized into the proper widget for the field. If the form is submitted and it contains errors, they are appended below the widgets, as usual.

The above sample form is show in the image below.

[[image @///image/en6500.png center 300px]]

A similar result could have been obtained without using a custom form:

``

SQLFORM(...,formstyle='table2cols')

``:code

or in case of CRUD forms with the following parameter:

``

crud.settings.formstyle='table2cols'

``:code

Or, to use no salt:

``

requires = CRYPT(salt=False)

``:code

The CRYPT validator hashes its input, and this makes it somewhat special. If you need to validate a password field before it is hashed, you can use CRYPT in a list of validators, but must make sure it is the last of the list, so that it is called last. For example:

``

requires = [IS_STRONG(),CRYPT(key='sha512:thisisthekey')]

``:code

``CRYPT`` also takes a ``min_length`` argument, which defaults to zero.

The resulting hash takes the form ``alg$salt$hash``, where ``alg`` is the hash algorithm used, ``salt`` is the salt string (which can be empty), and ``hash`` is the algorithm's output. Consequently, the hash is self-identifying, allowing, for example, the algorithm to be changed without invalidating previous hashes. The key, however, must remain the same.

#### Database validators

##### ``IS_NOT_IN_DB``

``IS_NOT_IN_DB``:inxx

Synopsis:

Consider the following example:

``

db.define_table('person', Field('name'))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

It requires that when you insert a new person, his/her name is not already in the database, ``db``, in the field ``person.name``.

As with all other validators this requirement is enforced at the form processing level, not at the database level. This means that there is a small probability that, if two visitors try to concurrently insert records with the same person.name, this results in a race condition and both records are accepted. It is therefore safer to also inform the database that this field should have a unique value:

``

db.define_table('person', Field('name', unique=True))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

Now if a race condition occurs, the database raises an OperationalError and one of the two inserts is rejected.

The first argument of ``IS_NOT_IN_DB`` can be a database connection or a Set. In the latter case, you would be checking only the set defined by the Set.

The following code, for example, does not allow registration of two persons with the same name within 10 days of each other:

``

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

Synopsis:

``IS_IN_DB(db|set,'table.value_field','%(representing_field)s',zero='choose one')``

where the third and fourth arguments are optional.

Consider the following tables and requirement:

``

ui = dict(

buttontable='icon rightarrow',

buttonview='icon magnifier')

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``showbuttontext`` allows to have buttons without text (there will effectively be only icons)

- ``_class`` is the class for the grid container.

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

Now, edit the previous controller and add a new action:

``

def display_manual_form():

``:code

Now, edit the previous controller and add a new action:

``

def display_manual_form():

``:code

A run-time construction technique for SQLFORM.factory() is

``

fields = []

fields.append(Field(...))

form=SQLFORM.factory(*fields)

``:code

A run-time construction technique for SQLFORM.factory() is

``

fields = []

fields.append(Field(...))

form=SQLFORM.factory(*fields)

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``showbuttontext`` allows to have buttons without text (there will effectively be only icons)

- ``_class`` is the class for the grid container.

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``showbuttontext`` allows to have buttons without text (there will effectively be only icons)

- ``_class`` is the class for the grid container.

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

##### ``IS_NOT_IN_DB``

``IS_NOT_IN_DB``:inxx

Consider the following example:

``

db.define_table('person', Field('name'))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

``

db.define_table('person', Field('name', unique=True))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

Now if a race condition occurs, the database raises an OperationalError and one of the two inserts is rejected.

The first argument of ``IS_NOT_IN_DB`` can be a database connection or a Set. In the latter case, you would be checking only the set defined by the Set.

The following code, for example, does not allow registration of two persons with the same name within 10 days of each other:

``

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

Consider the following tables and requirement:

``

db.define_table('person', Field('name', unique=True))

db.define_table('dog', Field('name'), Field('owner', db.person)

db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s',

zero=T('choose one'))

``:code

##### ``IS_NOT_IN_DB``

``IS_NOT_IN_DB``:inxx

Consider the following example:

``

db.define_table('person', Field('name'))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

``

db.define_table('person', Field('name', unique=True))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

Now if a race condition occurs, the database raises an OperationalError and one of the two inserts is rejected.

The first argument of ``IS_NOT_IN_DB`` can be a database connection or a Set. In the latter case, you would be checking only the set defined by the Set.

The following code, for example, does not allow registration of two persons with the same name within 10 days of each other:

``

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

Consider the following tables and requirement:

``

db.define_table('person', Field('name', unique=True))

db.define_table('dog', Field('name'), Field('owner', db.person)

db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s',

zero=T('choose one'))

``:code

``:code

- ``field_id`` must be the field of the table to be used as ID, for example ``db.mytable.id``.

- ``left`` is an optional left join expressions used to build ``...select(left=...)``.

- ``headers`` is a dictionary that maps 'tablename.fieldname' into the corresponding header label, e.g. ``{'auth_user.email' : 'Email Address'}``

- ``orderby`` is used as default ordering for the rows.

- ``groupby`` is used to group the set. Use the same syntax as you were passing in a simple ``select(groupby=...)``.

- ``searchable``, ``sortable``, ``deletable``, ``editable``, ``details``, ``create`` determine whether one can search, sort, delete, edit, view details, and create new records respectively.

- ``selectable`` can be used to call a custom function on multiple records (a checkbox will be inserted for every row) e.g.

``

``:code

- ``paginate`` sets the max number of rows per page.

- ``csv`` if set to true allows to download the grid in various format (more on that later).

- ``links`` is used to display new columns which can be links to other pages. The ``links`` argument must be a list of ``dict(header='name',body=lambda row: A(...))`` where ``header`` is the header of the new column and ``body`` is a function that takes a row and returns a value. In the example, the value is a ``A(...)`` helper.

- ``links_in_grid`` if set to False, links will only be displayed in the "details" and "edit" page (so, not on the main grid)

- ``upload`` same as SQLFORM's one. web2py uses the action at that URL to download the file

- ``maxtextlength`` sets the maximum length of text to be displayed for each field value, in the grid view. This value can be overwritten for each field using ``maxtextlengths``, a dictionary of 'tablename.fieldname':length e.g. ``{'auth_user.email' : 50}``

- ``onvalidation``, ``oncreate``, ``onupdate`` and ``ondelete`` are callback functions. All but ``ondelete`` take a form object as input, ondelete takes the table and the record id

Because the edit/create form is an SQLFORM which extends FORM, these callbacks are essentially used in the same way as documented in the sections for FORM and SQLFORM.

``:code

- ``field_id`` must be the field of the table to be used as ID, for example ``db.mytable.id``.

- ``left`` is an optional left join expressions used to build ``...select(left=...)``.

- ``headers`` is a dictionary that maps 'tablename.fieldname' into the corresponding header label, e.g. ``{'auth_user.email' : 'Email Address'}``

- ``orderby`` is used as default ordering for the rows.

- ``groupby`` is used to group the set. Use the same syntax as you were passing in a simple ``select(groupby=...)``.

- ``searchable``, ``sortable``, ``deletable``, ``editable``, ``details``, ``create`` determine whether one can search, sort, delete, edit, view details, and create new records respectively.

- ``selectable`` can be used to call a custom function on multiple records (a checkbox will be inserted for every row) e.g.

``

``:code

- ``paginate`` sets the max number of rows per page.

- ``csv`` if set to true allows to download the grid in various format (more on that later).

- ``links`` is used to display new columns which can be links to other pages. The ``links`` argument must be a list of ``dict(header='name',body=lambda row: A(...))`` where ``header`` is the header of the new column and ``body`` is a function that takes a row and returns a value. In the example, the value is a ``A(...)`` helper.

- ``links_in_grid`` if set to False, links will only be displayed in the "details" and "edit" page (so, not on the main grid)

- ``upload`` same as SQLFORM's one. web2py uses the action at that URL to download the file

- ``maxtextlength`` sets the maximum length of text to be displayed for each field value, in the grid view. This value can be overwritten for each field using ``maxtextlengths``, a dictionary of 'tablename.fieldname':length e.g. ``{'auth_user.email' : 50}``

- ``onvalidation``, ``oncreate``, ``onupdate`` and ``ondelete`` are callback functions. All but ``ondelete`` take a form object as input, ondelete takes the table and the record id

Because the edit/create form is an SQLFORM which extends FORM, these callbacks are essentially used in the same way as documented in the sections for FORM and SQLFORM.

Here is an example:

``

db.define_table('numbers',

Field('a', 'integer'),

Field('b', 'integer'),

Field('c', 'integer', readable=False, writable=False))

def my_form_processing(form):

c = form.vars.a * form.vars.b

if c < 0:

form.errors.b = 'a*b cannot be negative'

else:

form.vars.c = c

def insert_numbers():

form = SQLFORM(db.numbers)

if form.process(onvalidation=my_form_processing).accepted:

session.flash = 'record inserted'

redirect(URL())

In the new controller, you do not need to build a ``FORM``, since the ``SQLFORM`

</tr>

<tr id="submit_record__row">

<td></td>

<td><input value="Submit" type="submit" /></td>

<td></td>

</tr>

</table>

<input value="9038845529" type="hidden" name="_formkey" />

<input value="person" type="hidden" name="_formname" />

</form>

``:code

The automatically generated form is more complex than the previous low-level form. First of all, it contains a table of rows, and each row has three columns. The first column contains the field labels (as determined from the ``db.person``), the second column contains the input fields (and eventually error messages), and the third column is optional and therefore empty (it can be populated with the fields in the ``SQLFORM`` constructor).

All tags in the form have names derived from the table and field name. This allows easy customization of the form using CSS and JavaScript. This capability is discussed in more detail in Chapter 11.

More important is that now the ``accepts`` method does a lot more work for you. As in the previous case, it performs validation of the input, but additionally, if the input passes validation, it also performs a database insert of the new record and stores in ``form.vars.id`` the unique "id" of the new record.

A ``SQLFORM`` object also deals automatically with "upload" fields by saving uploaded files in the "uploads" folder (after having them renamed safely to avoid conflicts and prevent directory traversal attacks) and stores their names (their new names) into the appropriate field in the database. After the form has been processed, the new filename is available in ``form.vars.fieldname`` (i.e., it replaces the ``cgi.FieldStorage`` object in ``request.vars.fieldname``), so you can easily reference the new name right after upload.

For example, consider the following model:

``

db.define_table('person',

Field('name', requires=IS_NOT_EMPTY()),

Field('married', 'boolean'),

Field('gender', requires=IS_IN_SET(['Male', 'Female', 'Other'])),

Field('profile', 'text'),

Field('image', 'upload'))

``:code

In this case, ``SQLFORM(db.person)`` generates the form shown below:

[[image @///image/en6200.png center 300px]]

The ``SQLFORM`` constructor allows various customizations, such as displaying only a subset of the fields, changing the labels, adding values to the optional third column, or creating UPDATE and DELETE forms, as opposed to INSERT forms like the current one.

``SQLFORM`` is the single biggest time-saver object in web2py.

The class ``SQLFORM`` is defined in "gluon/sqlhtml.py". It can be easily extended by overriding its ``xml`` method, the method that serializes the objects, to change its output.

col3 = {'name':A('what is this?',

For example, adding a URL-based back-button (for a multi-page form) and a renamed submit button:

``

buttons = [TAG.button('Back',_type="button",_onClick = "parent.location='%s' " % URL(...),

TAG.button('Next',_type="submit")]

``:code

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

There is a special ``hidden`` attribute. When a dictionary is passed as ``hidden``, its items are translated into "hidden" INPUT fields (see the example for the ``FORM`` helper in Chapter 5).

``

form = SQLFORM(....,hidden=...)

``:code

causes the hidden fields to be passed with the submission, no more, no less.

``form.accepts(...)`` is not

intended to read the received hidden fields and move them into

So you have to do explicitly move hidden fields from the request to the form:

``

form.vars.a = request.vars.a

form = SQLFORM(..., hidden=dict(a='b'))

``:code

#### ``SQLFORM`` and ``insert``/``update``/``delete``

``SQLFORM`` creates a new db record when the form is accepted. Assuming ``form=SQLFORM(db.test)``:code, then the id of the last-created record will be accessible in ``myform.vars.id``.

``delete record``:inxx

If you pass a record as the optional second argument to the ``SQLFORM`` constructor, the form becomes an UPDATE form for that record. This means that when the form is submitted the existing record is updated and no new record is inserted. If you set the argument ``deletable=True``, the UPDATE form displays a "check to delete" checkbox. If checked, the record is deleted.

------

If a form is submitted and the delete checkbox is checked the attribute

``form.deleted`` is set to ``True``.

------

You can modify the controller of the previous example so that when we pass an additional integer argument in the URL path, as in:

Normally uploaded files are stored into "app/uploads" but you can specify an alt

Field('image', 'upload', uploadfolder='...')

``

In most operating system, accessing the file system can become slow when there are many files in the same folder. If you plan to upload more than 1000 files you can ask web2py to organize the uploads in subfolders:

``

Field('image', 'upload', uploadseparate=True)

``

#### Storing the original filename

web2py automatically stores the original filename inside the new UUID filename and retrieves it when the file is downloaded. Upon download, the original filename is stored in the content-disposition header of the HTTP response. This is all done transparently without the need for programming.

Occasionally you may want to store the original filename in a database field. In this case, you need to modify the model and add a field to store it in:

``

db.define_table('person',

Field('name', requires=IS_NOT_EMPTY()),

Field('image_filename'),

Field('image', 'upload'))

``:code

``

def display_form():

record = db.person(request.args(0)) or redirect(URL('index'))

url = URL('download')

form = SQLFORM(db.person, record, deletable=True,

upload=url, fields=['name', 'image'])

if request.vars.image!=None:

form.vars.image_filename = request.vars.image.filename

if form.process().accepted:

response.flash = 'form accepted'

elif form.errors:

response.flash = 'form has errors'

return dict(form=form)

``:code

Notice that the ``SQLFORM`` does not display the "image_filename" field.

The "display_form" action moves the filename of the ``request.vars.image``

into the ``form.vars.image_filename``, so that it gets processed by ``accepts`` and

stored in the database. The download function, before serving the file, checks in the database for the original filename and uses it in the content-disposition header.

form.process(detect_record_change=True)``

and it is set in:

``

crud.settings.detect_record_change = True

``:code

and it can be changed/disabled by setting the variable to ``False``.

You can change the form style by

``

crud.settings.formstyle = 'table3cols' or 'table2cols' or 'divs' or 'ul'

``:code

You can set the separator in all crud forms:

``

crud.settings.label_separator = ':'

``:code

You can add captcha to forms, using the same convention explained for auth, with:

``

crud.settings.create_captcha = None

crud.settings.update_captcha = None

crud.settings.captcha = None

``:code

#### Messages

Here is a list of customizable messages:

``

crud.messages.submit_button = 'Submit'

``:code

sets the text of the "submit" button for both create and update forms.

``

crud.messages.delete_label = 'Check to delete:'

``:code

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

Consider the following tables and requirement:

``

db.define_table('person', Field('name', unique=True))

db.define_table('dog', Field('name'), Field('owner', db.person)

db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s',

zero=T('choose one'))

``:code

The ``zero`` option works very much like for the ``IS_IN_SET`` validator.

``

def index():

(...)

query = (db.table.field == 'xyz') #in practice 'xyz' would be a variable

db.table.field.requires=IS_IN_DB(db(query),....)

form=SQLFORM(...)

if form.process().accepted: ...

(...)

``:code

``

db.dog.owner.requires = [IS_IN_DB(db, 'person.id', '%(name)s')]

``:code

``_and``:inxx

``

subset=db(db.person.id>100)

db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s',

_and=IS_NOT_IN_DB(subset,'person.id'))

``:code

``IS_IN_DB`` also takes a ``cache`` argument that works like the ``cache`` argument of select.

##### ``IS_IN_DB`` and Tagging

``tags``:inxx

``multiple``:inxx

The ``IS_IN_DB`` validator has an optional attribute ``multiple=False``. If set to ``True`` multiple values can be stored in one field. This field should be of type ``list:reference`` as discussed in Chapter 6. An explicit example of tagging is discussed there. ``multiple`` references are handled automatically in create and update forms, but they are transparent to the DAL. We strongly suggest using the jQuery multiselect plugin to render multiple fields.

#### Custom validators

``custom validator``:inxx

All validators follow the prototype below:

``

class sample_validator:

def __init__(self, *a, error_message='error'):

self.a = a

self.e = error_message

def __call__(self, value):

The first case is easy:

db.define_table('category',Field('name'))

db.define_table('product',Field('name'),Field('category'))

db.product.category.widget = SQLFORM.widgets.autocomplete(

request, db.category.name, limitby=(0,10), min_length=2)

``:code

Where ``limitby`` instructs the widget to display no more than 10 suggestions at the time, and ``min_length`` instructs the widget to perform an Ajax callback to fetch suggestions only after the user has typed at least 2 characters in the search box.

The second case is more complex:

``

db.define_table('category',Field('name'))

db.define_table('product',Field('name'),Field('category'))

db.product.category.widget = SQLFORM.widgets.autocomplete(

request, db.category.name, id_field=db.category.id)

``:code

In this case the value of ``id_field`` tells the widget that even if the value to be autocompleted is a ``db.category.name``, the value to be stored is the corresponding ``db.category.id``. An optional parameter is ``orderby`` that instructs the widget on how to sort the suggestions (alphabetical by default).

This widget works via Ajax. Where is the Ajax callback? Some magic is going on in this widget. The callback is a method of the widget object itself. How is it exposed? In web2py any piece of code can generate a response by raising an HTTP exception. This widget exploits this possibility in the following way: the widget sends the Ajax call to the same URL that generated the widget in the first place and puts a special token in the request.vars. Should the widget get instantiated again, it finds the token and raises an HTTP exception that responds to the request. All of this is done under the hood and hidden to the developer.

-------

Attention: grid and smartgrid were experimental prior web2py version 2.0 and were vulnerable to information leakage. The grid and smartgrid are no longer experimental, but we are still not promising backward compatibility of the presentation layer of the grid, only of its APIs.

-------

These are two high level objects that create complex CRUD controls. They provide pagination, the ability to browse, search, sort, create, update and delete records from a single object.

Because web2py's HTML objects build on the underlying, simpler objects, the grids create SQLFORMs for viewing, editing and creating its rows. Many of the arguments to the grids are passed through to this SQLFORM. This means the documentation for SQLFORM (and FORM) is relevant. For example, the grid takes an ``onvalidation`` callback. The processing logic of the grid ultimately passes this through to the underlying process() method of FORM, which means you should consult the documentation of ``onvalidation`` for FORMs.

As the grid passes through different states, such as editing a row, a new request is generated. request.args has information about which state the grid is in.

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid object will provide access to records matching the query.

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

``:code

but we do not recommend it.

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

To make the default search grid work in more than one LOADed grid, please use a different ``formname`` for each one.

-----

``

@auth.requires_login()

def manage():

table = request.args(0)

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.grid(db[table],args=request.args[:1])

return locals()

``:code

the ``args`` argument of the ``grid`` specifies which ``request.args`` should be passed along and ignored by the object. In our case ``request.args[:1]`` is the name of the table we want to manage and it is handled by the ``manage`` function itself, not by the object.

The complete signature for the grid is the following:

``

SQLFORM.grid(

query,

fields=None,

field_id=None,

left=None,

headers={},

orderby=None,

groupby=None,

searchable=True,

sortable=True,

paginate=20,

deletable=True,

editable=True,

details=True,

selectable=None,

create=True,

csv=True,

ui = dict(

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

In versions of web2py after 2.6, virtual fields are shown in grids like normal fields: either shown alongside all other fields by default, or by including them in the ``fields`` argument. However, virtual fields are not sortable.

In older web2py versions, showing virtual fields in a grid requires use of the ``links`` argument. This is still supported for more recent versions. If table db.t1 has a field called t1.vfield which is based on the values of t1.field1 and t1.field2, do this:

``grid = SQLFORM.grid(db.t1, ..., fields = [t1.field1, t1.field2,...],

links = [dict(header='Virtual Field 1',body=lamba row:row.vfield),...] )

``:code

Note that when defining the virtual field, the lambda function must qualify fields with the database name, but in the links argument, this is not necessary.

So for the example above, the virtual field may be defined like:

``

db.define_table('t1',Field('field1','string'),

Field('field2','string'),

Field.Virtual('virtual1', lambda row: row.t1.field1 + row.t1.field2),

...)

``:code

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

For example consider the following table structure:

``

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

``:code

all children:

``

SQLFORM.grid(db.child)

``:code

and all parents and children in one table:

``

Also notice that when clicking on the "children" link for a given parent one onl

@auth.requires_login():

def manage():

db.child.parent.writable = False

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

return locals()

``:code

If the ``linked_tables`` argument is not specified all referencing tables are automatically linked. Anyway, to avoid accidentally exposing data we recommend explicitly listing tables that should be linked.

The following code creates a very powerful management interface for all tables in the system:

``

@auth.requires_membership('managers'):

def manage():

table = request.args(0) or 'auth_user'

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.smartgrid(db[table],args=request.args[:1])

return locals()

``:code

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some caveats:

- The first argument is a table, not a query

- There is a extra argument ``constraints`` which is a dictionary of 'tablename':query which can be used to further restrict access to the records displayed in the 'tablename' grid.

- There is a extra argument ``linked_tables`` which is a list of tablenames of tables that should be accessible via the smartgrid.

- ``divider`` allows to specify a character to use in the breadcrumb navigator, ``breadcrumbs_class`` will apply the class to the breadcrumb element

- All the arguments but the table, ``args``, ``linked_tables`` and ``user_signatures`` can be dictionaries as explained below.

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

``:code

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

``:code

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

``:code

In this way we made parents searchable but children for each parent not searchable (there should not be that many to need the search widget).

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

``:code

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

``:code

The ``smartgrid`` is the only object in web2py that displays the table name and it need both the singular and the plural. For example one parent can have one "Child" or many "Children". Therefore a table object needs to know its own singular and plural names. web2py normally guesses them but you can set the explicitly:

Here is an example:

``

db.define_table('numbers',

Field('a', 'integer'),

Field('b', 'integer'),

Field('c', 'integer', readable=False, writable=False))

def my_form_processing(form):

c = form.vars.a * form.vars.b

if c < 0:

form.errors.b = 'a*b cannot be negative'

else:

form.vars.c = c

def insert_numbers():

form = SQLFORM(db.numbers)

if form.process(onvalidation=my_form_processing).accepted:

session.flash = 'record inserted'

redirect(URL())

In the new controller, you do not need to build a ``FORM``, since the ``SQLFORM`

</tr>

<tr id="submit_record__row">

<td></td>

<td><input value="Submit" type="submit" /></td>

<td></td>

</tr>

</table>

<input value="9038845529" type="hidden" name="_formkey" />

<input value="person" type="hidden" name="_formname" />

</form>

``:code

The automatically generated form is more complex than the previous low-level form. First of all, it contains a table of rows, and each row has three columns. The first column contains the field labels (as determined from the ``db.person``), the second column contains the input fields (and eventually error messages), and the third column is optional and therefore empty (it can be populated with the fields in the ``SQLFORM`` constructor).

All tags in the form have names derived from the table and field name. This allows easy customization of the form using CSS and JavaScript. This capability is discussed in more detail in Chapter 11.

More important is that now the ``accepts`` method does a lot more work for you. As in the previous case, it performs validation of the input, but additionally, if the input passes validation, it also performs a database insert of the new record and stores in ``form.vars.id`` the unique "id" of the new record.

A ``SQLFORM`` object also deals automatically with "upload" fields by saving uploaded files in the "uploads" folder (after having them renamed safely to avoid conflicts and prevent directory traversal attacks) and stores their names (their new names) into the appropriate field in the database. After the form has been processed, the new filename is available in ``form.vars.fieldname`` (i.e., it replaces the ``cgi.FieldStorage`` object in ``request.vars.fieldname``), so you can easily reference the new name right after upload.

For example, consider the following model:

``

db.define_table('person',

Field('name', requires=IS_NOT_EMPTY()),

Field('married', 'boolean'),

Field('gender', requires=IS_IN_SET(['Male', 'Female', 'Other'])),

Field('profile', 'text'),

Field('image', 'upload'))

``:code

In this case, ``SQLFORM(db.person)`` generates the form shown below:

[[image @///image/en6200.png center 300px]]

The ``SQLFORM`` constructor allows various customizations, such as displaying only a subset of the fields, changing the labels, adding values to the optional third column, or creating UPDATE and DELETE forms, as opposed to INSERT forms like the current one.

``SQLFORM`` is the single biggest time-saver object in web2py.

The class ``SQLFORM`` is defined in "gluon/sqlhtml.py". It can be easily extended by overriding its ``xml`` method, the method that serializes the objects, to change its output.

col3 = {'name':A('what is this?',

For example, adding a URL-based back-button (for a multi-page form) and a renamed submit button:

``

buttons = [TAG.button('Back',_type="button",_onClick = "parent.location='%s' " % URL(...),

TAG.button('Next',_type="submit")]

``:code

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

There is a special ``hidden`` attribute. When a dictionary is passed as ``hidden``, its items are translated into "hidden" INPUT fields (see the example for the ``FORM`` helper in Chapter 5).

``

form = SQLFORM(....,hidden=...)

``:code

causes the hidden fields to be passed with the submission, no more, no less.

``form.accepts(...)`` is not

intended to read the received hidden fields and move them into

So you have to do explicitly move hidden fields from the request to the form:

``

form.vars.a = request.vars.a

form = SQLFORM(..., hidden=dict(a='b'))

``:code

#### ``SQLFORM`` and ``insert``/``update``/``delete``

``SQLFORM`` creates a new db record when the form is accepted. Assuming ``form=SQLFORM(db.test)``:code, then the id of the last-created record will be accessible in ``myform.vars.id``.

``delete record``:inxx

If you pass a record as the optional second argument to the ``SQLFORM`` constructor, the form becomes an UPDATE form for that record. This means that when the form is submitted the existing record is updated and no new record is inserted. If you set the argument ``deletable=True``, the UPDATE form displays a "check to delete" checkbox. If checked, the record is deleted.

------

If a form is submitted and the delete checkbox is checked the attribute

``form.deleted`` is set to ``True``.

------

You can modify the controller of the previous example so that when we pass an additional integer argument in the URL path, as in:

Normally uploaded files are stored into "app/uploads" but you can specify an alt

Field('image', 'upload', uploadfolder='...')

``

In most operating system, accessing the file system can become slow when there are many files in the same folder. If you plan to upload more than 1000 files you can ask web2py to organize the uploads in subfolders:

``

Field('image', 'upload', uploadseparate=True)

``

#### Storing the original filename

web2py automatically stores the original filename inside the new UUID filename and retrieves it when the file is downloaded. Upon download, the original filename is stored in the content-disposition header of the HTTP response. This is all done transparently without the need for programming.

Occasionally you may want to store the original filename in a database field. In this case, you need to modify the model and add a field to store it in:

``

db.define_table('person',

Field('name', requires=IS_NOT_EMPTY()),

Field('image_filename'),

Field('image', 'upload'))

``:code

``

def display_form():

record = db.person(request.args(0)) or redirect(URL('index'))

url = URL('download')

form = SQLFORM(db.person, record, deletable=True,

upload=url, fields=['name', 'image'])

if request.vars.image!=None:

form.vars.image_filename = request.vars.image.filename

if form.process().accepted:

response.flash = 'form accepted'

elif form.errors:

response.flash = 'form has errors'

return dict(form=form)

``:code

Notice that the ``SQLFORM`` does not display the "image_filename" field.

The "display_form" action moves the filename of the ``request.vars.image``

into the ``form.vars.image_filename``, so that it gets processed by ``accepts`` and

stored in the database. The download function, before serving the file, checks in the database for the original filename and uses it in the content-disposition header.

form.process(detect_record_change=True)``

and it is set in:

``

crud.settings.detect_record_change = True

``:code

and it can be changed/disabled by setting the variable to ``False``.

You can change the form style by

``

crud.settings.formstyle = 'table3cols' or 'table2cols' or 'divs' or 'ul'

``:code

You can set the separator in all crud forms:

``

crud.settings.label_separator = ':'

``:code

You can add captcha to forms, using the same convention explained for auth, with:

``

crud.settings.create_captcha = None

crud.settings.update_captcha = None

crud.settings.captcha = None

``:code

#### Messages

Here is a list of customizable messages:

``

crud.messages.submit_button = 'Submit'

``:code

sets the text of the "submit" button for both create and update forms.

``

crud.messages.delete_label = 'Check to delete:'

``:code

import datetime

now = datetime.datetime.today()

db.define_table('person',

Field('name'),

Field('registration_stamp', 'datetime', default=now))

recent = db(db.person.registration_stamp>now-datetime.timedelta(10))

db.person.name.requires = IS_NOT_IN_DB(recent, 'person.name')

``:code

##### ``IS_IN_DB``

``IS_IN_DB``:inxx

Consider the following tables and requirement:

``

db.define_table('person', Field('name', unique=True))

db.define_table('dog', Field('name'), Field('owner', db.person)

db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s',

zero=T('choose one'))

``:code

The ``zero`` option works very much like for the ``IS_IN_SET`` validator.

``

def index():

(...)

query = (db.table.field == 'xyz') #in practice 'xyz' would be a variable

db.table.field.requires=IS_IN_DB(db(query),....)

form=SQLFORM(...)

if form.process().accepted: ...

(...)

``:code

``

db.dog.owner.requires = [IS_IN_DB(db, 'person.id', '%(name)s')]

``:code

``_and``:inxx

``

subset=db(db.person.id>100)

db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s',

_and=IS_NOT_IN_DB(subset,'person.id'))

``:code

``IS_IN_DB`` also takes a ``cache`` argument that works like the ``cache`` argument of select.

##### ``IS_IN_DB`` and Tagging

``tags``:inxx

``multiple``:inxx

The ``IS_IN_DB`` validator has an optional attribute ``multiple=False``. If set to ``True`` multiple values can be stored in one field. This field should be of type ``list:reference`` as discussed in Chapter 6. An explicit example of tagging is discussed there. ``multiple`` references are handled automatically in create and update forms, but they are transparent to the DAL. We strongly suggest using the jQuery multiselect plugin to render multiple fields.

#### Custom validators

``custom validator``:inxx

All validators follow the prototype below:

``

class sample_validator:

def __init__(self, *a, error_message='error'):

self.a = a

self.e = error_message

def __call__(self, value):

The first case is easy:

db.define_table('category',Field('name'))

db.define_table('product',Field('name'),Field('category'))

db.product.category.widget = SQLFORM.widgets.autocomplete(

request, db.category.name, limitby=(0,10), min_length=2)

``:code

Where ``limitby`` instructs the widget to display no more than 10 suggestions at the time, and ``min_length`` instructs the widget to perform an Ajax callback to fetch suggestions only after the user has typed at least 2 characters in the search box.

The second case is more complex:

``

db.define_table('category',Field('name'))

db.define_table('product',Field('name'),Field('category'))

db.product.category.widget = SQLFORM.widgets.autocomplete(

request, db.category.name, id_field=db.category.id)

``:code

In this case the value of ``id_field`` tells the widget that even if the value to be autocompleted is a ``db.category.name``, the value to be stored is the corresponding ``db.category.id``. An optional parameter is ``orderby`` that instructs the widget on how to sort the suggestions (alphabetical by default).

This widget works via Ajax. Where is the Ajax callback? Some magic is going on in this widget. The callback is a method of the widget object itself. How is it exposed? In web2py any piece of code can generate a response by raising an HTTP exception. This widget exploits this possibility in the following way: the widget sends the Ajax call to the same URL that generated the widget in the first place and puts a special token in the request.vars. Should the widget get instantiated again, it finds the token and raises an HTTP exception that responds to the request. All of this is done under the hood and hidden to the developer.

-------

Attention: grid and smartgrid were experimental prior web2py version 2.0 and were vulnerable to information leakage. The grid and smartgrid are no longer experimental, but we are still not promising backward compatibility of the presentation layer of the grid, only of its APIs.

-------

These are two high level objects that create complex CRUD controls. They provide pagination, the ability to browse, search, sort, create, update and delete records from a single object.

Because web2py's HTML objects build on the underlying, simpler objects, the grids create SQLFORMs for viewing, editing and creating its rows. Many of the arguments to the grids are passed through to this SQLFORM. This means the documentation for SQLFORM (and FORM) is relevant. For example, the grid takes an ``onvalidation`` callback. The processing logic of the grid ultimately passes this through to the underlying process() method of FORM, which means you should consult the documentation of ``onvalidation`` for FORMs.

As the grid passes through different states, such as editing a row, a new request is generated. request.args has information about which state the grid is in.

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid object will provide access to records matching the query.

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

``:code

but we do not recommend it.

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

To make the default search grid work in more than one LOADed grid, please use a different ``formname`` for each one.

-----

``

@auth.requires_login()

def manage():

table = request.args(0)

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.grid(db[table],args=request.args[:1])

return locals()

``:code

the ``args`` argument of the ``grid`` specifies which ``request.args`` should be passed along and ignored by the object. In our case ``request.args[:1]`` is the name of the table we want to manage and it is handled by the ``manage`` function itself, not by the object.

The complete signature for the grid is the following:

``

SQLFORM.grid(

query,

fields=None,

field_id=None,

left=None,

headers={},

orderby=None,

groupby=None,

searchable=True,

sortable=True,

paginate=20,

deletable=True,

editable=True,

details=True,

selectable=None,

create=True,

csv=True,

ui = dict(

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

In versions of web2py after 2.6, virtual fields are shown in grids like normal fields: either shown alongside all other fields by default, or by including them in the ``fields`` argument. However, virtual fields are not sortable.

In older web2py versions, showing virtual fields in a grid requires use of the ``links`` argument. This is still supported for more recent versions. If table db.t1 has a field called t1.vfield which is based on the values of t1.field1 and t1.field2, do this:

``grid = SQLFORM.grid(db.t1, ..., fields = [t1.field1, t1.field2,...],

links = [dict(header='Virtual Field 1',body=lamba row:row.vfield),...] )

``:code

Note that when defining the virtual field, the lambda function must qualify fields with the database name, but in the links argument, this is not necessary.

So for the example above, the virtual field may be defined like:

``

db.define_table('t1',Field('field1','string'),

Field('field2','string'),

Field.Virtual('virtual1', lambda row: row.t1.field1 + row.t1.field2),

...)

``:code

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

For example consider the following table structure:

``

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

``:code

all children:

``

SQLFORM.grid(db.child)

``:code

and all parents and children in one table:

``

Also notice that when clicking on the "children" link for a given parent one onl

@auth.requires_login():

def manage():

db.child.parent.writable = False

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

return locals()

``:code

If the ``linked_tables`` argument is not specified all referencing tables are automatically linked. Anyway, to avoid accidentally exposing data we recommend explicitly listing tables that should be linked.

The following code creates a very powerful management interface for all tables in the system:

``

@auth.requires_membership('managers'):

def manage():

table = request.args(0) or 'auth_user'

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.smartgrid(db[table],args=request.args[:1])

return locals()

``:code

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some caveats:

- The first argument is a table, not a query

- There is a extra argument ``constraints`` which is a dictionary of 'tablename':query which can be used to further restrict access to the records displayed in the 'tablename' grid.

- There is a extra argument ``linked_tables`` which is a list of tablenames of tables that should be accessible via the smartgrid.

- ``divider`` allows to specify a character to use in the breadcrumb navigator, ``breadcrumbs_class`` will apply the class to the breadcrumb element

- All the arguments but the table, ``args``, ``linked_tables`` and ``user_signatures`` can be dictionaries as explained below.

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

``:code

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

``:code

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

``:code

In this way we made parents searchable but children for each parent not searchable (there should not be that many to need the search widget).

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

``:code

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

``:code

The ``smartgrid`` is the only object in web2py that displays the table name and it need both the singular and the plural. For example one parent can have one "Child" or many "Children". Therefore a table object needs to know its own singular and plural names. web2py normally guesses them but you can set the explicitly:

#### ``SQLFORM.grid``

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

``:code

but we do not recommend it.

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

To make the default search grid work in more than one LOADed grid, please use a different ``formname`` for each one.

-----

``

@auth.requires_login()

def manage():

table = request.args(0)

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.grid(db[table],args=request.args[:1])

return locals()

``:code

The complete signature for the grid is the following:

``

SQLFORM.grid(

query,

fields=None,

field_id=None,

left=None,

headers={},

orderby=None,

groupby=None,

searchable=True,

sortable=True,

paginate=20,

deletable=True,

editable=True,

details=True,

selectable=None,

create=True,

SQLFORM.grid(

showbuttontext=True,

_class="web2py_grid",

formname='web2py_grid',

search_widget='default',

ignore_rw = False,

formstyle = 'table3cols',

exportclasses = None,

formargs={},

createargs={},

editargs={},

viewargs={},

buttons_placement = 'right',

links_placement = 'right'

)

``:code

- ``fields`` is a list of fields to be fetched from the database. It is also used to determine which fields to be shown in the grid view. However, it doesn't control what is displayed in the separate form used to edit rows. For that, use the readable and writable attribute of the database fields. For example, in a editable grid, suppress updating of a field like this: before creating the SQLFORM.grid, set

``

db.my_table.a_field.writable = False

db.my_table.a_field.readable = False

``:code

- ``field_id`` must be the field of the table to be used as ID, for example ``db.mytable.id``.

- ``left`` is an optional left join expressions used to build ``...select(left=...)``.

- ``headers`` is a dictionary that maps 'tablename.fieldname' into the corresponding header label, e.g. ``{'auth_user.email' : 'Email Address'}``

- ``orderby`` is used as default ordering for the rows.

- ``groupby`` is used to group the set. Use the same syntax as you were passing in a simple ``select(groupby=...)``.

- ``searchable``, ``sortable``, ``deletable``, ``editable``, ``details``, ``create`` determine whether one can search, sort, delete, edit, view details, and create new records respectively.

- ``selectable`` can be used to call a custom function on multiple records (a checkbox will be inserted for every row) e.g.

``

selectable = lambda ids : redirect(URL('default', 'mapping_multiple', vars=dict(id=ids)))

``:code

- ``paginate`` sets the max number of rows per page.

- ``csv`` if set to true allows to download the grid in various format (more on that later).

- ``links`` is used to display new columns which can be links to other pages. The ``links`` argument must be a list of ``dict(header='name',body=lambda row: A(...))`` where ``header`` is the header of the new column and ``body`` is a function that takes a row and returns a value. In the example, the value is a ``A(...)`` helper.

- ``links_in_grid`` if set to False, links will only be displayed in the "details" and "edit" page (so, not on the main grid)

- ``upload`` same as SQLFORM's one. web2py uses the action at that URL to download the file

- ``maxtextlength`` sets the maximum length of text to be displayed for each field value, in the grid view. This value can be overwritten for each field using ``maxtextlengths``, a dictionary of 'tablename.fieldname':length e.g. ``{'auth_user.email' : 50}``

- ``onvalidation``, ``oncreate``, ``onupdate`` and ``ondelete`` are callback functions. All but ``ondelete`` take a form object as input, ondelete takes the table and the record id

``

def myonvalidation(form):

print form.vars

def myoncreate(form):

print 'create!'

print form.vars

def myonupdate(form):

print 'update!'

print form.vars

def myondelete(table, id):

print 'delete!'

print table, id

``:code

- ``sorter_icons`` is a list of two strings (or helpers) that will be used to represent the up and down sorting options for each field.

- ``ui`` can be set equal to 'web2py' and will generate web2py friendly class names, can be set equal to ``jquery-ui`` and will generate jquery UI friendly class names, but it can also be its own set of class names for the various grid components:

``

ui = dict(

widget='',

header='',

content='',

default='',

cornerall='',

cornertop='',

cornerbottom='',

button='button',

buttontext='buttontext button',

buttonadd='icon plus',

buttonback='icon leftarrow',

buttonexport='icon downarrow',

buttondelete='icon trash',

buttonedit='icon pen',

buttontable='icon rightarrow',

buttonview='icon magnifier')

ui = dict(

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

##### ``Showing virtual fields in SQLFORM.grid and smartgrid``

``grid = SQLFORM.grid(db.t1, ..., fields = [t1.field1, t1.field2,...],

``:code

In all cases, because t1.vfield depends on t1.field1 and t1.field2, these fields must be present in the row. In the example above, this is guaranteed by including t1.field1 and t1.field2 in the fields argument. Alternatively, showing all fields will also work. You can suppress a field from displaying by setting the readable attibute to False.

Note that when defining the virtual field, the lambda function must qualify fields with the database name, but in the links argument, this is not necessary.

So for the example above, the virtual field may be defined like:

``

db.define_table('t1',Field('field1','string'),

Field('field2','string'),

Field.Virtual('virtual1', lambda row: row.t1.field1 + row.t1.field2),

...)

``:code

#### ``SQLFORM.smartgrid``

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

For example consider the following table structure:

``

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

``:code

all children:

``

SQLFORM.grid(db.child)

``:code

and all parents and children in one table:

``

SQLFORM.grid(db.parent,left=db.child.on(db.child.parent==db.parent.id))

``:code

``

@auth.requires_login()

def manage():

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

return locals()

``:code

which looks like this:

[[image @///image/en6800.png center 480px]]

``

@auth.requires_login():

def manage():

db.child.parent.writable = False

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

return locals()

``:code

If the ``linked_tables`` argument is not specified all referencing tables are automatically linked. Anyway, to avoid accidentally exposing data we recommend explicitly listing tables that should be linked.

The following code creates a very powerful management interface for all tables in the system:

``

@auth.requires_membership('managers'):

def manage():

table = request.args(0) or 'auth_user'

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.smartgrid(db[table],args=request.args[:1])

return locals()

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

``:code

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

``:code

for a smartgrid we would pass a dictionary of booleans:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

``:code

In this way we made parents searchable but children for each parent not searchable (there should not be that many to need the search widget).

-------

-------

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

``:code

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

``:code

#### ``SQLFORM.grid``

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

``:code

but we do not recommend it.

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

To make the default search grid work in more than one LOADed grid, please use a different ``formname`` for each one.

-----

``

@auth.requires_login()

def manage():

table = request.args(0)

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.grid(db[table],args=request.args[:1])

return locals()

``:code

The complete signature for the grid is the following:

``

SQLFORM.grid(

query,

fields=None,

field_id=None,

left=None,

headers={},

orderby=None,

groupby=None,

searchable=True,

sortable=True,

paginate=20,

deletable=True,

editable=True,

details=True,

selectable=None,

create=True,

SQLFORM.grid(

showbuttontext=True,

_class="web2py_grid",

formname='web2py_grid',

search_widget='default',

ignore_rw = False,

formstyle = 'table3cols',

exportclasses = None,

formargs={},

createargs={},

editargs={},

viewargs={},

buttons_placement = 'right',

links_placement = 'right'

)

``:code

- ``fields`` is a list of fields to be fetched from the database. It is also used to determine which fields to be shown in the grid view. However, it doesn't control what is displayed in the separate form used to edit rows. For that, use the readable and writable attribute of the database fields. For example, in a editable grid, suppress updating of a field like this: before creating the SQLFORM.grid, set

``

db.my_table.a_field.writable = False

db.my_table.a_field.readable = False

``:code

- ``field_id`` must be the field of the table to be used as ID, for example ``db.mytable.id``.

- ``left`` is an optional left join expressions used to build ``...select(left=...)``.

- ``headers`` is a dictionary that maps 'tablename.fieldname' into the corresponding header label, e.g. ``{'auth_user.email' : 'Email Address'}``

- ``orderby`` is used as default ordering for the rows.

- ``groupby`` is used to group the set. Use the same syntax as you were passing in a simple ``select(groupby=...)``.

- ``searchable``, ``sortable``, ``deletable``, ``editable``, ``details``, ``create`` determine whether one can search, sort, delete, edit, view details, and create new records respectively.

- ``selectable`` can be used to call a custom function on multiple records (a checkbox will be inserted for every row) e.g.

``

selectable = lambda ids : redirect(URL('default', 'mapping_multiple', vars=dict(id=ids)))

``:code

- ``paginate`` sets the max number of rows per page.

- ``csv`` if set to true allows to download the grid in various format (more on that later).

- ``links`` is used to display new columns which can be links to other pages. The ``links`` argument must be a list of ``dict(header='name',body=lambda row: A(...))`` where ``header`` is the header of the new column and ``body`` is a function that takes a row and returns a value. In the example, the value is a ``A(...)`` helper.

- ``links_in_grid`` if set to False, links will only be displayed in the "details" and "edit" page (so, not on the main grid)

- ``upload`` same as SQLFORM's one. web2py uses the action at that URL to download the file

- ``maxtextlength`` sets the maximum length of text to be displayed for each field value, in the grid view. This value can be overwritten for each field using ``maxtextlengths``, a dictionary of 'tablename.fieldname':length e.g. ``{'auth_user.email' : 50}``

- ``onvalidation``, ``oncreate``, ``onupdate`` and ``ondelete`` are callback functions. All but ``ondelete`` take a form object as input, ondelete takes the table and the record id

``

def myonvalidation(form):

print form.vars

def myoncreate(form):

print 'create!'

print form.vars

def myonupdate(form):

print 'update!'

print form.vars

def myondelete(table, id):

print 'delete!'

print table, id

``:code

- ``sorter_icons`` is a list of two strings (or helpers) that will be used to represent the up and down sorting options for each field.

- ``ui`` can be set equal to 'web2py' and will generate web2py friendly class names, can be set equal to ``jquery-ui`` and will generate jquery UI friendly class names, but it can also be its own set of class names for the various grid components:

``

ui = dict(

widget='',

header='',

content='',

default='',

cornerall='',

cornertop='',

cornerbottom='',

button='button',

buttontext='buttontext button',

buttonadd='icon plus',

buttonback='icon leftarrow',

buttonexport='icon downarrow',

buttondelete='icon trash',

buttonedit='icon pen',

buttontable='icon rightarrow',

buttonview='icon magnifier')

ui = dict(

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

##### ``Showing virtual fields in SQLFORM.grid and smartgrid``

``grid = SQLFORM.grid(db.t1, ..., fields = [t1.field1, t1.field2,...],

``:code

In all cases, because t1.vfield depends on t1.field1 and t1.field2, these fields must be present in the row. In the example above, this is guaranteed by including t1.field1 and t1.field2 in the fields argument. Alternatively, showing all fields will also work. You can suppress a field from displaying by setting the readable attibute to False.

Note that when defining the virtual field, the lambda function must qualify fields with the database name, but in the links argument, this is not necessary.

So for the example above, the virtual field may be defined like:

``

db.define_table('t1',Field('field1','string'),

Field('field2','string'),

Field.Virtual('virtual1', lambda row: row.t1.field1 + row.t1.field2),

...)

``:code

#### ``SQLFORM.smartgrid``

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

For example consider the following table structure:

``

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

``:code

all children:

``

SQLFORM.grid(db.child)

``:code

and all parents and children in one table:

``

SQLFORM.grid(db.parent,left=db.child.on(db.child.parent==db.parent.id))

``:code

``

@auth.requires_login()

def manage():

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

return locals()

``:code

which looks like this:

[[image @///image/en6800.png center 480px]]

``

@auth.requires_login():

def manage():

db.child.parent.writable = False

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

return locals()

``:code

If the ``linked_tables`` argument is not specified all referencing tables are automatically linked. Anyway, to avoid accidentally exposing data we recommend explicitly listing tables that should be linked.

The following code creates a very powerful management interface for all tables in the system:

``

@auth.requires_membership('managers'):

def manage():

table = request.args(0) or 'auth_user'

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.smartgrid(db[table],args=request.args[:1])

return locals()

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

``:code

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

``:code

for a smartgrid we would pass a dictionary of booleans:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

``:code

In this way we made parents searchable but children for each parent not searchable (there should not be that many to need the search widget).

-------

-------

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

``:code

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

``:code

#### ``SQLFORM`` and ``insert``/``update``/``delete``

#### ``SQLFORM`` and ``insert``/``update``/``delete``

- ``fields`` is a list of fields to be fetched from the database. It is also used to determine which fields to be shown in the grid view.

- ``field_id`` must be the field of the table to be used as ID, for example ``db.mytable.id``.

- ``left`` is an optional left join expressions used to build ``...select(left=...)``.

- ``headers`` is a dictionary that maps 'tablename.fieldname' into the corresponding header label, e.g. ``{'auth_user.email' : 'Email Address'}``

- ``orderby`` is used as default ordering for the rows.

- ``groupby`` is used to group the set. Use the same syntax as you were passing in a simple ``select(groupby=...)``.

- ``searchable``, ``sortable``, ``deletable``, ``editable``, ``details``, ``create`` determine whether one can search, sort, delete, edit, view details, and create new records respectively.

- ``selectable`` can be used to call a custom function on multiple records (a checkbox will be inserted for every row) e.g.

``

selectable = lambda ids : redirect(URL('default', 'mapping_multiple', vars=dict(id=ids)))

``:code

- ``paginate`` sets the max number of rows per page.

- ``csv`` if set to true allows to download the grid in various format (more on that later).

- ``links`` is used to display new columns which can be links to other pages. The ``links`` argument must be a list of ``dict(header='name',body=lambda row: A(...))`` where ``header`` is the header of the new column and ``body`` is a function that takes a row and returns a value. In the example, the value is a ``A(...)`` helper.

- ``links_in_grid`` if set to False, links will only be displayed in the "details" and "edit" page (so, not on the main grid)

- ``upload`` same as SQLFORM's one. web2py uses the action at that URL to download the file

- ``maxtextlength`` sets the maximum length of text to be displayed for each field value, in the grid view. This value can be overwritten for each field using ``maxtextlengths``, a dictionary of 'tablename.fieldname':length e.g. ``{'auth_user.email' : 50}``

- ``fields`` is a list of fields to be fetched from the database. It is also used to determine which fields to be shown in the grid view.

- ``field_id`` must be the field of the table to be used as ID, for example ``db.mytable.id``.

- ``left`` is an optional left join expressions used to build ``...select(left=...)``.

- ``headers`` is a dictionary that maps 'tablename.fieldname' into the corresponding header label, e.g. ``{'auth_user.email' : 'Email Address'}``

- ``orderby`` is used as default ordering for the rows.

- ``groupby`` is used to group the set. Use the same syntax as you were passing in a simple ``select(groupby=...)``.

- ``searchable``, ``sortable``, ``deletable``, ``editable``, ``details``, ``create`` determine whether one can search, sort, delete, edit, view details, and create new records respectively.

- ``selectable`` can be used to call a custom function on multiple records (a checkbox will be inserted for every row) e.g.

``

selectable = lambda ids : redirect(URL('default', 'mapping_multiple', vars=dict(id=ids)))

``:code

- ``paginate`` sets the max number of rows per page.

- ``csv`` if set to true allows to download the grid in various format (more on that later).

- ``links`` is used to display new columns which can be links to other pages. The ``links`` argument must be a list of ``dict(header='name',body=lambda row: A(...))`` where ``header`` is the header of the new column and ``body`` is a function that takes a row and returns a value. In the example, the value is a ``A(...)`` helper.

- ``links_in_grid`` if set to False, links will only be displayed in the "details" and "edit" page (so, not on the main grid)

- ``upload`` same as SQLFORM's one. web2py uses the action at that URL to download the file

- ``maxtextlength`` sets the maximum length of text to be displayed for each field value, in the grid view. This value can be overwritten for each field using ``maxtextlengths``, a dictionary of 'tablename.fieldname':length e.g. ``{'auth_user.email' : 50}``

If you pass a URL to the ``SQLFORM`` constructor via the upload argument, web2py uses the action at that URL to download the file. Consider the following actions:

``

def display_form():

form = SQLFORM(db.person, record, deletable=True,

upload=URL('download'))

if form.process().accepted:

response.flash = 'form accepted'

elif form.errors:

response.flash = 'form has errors'

return dict(form=form)

def download():

return response.download(request, db)

``:code

Now, insert a new record at the URL:

``

http://127.0.0.1:8000/test/default/display_form

``:code

Upload an image, submit the form, and then edit the newly created record

by visiting:

``

Here is the page:

[[image @///image/en6400.png center 300px]]

There is a link called "dog.owner". The name of this link can be changed via the ``labels`` argument of the ``SQLFORM``, for example:

``

labels = {'dog.owner':"This person's dogs"}

``:code

If you click on the link you get directed to:

``

/test/default/list_records/dog?query=db.dog.owner%3D%3D5

``:code

"list_records" is the specified action, with ``request.args(0)`` set to the name of the referencing table and ``request.vars.query`` set to the SQL query string.

The query string in the URL

contains the value "dog.owner=5" appropriately url-encoded

(web2py decodes this automatically when the URL is parsed).

You can easily implement a very general "list_records" action as follows:

``

def list_records():

REGEX = re.compile('^(\w+)\.(\w+)\.(\w+)\=\=(\d+)$')

match = REGEX.match(request.vars.query)

if not match:

redirect(URL('error'))

table, field, id = match.group(2), match.group(3), match.group(4)

records = db(db[table][field]==id).select()

return dict(records=records)

``:code

If you pass a URL to the ``SQLFORM`` constructor via the upload argument, web2py uses the action at that URL to download the file. Consider the following actions:

``

def display_form():

form = SQLFORM(db.person, record, deletable=True,

upload=URL('download'))

if form.process().accepted:

response.flash = 'form accepted'

elif form.errors:

response.flash = 'form has errors'

return dict(form=form)

def download():

return response.download(request, db)

``:code

Now, insert a new record at the URL:

``

http://127.0.0.1:8000/test/default/display_form

``:code

Upload an image, submit the form, and then edit the newly created record

by visiting:

``

Here is the page:

[[image @///image/en6400.png center 300px]]

There is a link called "dog.owner". The name of this link can be changed via the ``labels`` argument of the ``SQLFORM``, for example:

``

labels = {'dog.owner':"This person's dogs"}

``:code

If you click on the link you get directed to:

``

/test/default/list_records/dog?query=db.dog.owner%3D%3D5

``:code

"list_records" is the specified action, with ``request.args(0)`` set to the name of the referencing table and ``request.vars.query`` set to the SQL query string.

The query string in the URL

contains the value "dog.owner=5" appropriately url-encoded

(web2py decodes this automatically when the URL is parsed).

You can easily implement a very general "list_records" action as follows:

``

def list_records():

REGEX = re.compile('^(\w+)\.(\w+)\.(\w+)\=\=(\d+)$')

match = REGEX.match(request.vars.query)

if not match:

redirect(URL('error'))

table, field, id = match.group(2), match.group(3), match.group(4)

records = db(db[table][field]==id).select()

return dict(records=records)

``:code

You can add more than one button to form. The arguments of ``add_button`` are the value of the button (its text) and the url where to redirect to.

#### More about manipulation of FORMs

As discussed in the Views chapter, a FORM is an HTML helper. Helpers can be manipulated as Python lists and as dictionaries, which enables run-time creation and modification.

### ``SQLFORM``

We now move to the next level by providing the application with a model file:

``

db = DAL('sqlite://storage.sqlite')

db.define_table('person', Field('name', requires=IS_NOT_EMPTY()))

``:code

Modify the controller as follows:

``

def display_form():

form = SQLFORM(db.person)

if form.process().accepted:

response.flash = 'form accepted'

elif form.errors:

response.flash = 'form has errors'

SQLFORM(table, record = None,

- ``id_label`` sets the label of the record "id"

- The "id" of the record is not shown if ``showid`` is set to ``False``.

- ``fields`` is an optional list of field names that you want to display. If a list is provided, only fields in the list are displayed. For example:

``

fields = ['name']

``:code

- ``labels`` is a dictionary of field labels. The dictionary key is a field name and the corresponding value is what gets displayed as its label. If a label is not provided, web2py derives the label from the field name (it capitalizes the field name and replaces underscores with spaces). For example:

``

labels = {'name':'Your Full Name:'}

``:code

- ``col3`` is a dictionary of values for the third column. For example:

``

col3 = {'name':A('what is this?',

_href='http://www.google.com/search?q=define:name')}

``:code

- ``linkto`` and ``upload`` are optional URLs to user-defined controllers that allow the form to deal with reference fields. This is discussed in more detail later in the section.

- ``readonly``. If set to True, displays the form as readonly

- ``comments``. If set to False, does not display the col3 comments

- ``ignore_rw``. Normally, for a create/update form, only fields marked as writable=True are shown, and for readonly forms, only fields marked as readable=True are shown. Setting ``ignore_rw=True`` causes those constraints to be ignored, and all fields are displayed. This is mostly used in the appadmin interface to display all fields for each table, overriding what the model indicates.

- ``formstyle``:inxx ``formstyle`` determines the style to be used when serializing the form in html. It can be "table3cols" (default), "table2cols" (one row for label and comment, and one row for input), "ul" (makes an unordered list of input fields), "divs" (represents the form using css friendly divs, for arbitrary customization). ``formstyle`` can also be a function that takes (record_id, field_label, field_widget, field_comment) as attributes and returns a TR() object.

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

You can add more than one button to form. The arguments of ``add_button`` are the value of the button (its text) and the url where to redirect to.

#### More about manipulation of FORMs

As discussed in the Views chapter, a FORM is an HTML helper. Helpers can be manipulated as Python lists and as dictionaries, which enables run-time creation and modification.

### ``SQLFORM``

We now move to the next level by providing the application with a model file:

``

db = DAL('sqlite://storage.sqlite')

db.define_table('person', Field('name', requires=IS_NOT_EMPTY()))

``:code

Modify the controller as follows:

``

def display_form():

form = SQLFORM(db.person)

if form.process().accepted:

response.flash = 'form accepted'

elif form.errors:

response.flash = 'form has errors'

SQLFORM(table, record = None,

- ``id_label`` sets the label of the record "id"

- The "id" of the record is not shown if ``showid`` is set to ``False``.

- ``fields`` is an optional list of field names that you want to display. If a list is provided, only fields in the list are displayed. For example:

``

fields = ['name']

``:code

- ``labels`` is a dictionary of field labels. The dictionary key is a field name and the corresponding value is what gets displayed as its label. If a label is not provided, web2py derives the label from the field name (it capitalizes the field name and replaces underscores with spaces). For example:

``

labels = {'name':'Your Full Name:'}

``:code

- ``col3`` is a dictionary of values for the third column. For example:

``

col3 = {'name':A('what is this?',

_href='http://www.google.com/search?q=define:name')}

``:code

- ``linkto`` and ``upload`` are optional URLs to user-defined controllers that allow the form to deal with reference fields. This is discussed in more detail later in the section.

- ``readonly``. If set to True, displays the form as readonly

- ``comments``. If set to False, does not display the col3 comments

- ``ignore_rw``. Normally, for a create/update form, only fields marked as writable=True are shown, and for readonly forms, only fields marked as readable=True are shown. Setting ``ignore_rw=True`` causes those constraints to be ignored, and all fields are displayed. This is mostly used in the appadmin interface to display all fields for each table, overriding what the model indicates.

- ``formstyle``:inxx ``formstyle`` determines the style to be used when serializing the form in html. It can be "table3cols" (default), "table2cols" (one row for label and comment, and one row for input), "ul" (makes an unordered list of input fields), "divs" (represents the form using css friendly divs, for arbitrary customization). ``formstyle`` can also be a function that takes (record_id, field_label, field_widget, field_comment) as attributes and returns a TR() object.

- ``separator``:inxx ``separator`` sets the string that separates form labels from form input fields.

- Optional ``attributes`` are arguments starting with underscore that you want to pass to the ``FORM`` tag that renders the ``SQLFORM`` object. Examples are:

``

_action = '.'

_method = 'POST'

``:code

and insert the form in the associated "default/display_manual_form.html" view:

``

{{extend 'layout.html'}}

<ul>

<li>Your name is <input name="name" /></li>

</ul>

<input type="submit" />

<input type="hidden" name="_formname" value="test" />

</form>

``:code

and insert the form in the associated "default/display_manual_form.html" view:

``

{{extend 'layout.html'}}

<ul>

<li>Your name is <input name="name" /></li>

</ul>

<input type="submit" />

<input type="hidden" name="_formname" value="test" />

</form>

``:code

##### ``Showing virtual fields in SQLFORM.grid and smartgrid``

``grid = SQLFORM.grid(db.t1, ..., fields = [t1.field1, t1.field2,...],

links = [dict(header='Virtual Field',body=lamba row:row.vfield),...] )

``:code

##### ``Showing virtual fields in SQLFORM.grid and smartgrid``

``grid = SQLFORM.grid(db.t1, ..., fields = [t1.field1, t1.field2,...],

links = [dict(header='Virtual Field',body=lamba row:row.vfield),...] )

``:code

links = [dict(header='Virtual Field',body=lamba row:row.vfield),...] )

``:code

links = [dict(header='Virtual Field',body=lamba row:row.vfield),...] )

``:code

grid = SQLFORM.grid(db.t1, ..., fields = [t1.field1, t1.field2,...],

links = [dict(header='Virtual Field',body=lamba row:row.vfield),...] )

``:code

grid = SQLFORM.grid(db.t1, ..., fields = [t1.field1, t1.field2,...],

links = [dict(header='Virtual Field',body=lamba row:row.vfield),...] )

``:code

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid gadget will provide access to records matching the query.

Before we dive into the long list of arguments of the grid gadget we need to understand how it works. The gadget looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the gadget links the same function (``manage_users`` in the above case) but passes different ``request.args``. By default all the URL generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

ui = dict(

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid gadget will provide access to records matching the query.

Before we dive into the long list of arguments of the grid gadget we need to understand how it works. The gadget looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the gadget links the same function (``manage_users`` in the above case) but passes different ``request.args``. By default all the URL generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

ui = dict(

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid gadget will provide access to records matching the query.

Before we dive into the long list of arguments of the grid gadget we need to understand how it works. The gadget looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the gadget links the same function (``manage_users`` in the above case) but passes different ``request.args``. By default all the URL generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

ui = dict(

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid gadget will provide access to records matching the query.

Before we dive into the long list of arguments of the grid gadget we need to understand how it works. The gadget looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the gadget links the same function (``manage_users`` in the above case) but passes different ``request.args``. By default all the URL generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

ui = dict(

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

``

csv_with_hidden_cols=(ExporterCSV, 'CSV (hidden cols)'),

csv=(ExporterCSV, 'CSV'),

xml=(ExporterXML, 'XML'),

html=(ExporterHTML, 'HTML'),

tsv_with_hidden_cols=(ExporterTSV, 'TSV (Excel compatible, hidden cols)'),

tsv=(ExporterTSV, 'TSV (Excel compatible)'))

``:code

ExporterCSV, ExporterXML, ExporterHTML and ExporterTSV are all defined in gluon/sqlhtml.py. Take a look at those for creating your own exporter. If you pass a dict like ``dict(xml=False, html=False)`` you will disable the xml and html export formats.

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

``

requires=IS_IN_SET(['on'])

``:code

``

requires=IS_IN_SET(['on'])

``:code

You may also use a dictionary or a list of tuples to make the drop down list more descriptive:

``

#### Dictionary example:

requires = IS_IN_SET({'A':'Apple','B':'Banana','C':'Cherry'},zero=None)

#### List of tuples example:

requires = IS_IN_SET([('A','Apple'),('B','Banana'),('C','Cherry')])

``:code

You may also use a dictionary or a list of tuples to make the drop down list more descriptive:

``

#### Dictionary example:

requires = IS_IN_SET({'A':'Apple','B':'Banana','C':'Cherry'},zero=None)

#### List of tuples example:

requires = IS_IN_SET([('A','Apple'),('B','Banana'),('C','Cherry')])

``:code

Forms created with

``

form = SQLFORM(...,ignore_rw=True)

``:code

ignore the ``readable`` and ``writable`` attributes and always show all fields. Forms in ``appadmin`` ignore them by default.

Forms created with

``

form = SQLFORM(table,record_id,readonly=True)

``:code

always show all fields in readonly mode, and they cannot be accepted.

#### ``SQLFORM`` in HTML

Forms created with

``

form = SQLFORM(...,ignore_rw=True)

``:code

ignore the ``readable`` and ``writable`` attributes and always show all fields. Forms in ``appadmin`` ignore them by default.

Forms created with

``

form = SQLFORM(table,record_id,readonly=True)

``:code

always show all fields in readonly mode, and they cannot be accepted.

#### ``SQLFORM`` in HTML

The process function takes some extra argument that ``accepts`` does not take:

- ``message_onsuccess``

- ``onsuccess``: if equal to 'flash' (default) and the form is accepted it will flash the above `message_onsuccess``

- ``message_onfailure``

- ``onfailure``: if equal to 'flash' (default) and the form fails validation, it will flash the above `message_onfailure``

``onsuccess`` and ``onfailure`` can be functions like ``lambda form: do_something(form)``.

``

form.validate(...)

``:code

is a shortcut for

``

form.process(...,dbio=False).accepted

``:code

#### Hidden fields

When the above form object is serialized by ``{{=form}}``, and because of the previous call to the ``accepts`` method, it now looks like this:

``

<form enctype="multipart/form-data" action="" method="post">

your name:

<input name="name" />

<input type="submit" />

The ``SQLFORM`` constructor allows various customizations, such as displaying on

The class ``SQLFORM`` is defined in "gluon/sqlhtml.py". It can be easily extended by overriding its ``xml`` method, the method that serializes the objects, to change its output.

``fields``:inxx ``labels``:inxx

The signature for the ``SQLFORM`` constructor is the following:

``

SQLFORM(table, record = None,

deletable = False, linkto = None,

upload = None, fields = None, labels = None,

col3 = {}, submit_button = 'Submit',

delete_label = 'Check to delete:',

showid = True, readonly = False,

comments = True, keepopts = [],

ignore_rw = False, record_id = None,

formstyle = 'table3cols',

buttons = ['submit'], separator = ': ',

**attributes)

``:code

- The optional second argument turns the INSERT form into an UPDATE form for the specified record (see next subsection). ``showid``:inxx ``delete_label``:inxx ``id_label``:inxx ``submit_button``:inxx

- ``submit_button`` sets the value of the submit button.

- ``id_label`` sets the label of the record "id"

- The "id" of the record is not shown if ``showid`` is set to ``False``.

- ``fields`` is an optional list of field names that you want to display. If a list is provided, only fields in the list are displayed. For example:

``

fields = ['name']

``:code

- ``labels`` is a dictionary of field labels. The dictionary key is a field name and the corresponding value is what gets displayed as its label. If a label is not provided, web2py derives the label from the field name (it capitalizes the field name and replaces underscores with spaces). For example:

``

labels = {'name':'Your Full Name:'}

``:code

- ``col3`` is a dictionary of values for the third column. For example:

``

col3 = {'name':A('what is this?',

_href='http://www.google.com/search?q=define:name')}

``:code

- ``linkto`` and ``upload`` are optional URLs to user-defined controllers that allow the form to deal with reference fields. This is discussed in more detail later in the section.

- ``readonly``. If set to True, displays the form as readonly

- ``comments``. If set to False, does not display the col3 comments

- ``ignore_rw``. Normally, for a create/update form, only fields marked as writable=True are shown, and for readonly forms, only fields marked as readable=True are shown. Setting ``ignore_rw=True`` causes those constraints to be ignored, and all fields are displayed. This is mostly used in the appadmin interface to display all fields for each table, overriding what the model indicates.

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid gadget will provide access to records matching the query.

Before we dive into the long list of arguments of the grid gadget we need to understand how it works. The gadget looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the gadget links the same function (``manage_users`` in the above case) but passes different ``request.args``. By default all the URL generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

``:code

but we do not recommend it.

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

To make the default search grid work in more than one LOADed grid, please use a different ``formname`` for each one.

-----

The process function takes some extra argument that ``accepts`` does not take:

- ``message_onsuccess``

- ``onsuccess``: if equal to 'flash' (default) and the form is accepted it will flash the above `message_onsuccess``

- ``message_onfailure``

- ``onfailure``: if equal to 'flash' (default) and the form fails validation, it will flash the above `message_onfailure``

``onsuccess`` and ``onfailure`` can be functions like ``lambda form: do_something(form)``.

``

form.validate(...)

``:code

is a shortcut for

``

form.process(...,dbio=False).accepted

``:code

#### Hidden fields

When the above form object is serialized by ``{{=form}}``, and because of the previous call to the ``accepts`` method, it now looks like this:

``

<form enctype="multipart/form-data" action="" method="post">

your name:

<input name="name" />

<input type="submit" />

The ``SQLFORM`` constructor allows various customizations, such as displaying on

The class ``SQLFORM`` is defined in "gluon/sqlhtml.py". It can be easily extended by overriding its ``xml`` method, the method that serializes the objects, to change its output.

``fields``:inxx ``labels``:inxx

The signature for the ``SQLFORM`` constructor is the following:

``

SQLFORM(table, record = None,

deletable = False, linkto = None,

upload = None, fields = None, labels = None,

col3 = {}, submit_button = 'Submit',

delete_label = 'Check to delete:',

showid = True, readonly = False,

comments = True, keepopts = [],

ignore_rw = False, record_id = None,

formstyle = 'table3cols',

buttons = ['submit'], separator = ': ',

**attributes)

``:code

- The optional second argument turns the INSERT form into an UPDATE form for the specified record (see next subsection). ``showid``:inxx ``delete_label``:inxx ``id_label``:inxx ``submit_button``:inxx

- ``submit_button`` sets the value of the submit button.

- ``id_label`` sets the label of the record "id"

- The "id" of the record is not shown if ``showid`` is set to ``False``.

- ``fields`` is an optional list of field names that you want to display. If a list is provided, only fields in the list are displayed. For example:

``

fields = ['name']

``:code

- ``labels`` is a dictionary of field labels. The dictionary key is a field name and the corresponding value is what gets displayed as its label. If a label is not provided, web2py derives the label from the field name (it capitalizes the field name and replaces underscores with spaces). For example:

``

labels = {'name':'Your Full Name:'}

``:code

- ``col3`` is a dictionary of values for the third column. For example:

``

col3 = {'name':A('what is this?',

_href='http://www.google.com/search?q=define:name')}

``:code

- ``linkto`` and ``upload`` are optional URLs to user-defined controllers that allow the form to deal with reference fields. This is discussed in more detail later in the section.

- ``readonly``. If set to True, displays the form as readonly

- ``comments``. If set to False, does not display the col3 comments

- ``ignore_rw``. Normally, for a create/update form, only fields marked as writable=True are shown, and for readonly forms, only fields marked as readable=True are shown. Setting ``ignore_rw=True`` causes those constraints to be ignored, and all fields are displayed. This is mostly used in the appadmin interface to display all fields for each table, overriding what the model indicates.

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid gadget will provide access to records matching the query.

Before we dive into the long list of arguments of the grid gadget we need to understand how it works. The gadget looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the gadget links the same function (``manage_users`` in the above case) but passes different ``request.args``. By default all the URL generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

``:code

but we do not recommend it.

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

To make the default search grid work in more than one LOADed grid, please use a different ``formname`` for each one.

-----

Here is the "default/form_from_factory.html" view:

``

{{extend 'layout.html'}}

{{=form}}

``:code

You need to use an underscore instead of a space for field labels, or explicitly pass a dictionary of ``labels`` to ``form_factory``, as you would for a ``SQLFORM``. By default ``SQLFORM.factory`` generates the form using html "id" attributes generated as if the form was generated from a table called "no_table". To change this dummy table name, use the ``table_name`` attribute for the factory:

``

form = SQLFORM.factory(...,table_name='other_dummy_name')

``:code

Changing the ``table_name`` is necessary if you need to place two factory generated forms in the same table and want to avoid CSS conflicts.

#### One form for multiple tables

Here is the "default/form_from_factory.html" view:

``

{{extend 'layout.html'}}

{{=form}}

``:code

You need to use an underscore instead of a space for field labels, or explicitly pass a dictionary of ``labels`` to ``form_factory``, as you would for a ``SQLFORM``. By default ``SQLFORM.factory`` generates the form using html "id" attributes generated as if the form was generated from a table called "no_table". To change this dummy table name, use the ``table_name`` attribute for the factory:

``

form = SQLFORM.factory(...,table_name='other_dummy_name')

``:code

Changing the ``table_name`` is necessary if you need to place two factory generated forms in the same table and want to avoid CSS conflicts.

#### One form for multiple tables

``

form = SQLFORM(..)

form['_style']='border:1px solid black'

``:code

### ``FORM``

``form``:inxx ``accepts``:inxx ``formname``:inxx

Consider as an example a **test** application with the following "default.py" controller:

``

def display_form():

return dict()

``:code

and the associated "default/display_form.html" view:

``

{{extend 'layout.html'}}

<h2>Input form</h2>

<form enctype="multipart/form-data"

action="{{=URL()}}" method="post">

Your name:

<input name="name" />

<input type="submit" />

</form>

<h2>Submitted variables</h2>

{{=BEAUTIFY(request.vars)}}

``:code

``

form = SQLFORM(..)

form['_style']='border:1px solid black'

``:code

### ``FORM``

``form``:inxx ``accepts``:inxx ``formname``:inxx

Consider as an example a **test** application with the following "default.py" controller:

``

def display_form():

return dict()

``:code

and the associated "default/display_form.html" view:

``

{{extend 'layout.html'}}

<h2>Input form</h2>

<form enctype="multipart/form-data"

action="{{=URL()}}" method="post">

Your name:

<input name="name" />

<input type="submit" />

</form>

<h2>Submitted variables</h2>

{{=BEAUTIFY(request.vars)}}

``:code

``

``:code

and you need a form to allow the visitor to modify this dictionary.

This can be done with:

``

form = SQLFORM.dictform(config)

if form.process().accepted: config.update(form.vars)

``:code

The form will display one INPUT field for each item in the dictionary. It will use dictionary keys as INPUT names and labels and current values to infer types (string, int, double, date, datetime, boolean).

This works great but leave to you the logic of making the config dictionary persistent. For example you may want to store the ``config`` in a session.

``

form = SQLFORM.dictform(session.config)

if form.process().accepted:

session.config.update(form.vars)

``:code

### CRUD

``CRUD``:inxx ``crud.create``:inxx ``crud.update``:inxx ``crud.select``:inxx ``crud.search``:inxx ``crud.tables``:inxx ``crud.delete``:inxx

One of the recent additions to web2py is the Create/Read/Update/Delete (CRUD) API on top of SQLFORM.

CRUD creates an SQLFORM, but it simplifies the coding because it incorporates the creation of the form, the processing of the form, the notification, and the redirection, all in one single function.

The first thing to notice is that CRUD differs from the other web2py APIs we have used so far because it is not already exposed. It must be imported. It also must be linked to a specific database. For example:

``

from gluon.tools import Crud

crud = Crud(db)

``:code

The ``crud`` object defined above provides the following API:

For example, to have a "string" field represented by a textarea:

Field('comment', 'string', widget=SQLFORM.widgets.text.widget)

``:code

Widgets can also be assigned to fields ''a posteriori'':

``

db.mytable.myfield.widget = SQLFORM.widgets.string.widget

``

Sometimes widgets take additional arguments and one needs to specify their values. In this case one can use ``lambda``

``

db.mytable.myfield.widget = lambda field,value: \

SQLFORM.widgets.string.widget(field,value,_style='color:blue')

``

Widgets are helper factories and their first two arguments are always ``field`` and ``value``. The other arguments can include normal helper attributes such as ``_style``, ``_class``, etc. Some widgets also take special arguments. In particular ``SQLFORM.widgets.radio`` and ``SQLFORM.widgets.checkboxes`` take a ``style`` argument (not to be confused with ``_style``) which can be set to "table", "ul", or "divs" in order to match the ``formstyle`` of the containing form.

You can create new widgets or extend existing widgets.

``

def my_string_widget(field, value):

return INPUT(_name=field.name,

_id="%s_%s" % (field._tablename, field.name),

_class=field.type,

_value=value,

requires=field.requires)

``

``:code

and you need a form to allow the visitor to modify this dictionary.

This can be done with:

``

form = SQLFORM.dictform(config)

if form.process().accepted: config.update(form.vars)

``:code

The form will display one INPUT field for each item in the dictionary. It will use dictionary keys as INPUT names and labels and current values to infer types (string, int, double, date, datetime, boolean).

This works great but leave to you the logic of making the config dictionary persistent. For example you may want to store the ``config`` in a session.

``

form = SQLFORM.dictform(session.config)

if form.process().accepted:

session.config.update(form.vars)

``:code

### CRUD

``CRUD``:inxx ``crud.create``:inxx ``crud.update``:inxx ``crud.select``:inxx ``crud.search``:inxx ``crud.tables``:inxx ``crud.delete``:inxx

One of the recent additions to web2py is the Create/Read/Update/Delete (CRUD) API on top of SQLFORM.

CRUD creates an SQLFORM, but it simplifies the coding because it incorporates the creation of the form, the processing of the form, the notification, and the redirection, all in one single function.

The first thing to notice is that CRUD differs from the other web2py APIs we have used so far because it is not already exposed. It must be imported. It also must be linked to a specific database. For example:

``

from gluon.tools import Crud

crud = Crud(db)

``:code

The ``crud`` object defined above provides the following API:

For example, to have a "string" field represented by a textarea:

Field('comment', 'string', widget=SQLFORM.widgets.text.widget)

``:code

Widgets can also be assigned to fields ''a posteriori'':

``

db.mytable.myfield.widget = SQLFORM.widgets.string.widget

``

Sometimes widgets take additional arguments and one needs to specify their values. In this case one can use ``lambda``

``

db.mytable.myfield.widget = lambda field,value: \

SQLFORM.widgets.string.widget(field,value,_style='color:blue')

``

Widgets are helper factories and their first two arguments are always ``field`` and ``value``. The other arguments can include normal helper attributes such as ``_style``, ``_class``, etc. Some widgets also take special arguments. In particular ``SQLFORM.widgets.radio`` and ``SQLFORM.widgets.checkboxes`` take a ``style`` argument (not to be confused with ``_style``) which can be set to "table", "ul", or "divs" in order to match the ``formstyle`` of the containing form.

You can create new widgets or extend existing widgets.

``

def my_string_widget(field, value):

return INPUT(_name=field.name,

_id="%s_%s" % (field._tablename, field.name),

_class=field.type,

_value=value,

requires=field.requires)

### ``SQLFORM``

### ``SQLFORM``

The ``SQLFORM``, upon deleting a record, does not delete the physical uploaded file(s) referenced by the record. The reason is that web2py does not know whether the same file is used/linked by other tables or used for other purpose. If you know it is safe to delete the actual file when the corresponding record is deleted, you can do the following:

``

db.define_table('image',

Field('name', requires=IS_NOT_EMPTY()),

``:code

The ``autodelete`` attribute is ``False`` by default. When set to ``True`` is makes sure the file is deleted when the record is deleted.

#### Links to referencing records

Now consider the case of two tables linked by a reference field. For example:

``

db.define_table('person',

Field('name', requires=IS_NOT_EMPTY()))

db.define_table('dog',

Field('owner', 'reference person'),

Field('name', requires=IS_NOT_EMPTY()))

db.dog.owner.requires = IS_IN_DB(db,db.person.id,'%(name)s')

``:code

A person has dogs, and each dog belongs to an owner, which is a person. The dog owner is required to reference a valid ``db.person.id`` by ``'%(name)s'``.

Let's use the **appadmin** interface for this application to add a

few persons and their dogs.

In the case of a table including an "upload"-type field ("fieldname"), both ``pr

The name assigned by web2py to the uploaded file can be found in:

``

form.vars.fieldname

``:code

### Other types of Forms

#### ``SQLFORM.factory``

There are cases when you want to generate forms ''as if'' you had a database table but you do not want the database table. You simply want to take advantage of the ``SQLFORM`` capability to generate a nice looking CSS-friendly form and perhaps perform file upload and renaming.

This can be done via a ``form_factory``. Here is an example where you generate the form, perform validation, upload a file and store everything in the ``session`` :

``

def form_from_factory():

form = SQLFORM.factory(

Field('your_name', requires=IS_NOT_EMPTY()),

Field('your_image', 'upload'))

if form.process().accepted:

response.flash = 'form accepted'

session.your_name = form.vars.your_name

elif form.errors:

response.flash = 'form has errors'

return dict(form=form)

``:code

Here is the "default/form_from_factory.html" view:

``

{{extend 'layout.html'}}

{{=form}}

``:code

You need to use an underscore instead of a space for field labels, or explicitly pass a dictionary of ``labels`` to ``form_factory``, as you would for a ``SQLFORM``. By default ``SQLFORM.factory`` generates the form using html "id" attributes generated as if the form was generated from a table called "no_table". To change this dummy table name, use the ``table_name`` attribute for the factory:

``

form = SQLFORM.factory(...,table_name='other_dummy_name')

``:code

Changing the ``table_name`` is necessary if you need to place two factory generated forms in the same table and want to avoid CSS conflicts.

#### One form for multiple tables

db.define_table('mytable',

``:code

There is nothing special about these fields and you may give them any name you like. They are filled before the record is archived and are archived with each copy of the record. The archive table name and/or reference field name can be changed like this:

``

db.define_table('myhistory',

Field('parent_record', 'reference mytable'),

db.mytable)

## ...

form = SQLFORM(db.mytable,myrecord)

form.process(onsuccess = lambda form:auth.archive(form,

archive_table=db.myhistory,

current_record='parent_record'))

``:code

### Custom forms

If a form is created with SQLFORM, SQLFORM.factory or CRUD, there are multiple ways it can be embedded in a view allowing multiple degrees of customization. Consider for example the following model:

``

db.define_table('image',

``:code

and upload action

``

def upload_image():

return dict(form=SQLFORM(db.image).process())

``:code

The simplest way to embed the form in the view for ``upload_image`` is

``

{{=form}}

``:code

This results in a standard table layout. If you wish to use a different layout, you can break the form into components

``

{{=form.custom.begin}}

Image name: <div>{{=form.custom.widget.name}}</div>

Click here to upload: {{=form.custom.submit}}

{{=form.custom.end}}

``:code

where ``form.custom.widget[fieldname]`` gets serialized into the proper widget for the field. If the form is submitted and it contains errors, they are appended below the widgets, as usual.

The above sample form is show in the image below.

[[image @///image/en6500.png center 300px]]

A similar result could have been obtained without using a custom form:

``

SQLFORM(...,formstyle='table2cols')

``:code

or in case of CRUD forms with the following parameter:

``

crud.settings.formstyle='table2cols'

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

``:code

all children:

``

SQLFORM.grid(db.child)

``:code

and all parents and children in one table:

``

SQLFORM.grid(db.parent,left=db.child.on(db.child.parent==db.parent.id))

``:code

With SQLFORM.smartgrid you can put all the data in one gadget that spawns both tables:

``

def manage():

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

return locals()

``:code

The ``SQLFORM``, upon deleting a record, does not delete the physical uploaded file(s) referenced by the record. The reason is that web2py does not know whether the same file is used/linked by other tables or used for other purpose. If you know it is safe to delete the actual file when the corresponding record is deleted, you can do the following:

``

db.define_table('image',

Field('name', requires=IS_NOT_EMPTY()),

``:code

The ``autodelete`` attribute is ``False`` by default. When set to ``True`` is makes sure the file is deleted when the record is deleted.

#### Links to referencing records

Now consider the case of two tables linked by a reference field. For example:

``

db.define_table('person',

Field('name', requires=IS_NOT_EMPTY()))

db.define_table('dog',

Field('owner', 'reference person'),

Field('name', requires=IS_NOT_EMPTY()))

db.dog.owner.requires = IS_IN_DB(db,db.person.id,'%(name)s')

``:code

A person has dogs, and each dog belongs to an owner, which is a person. The dog owner is required to reference a valid ``db.person.id`` by ``'%(name)s'``.

Let's use the **appadmin** interface for this application to add a

few persons and their dogs.

In the case of a table including an "upload"-type field ("fieldname"), both ``pr

The name assigned by web2py to the uploaded file can be found in:

``

form.vars.fieldname

``:code

### Other types of Forms

#### ``SQLFORM.factory``

There are cases when you want to generate forms ''as if'' you had a database table but you do not want the database table. You simply want to take advantage of the ``SQLFORM`` capability to generate a nice looking CSS-friendly form and perhaps perform file upload and renaming.

This can be done via a ``form_factory``. Here is an example where you generate the form, perform validation, upload a file and store everything in the ``session`` :

``

def form_from_factory():

form = SQLFORM.factory(

Field('your_name', requires=IS_NOT_EMPTY()),

Field('your_image', 'upload'))

if form.process().accepted:

response.flash = 'form accepted'

session.your_name = form.vars.your_name

elif form.errors:

response.flash = 'form has errors'

return dict(form=form)

``:code

Here is the "default/form_from_factory.html" view:

``

{{extend 'layout.html'}}

{{=form}}

``:code

You need to use an underscore instead of a space for field labels, or explicitly pass a dictionary of ``labels`` to ``form_factory``, as you would for a ``SQLFORM``. By default ``SQLFORM.factory`` generates the form using html "id" attributes generated as if the form was generated from a table called "no_table". To change this dummy table name, use the ``table_name`` attribute for the factory:

``

form = SQLFORM.factory(...,table_name='other_dummy_name')

``:code

Changing the ``table_name`` is necessary if you need to place two factory generated forms in the same table and want to avoid CSS conflicts.

#### One form for multiple tables

db.define_table('mytable',

``:code

There is nothing special about these fields and you may give them any name you like. They are filled before the record is archived and are archived with each copy of the record. The archive table name and/or reference field name can be changed like this:

``

db.define_table('myhistory',

Field('parent_record', 'reference mytable'),

db.mytable)

## ...

form = SQLFORM(db.mytable,myrecord)

form.process(onsuccess = lambda form:auth.archive(form,

archive_table=db.myhistory,

current_record='parent_record'))

``:code

### Custom forms

If a form is created with SQLFORM, SQLFORM.factory or CRUD, there are multiple ways it can be embedded in a view allowing multiple degrees of customization. Consider for example the following model:

``

db.define_table('image',

``:code

and upload action

``

def upload_image():

return dict(form=SQLFORM(db.image).process())

``:code

The simplest way to embed the form in the view for ``upload_image`` is

``

{{=form}}

``:code

This results in a standard table layout. If you wish to use a different layout, you can break the form into components

``

{{=form.custom.begin}}

Image name: <div>{{=form.custom.widget.name}}</div>

Click here to upload: {{=form.custom.submit}}

{{=form.custom.end}}

``:code

where ``form.custom.widget[fieldname]`` gets serialized into the proper widget for the field. If the form is submitted and it contains errors, they are appended below the widgets, as usual.

The above sample form is show in the image below.

[[image @///image/en6500.png center 300px]]

A similar result could have been obtained without using a custom form:

``

SQLFORM(...,formstyle='table2cols')

``:code

or in case of CRUD forms with the following parameter:

``

crud.settings.formstyle='table2cols'

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

``:code

all children:

``

SQLFORM.grid(db.child)

``:code

and all parents and children in one table:

``

SQLFORM.grid(db.parent,left=db.child.on(db.child.parent==db.parent.id))

``:code

With SQLFORM.smartgrid you can put all the data in one gadget that spawns both tables:

``

def manage():

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

return locals()

``:code

##### ``IS_UPPER``

``IS_UPPER``:inxx

##### ``IS_UPPER``

``IS_UPPER``:inxx

``fields``:inxx ``labels``:inxx

The signature for the ``SQLFORM`` constructor is the following:

``

SQLFORM(table, record = None,

deletable = False, linkto = None,

upload = None, fields = None, labels = None,

col3 = {}, submit_button = 'Submit',

delete_label = 'Check to delete:',

showid = True, readonly = False,

comments = True, keepopts = [],

ignore_rw = False, record_id = None,

formstyle = 'table3cols',

**attributes)

``:code

- The optional second argument turns the INSERT form into an UPDATE form for the specified record (see next subsection). ``showid``:inxx ``delete_label``:inxx ``id_label``:inxx ``submit_button``:inxx

- If ``deletable`` is set to ``True``, the UPDATE form displays a "Check to delete" checkbox. The value of the label if this field is set via the ``delete_label`` argument.

- ``submit_button`` sets the value of the submit button.

- ``id_label`` sets the label of the record "id"

- The "id" of the record is not shown if ``showid`` is set to ``False``.

- ``fields`` is an optional list of field names that you want to display. If a list is provided, only fields in the list are displayed. For example:

``

fields = ['name']

``:code

- ``labels`` is a dictionary of field labels. The dictionary key is a field name and the corresponding value is what gets displayed as its label. If a label is not provided, web2py derives the label from the field name (it capitalizes the field name and replaces underscores with spaces). For example:

``

labels = {'name':'Your Full Name:'}

``:code

- ``col3`` is a dictionary of values for the third column. For example:

``

col3 = {'name':A('what is this?',

_href='http://www.google.com/search?q=define:name')}

crud.settings.delete_next = URL('index')

``:code

To specify the URL to be used for linking uploaded files:

``

crud.settings.download_url = URL('download')

``:code

To specify extra functions to be executed after standard validation procedures for ``crud.create`` forms:

``

crud.settings.create_onvalidation = StorageList()

``:code

``StorageList`` is the same as a ``Storage`` object, they are both defined in the file "gluon/storage.py", but it defaults to ``[]`` as opposed to ``None``. It allows the following syntax:

``

crud.settings.create_onvalidation.mytablename.append(lambda form:....)

``:code

``

crud.settings.update_onvalidation = StorageList()

``:code

To specify extra functions to be executed after completion of ``crud.create`` forms:

``

crud.settings.create_onaccept = StorageList()

``:code

To specify extra functions to be executed after completion of ``crud.update`` forms:

``

crud.settings.update_onaccept = StorageList()

``:code

To specify extra functions to be executed after completion of ``crud.update`` if record is deleted:

``

grid = SQLFORM.grid(db.auth_user,

``:code

The ``smartgrid`` is the only gadget in web2py that displays the table name and it need both the singular and the plural. For example one parent can have one "Child" or many "Children". Therefore a table object needs to know its own singular and plural names. web2py normally guesses them but you can set the explicitly:

``

db.define_table('child', ..., singular="Child", plural="Children")

``:code

or with: ``singular``:inxx ``plural``:inxx

``

db.define_table('child', ...)

db.child._singular = "Child"

db.child._plural = "Children"

``:code

They should also be internationalized using the ``T`` operator.

The plural and singular values are then used by ``smartgrid``

to provide correct names for headers and links.

``fields``:inxx ``labels``:inxx

The signature for the ``SQLFORM`` constructor is the following:

``

SQLFORM(table, record = None,

deletable = False, linkto = None,

upload = None, fields = None, labels = None,

col3 = {}, submit_button = 'Submit',

delete_label = 'Check to delete:',

showid = True, readonly = False,

comments = True, keepopts = [],

ignore_rw = False, record_id = None,

formstyle = 'table3cols',

**attributes)

``:code

- The optional second argument turns the INSERT form into an UPDATE form for the specified record (see next subsection). ``showid``:inxx ``delete_label``:inxx ``id_label``:inxx ``submit_button``:inxx

- If ``deletable`` is set to ``True``, the UPDATE form displays a "Check to delete" checkbox. The value of the label if this field is set via the ``delete_label`` argument.

- ``submit_button`` sets the value of the submit button.

- ``id_label`` sets the label of the record "id"

- The "id" of the record is not shown if ``showid`` is set to ``False``.

- ``fields`` is an optional list of field names that you want to display. If a list is provided, only fields in the list are displayed. For example:

``

fields = ['name']

``:code

- ``labels`` is a dictionary of field labels. The dictionary key is a field name and the corresponding value is what gets displayed as its label. If a label is not provided, web2py derives the label from the field name (it capitalizes the field name and replaces underscores with spaces). For example:

``

labels = {'name':'Your Full Name:'}

``:code

- ``col3`` is a dictionary of values for the third column. For example:

``

col3 = {'name':A('what is this?',

_href='http://www.google.com/search?q=define:name')}

crud.settings.delete_next = URL('index')

``:code

To specify the URL to be used for linking uploaded files:

``

crud.settings.download_url = URL('download')

``:code

To specify extra functions to be executed after standard validation procedures for ``crud.create`` forms:

``

crud.settings.create_onvalidation = StorageList()

``:code

``StorageList`` is the same as a ``Storage`` object, they are both defined in the file "gluon/storage.py", but it defaults to ``[]`` as opposed to ``None``. It allows the following syntax:

``

crud.settings.create_onvalidation.mytablename.append(lambda form:....)

``:code

``

crud.settings.update_onvalidation = StorageList()

``:code

To specify extra functions to be executed after completion of ``crud.create`` forms:

``

crud.settings.create_onaccept = StorageList()

``:code

To specify extra functions to be executed after completion of ``crud.update`` forms:

``

crud.settings.update_onaccept = StorageList()

``:code

To specify extra functions to be executed after completion of ``crud.update`` if record is deleted:

``

grid = SQLFORM.grid(db.auth_user,

``:code

The ``smartgrid`` is the only gadget in web2py that displays the table name and it need both the singular and the plural. For example one parent can have one "Child" or many "Children". Therefore a table object needs to know its own singular and plural names. web2py normally guesses them but you can set the explicitly:

``

db.define_table('child', ..., singular="Child", plural="Children")

``:code

or with: ``singular``:inxx ``plural``:inxx

``

db.define_table('child', ...)

db.child._singular = "Child"

db.child._plural = "Children"

``:code

They should also be internationalized using the ``T`` operator.

The plural and singular values are then used by ``smartgrid``

to provide correct names for headers and links.

The plural and singular values are then used by ``smartgrid``

to provide correct names for headers and links.

The plural and singular values are then used by ``smartgrid``

to provide correct names for headers and links.

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``showbuttontext`` allows to have buttons without text (there will effectively be only icons)

- ``_class`` is the class for the grid container.

- ``showbutton`` allows to turn off all buttons.

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

For example consider the following table structure:

``

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

def manage():

return locals()

``:code

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some caveats:

- The first argument is a table, not a query

- There is a extra argument ``constraints`` which is a dictionary of 'tablename':query which can be used to further restrict access to the records displayed in the 'tablename' grid.

- There is a extra argument ``linked_tables`` which is a list of tablenames of tables that should be accessible via the smartgrid.

- ``divider`` allows to specify a character to use in the breadcrumb navigator, ``breadcrumbs_class`` will apply the class to the breadcrumb element

- All the arguments but the table, ``args``, ``linked_tables`` and ``user_signatures`` can be dictionaries as explained below.

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

``:code

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

for a smartgrid we would pass a dictionary of booleans:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

-------

The grid and smartgrid gadgets are here to stay but they are marked experimental because the actual html layout of what they return and the exact set of parameters one can pass to them may be subject to change as new functionalities are added.

-------

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

The ``smartgrid`` is the only gadget in web2py that displays the table name and it need both the singular and the plural. For example one parent can have one "Child" or many "Children". Therefore a table object needs to know its own singular and plural names. web2py normally guesses them but you can set the explicitly:

``

db.define_table('child', ..., singular="Child", plural="Children")

``:code

or with: ``singular``:inxx ``plural``:inxx

``

db.define_table('child', ...)

db.child._singular = "Child"

db.child._plural = "Children"

``:code

They should also be internationalized using the ``T`` operator.

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``showbuttontext`` allows to have buttons without text (there will effectively be only icons)

- ``_class`` is the class for the grid container.

- ``showbutton`` allows to turn off all buttons.

- ``exportclasses`` takes a dictionary of tuples: by default it's defined as

- ``formargs`` is passed to all SQLFORM objects used by the grid, while ``createargs``,``editargs`` and ``viewargs`` are passed only to the specific create, edit and details SQLFORMs

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

- ``buttons_placement`` and ``links_placement`` both take a parameter ('right', 'left', 'both') that will affect where on the row the buttons (or the links) will be placed

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

For example consider the following table structure:

``

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

def manage():

return locals()

``:code

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some caveats:

- The first argument is a table, not a query

- There is a extra argument ``constraints`` which is a dictionary of 'tablename':query which can be used to further restrict access to the records displayed in the 'tablename' grid.

- There is a extra argument ``linked_tables`` which is a list of tablenames of tables that should be accessible via the smartgrid.

- ``divider`` allows to specify a character to use in the breadcrumb navigator, ``breadcrumbs_class`` will apply the class to the breadcrumb element

- All the arguments but the table, ``args``, ``linked_tables`` and ``user_signatures`` can be dictionaries as explained below.

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

``:code

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

for a smartgrid we would pass a dictionary of booleans:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

-------

The grid and smartgrid gadgets are here to stay but they are marked experimental because the actual html layout of what they return and the exact set of parameters one can pass to them may be subject to change as new functionalities are added.

-------

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

The ``smartgrid`` is the only gadget in web2py that displays the table name and it need both the singular and the plural. For example one parent can have one "Child" or many "Children". Therefore a table object needs to know its own singular and plural names. web2py normally guesses them but you can set the explicitly:

``

db.define_table('child', ..., singular="Child", plural="Children")

``:code

or with: ``singular``:inxx ``plural``:inxx

``

db.define_table('child', ...)

db.child._singular = "Child"

db.child._plural = "Children"

``:code

They should also be internationalized using the ``T`` operator.

and upload action

``

def upload_image():

``:code

The simplest way to embed the form in the view for ``upload_image`` is

``

{{=form}}

``:code

This results in a standard table layout. If you wish to use a different layout, you can break the form into components

``

{{=form.custom.begin}}

Image name: <div>{{=form.custom.widget.name}}</div>

Image file: <div>{{=form.custom.widget.file}}</div>

Click here to upload: {{=form.custom.submit}}

{{=form.custom.end}}

``:code

where ``form.custom.widget[fieldname]`` gets serialized into the proper widget for the field. If the form is submitted and it contains errors, they are appended below the widgets, as usual.

The above sample form is show in the image below.

[[image @///image/en6500.png center 300px]]

``

crud.settings.formstyle='table2cols'

``:code

If you do not wish to use the widgets serialized by web2py, you can replace them with HTML. There are some variables that will be useful for this:

- ``form.custom.label[fieldname]`` contains the label for the field.

- ``form.custom.comment[fieldname]`` contains the comment for the field.

- ``form.custom.dspval[fieldname]`` form-type and field-type dependent display representation of the field.

- ``form.custom.inpval[fieldname]`` form-type and field-type dependent values to be used in field code.

It is important to follow the conventions described below.

and upload action

``

def upload_image():

``:code

The simplest way to embed the form in the view for ``upload_image`` is

``

{{=form}}

``:code

This results in a standard table layout. If you wish to use a different layout, you can break the form into components

``

{{=form.custom.begin}}

Image name: <div>{{=form.custom.widget.name}}</div>

Image file: <div>{{=form.custom.widget.file}}</div>

Click here to upload: {{=form.custom.submit}}

{{=form.custom.end}}

``:code

where ``form.custom.widget[fieldname]`` gets serialized into the proper widget for the field. If the form is submitted and it contains errors, they are appended below the widgets, as usual.

The above sample form is show in the image below.

[[image @///image/en6500.png center 300px]]

``

crud.settings.formstyle='table2cols'

``:code

If you do not wish to use the widgets serialized by web2py, you can replace them with HTML. There are some variables that will be useful for this:

- ``form.custom.label[fieldname]`` contains the label for the field.

- ``form.custom.comment[fieldname]`` contains the comment for the field.

- ``form.custom.dspval[fieldname]`` form-type and field-type dependent display representation of the field.

- ``form.custom.inpval[fieldname]`` form-type and field-type dependent values to be used in field code.

It is important to follow the conventions described below.

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``:code

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid gadget will provide access to records matching the query.

Before we dive into the long list of arguments of the grid gadget we need to understand how it works. The gadget looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the gadget links the same function (``manage_users`` in the above case) but passes different ``request.args``. By default all the URL generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

but we do not recommend it.

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

-----

Because the function that contains the grid may itself manipulate the command line arguments, the grid needs to know which args should be handled by the grid and which not. For example here is an example of code that allows one to manage any table:

``

@auth.requires_login()

def manage():

table = request.args(0)

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.grid(db[table],args=request.args[:1])

return locals()

the ``args`` argument of the ``grid`` specifies which ``request.args`` should be passed along and ignored by the gadget. In our case ``request.args[:1]`` is the name of the table we want to manage and it is handled by the ``manage`` function itself, not by the gadget.

The complete signature for the grid is the following:

``

``:code

- ``fields`` is a list of fields to be fetched from the database. It is also used to determine which fields to be shown in the grid view.

- ``field_id`` must be the field of the table to be used as ID, for example ``db.mytable.id``.

- ``left`` is an optional left join expressions used to build ``...select(left=...)``.

- ``orderby`` is used as default ordering for the rows.

- ``paginate`` sets the max number of rows per page.

- ``links`` is used to display new columns which can be links to other pages. The ``links`` argument must be a list of ``dict(header='name',body=lambda row: A(...))`` where ``header`` is the header of the new column and ``body`` is a function that takes a row and returns a value. In the example, the value is a ``A(...)`` helper.

- ``onvalidation``, ``oncreate``, ``onupdate`` and ``ondelete`` are callback functions. All but ``ondelete`` take a form object as input.

- ``sorter_icons`` is a list of two strings (or helpers) that will be used to represent the up and down sorting options for each field.

- ``ui`` can be set equal to 'web2py' and will generate web2py friendly class names, can be set equal to ``jquery-ui`` and will generate jquery UI friendly class names, but it can also be its own set of class names for the various grid components:

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``_class`` is the class for the grid container.

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

For example consider the following table structure:

``

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

``:code

all children:

``

def manage():

``:code

If the ``linked_tables`` argument is not specified all referencing tables are automatically linked. Anyway, to avoid accidentally exposing data we recommend explicitly listing tables that should be linked.

The following code creates a very powerful management interface for all tables in the system:

``

@auth.requires_membership('managers'):

def manage():

table = request.args(0) or 'auth_user'

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.smartgrid(db[table],args=request.args[:1])

return locals()

``:code

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some caveats:

- The first argument is a table, not a query

- There is a extra argument ``constraints`` which is a dictionary of 'tablename':query which can be used to further restrict access to the records displayed in the 'tablename' grid.

- There is a extra argument ``linked_tables`` which is a list of tablenames of tables that should be accessible via the smartgrid.

- All the arguments but the table, ``args``, ``linked_tables`` and ``user_signatures`` can be dictionaries as explained below.

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

for a smartgrid we would pass a dictionary of booleans:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

In this way we made parents searchable but children for each parent not searchable (there should not be that many to need the search widget).

-------

The grid and smartgrid gadgets are here to stay but they are marked experimental because the actual html layout of what they return and the exact set of parameters one can pass to them may be subject to change as new functionalities are added.

-------

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid gadget will provide access to records matching the query.

Before we dive into the long list of arguments of the grid gadget we need to understand how it works. The gadget looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the gadget links the same function (``manage_users`` in the above case) but passes different ``request.args``. By default all the URL generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

``

def manage_users():

grid = SQLFORM.grid(db.auth_user,user_signature=False)

return locals()

but we do not recommend it.

-----

Because of the way grid works one can only have one grid per controller function, unless they are embedded as components via ``LOAD``.

-----

Because the function that contains the grid may itself manipulate the command line arguments, the grid needs to know which args should be handled by the grid and which not. For example here is an example of code that allows one to manage any table:

``

@auth.requires_login()

def manage():

table = request.args(0)

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.grid(db[table],args=request.args[:1])

return locals()

the ``args`` argument of the ``grid`` specifies which ``request.args`` should be passed along and ignored by the gadget. In our case ``request.args[:1]`` is the name of the table we want to manage and it is handled by the ``manage`` function itself, not by the gadget.

The complete signature for the grid is the following:

``

``:code

- ``fields`` is a list of fields to be fetched from the database. It is also used to determine which fields to be shown in the grid view.

- ``field_id`` must be the field of the table to be used as ID, for example ``db.mytable.id``.

- ``left`` is an optional left join expressions used to build ``...select(left=...)``.

- ``orderby`` is used as default ordering for the rows.

- ``paginate`` sets the max number of rows per page.

- ``links`` is used to display new columns which can be links to other pages. The ``links`` argument must be a list of ``dict(header='name',body=lambda row: A(...))`` where ``header`` is the header of the new column and ``body`` is a function that takes a row and returns a value. In the example, the value is a ``A(...)`` helper.

- ``onvalidation``, ``oncreate``, ``onupdate`` and ``ondelete`` are callback functions. All but ``ondelete`` take a form object as input.

- ``sorter_icons`` is a list of two strings (or helpers) that will be used to represent the up and down sorting options for each field.

- ``ui`` can be set equal to 'web2py' and will generate web2py friendly class names, can be set equal to ``jquery-ui`` and will generate jquery UI friendly class names, but it can also be its own set of class names for the various grid components:

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``_class`` is the class for the grid container.

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

------

``deletable``, ``editable`` and ``details`` are usually boolean values but they can be functions which take the row object and decide whether to display the corresponding button or not.

-----

A ``SQLFORM.smartgrid`` looks a lot like a ``grid``, in fact it contains a grid but it is designed to take as input not a query but only one table and to browse said table and selected referencing tables.

For example consider the following table structure:

``

db.define_table('parent',Field('name'))

db.define_table('child',Field('name'),Field('parent','reference parent'))

``:code

With SQLFORM.grid you can list all parents:

``

SQLFORM.grid(db.parent)

``:code

all children:

``

def manage():

``:code

If the ``linked_tables`` argument is not specified all referencing tables are automatically linked. Anyway, to avoid accidentally exposing data we recommend explicitly listing tables that should be linked.

The following code creates a very powerful management interface for all tables in the system:

``

@auth.requires_membership('managers'):

def manage():

table = request.args(0) or 'auth_user'

if not table in db.tables(): redirect(URL('error'))

grid = SQLFORM.smartgrid(db[table],args=request.args[:1])

return locals()

``:code

The ``smartgrid`` takes the same arguments as a ``grid`` and some more with some caveats:

- The first argument is a table, not a query

- There is a extra argument ``constraints`` which is a dictionary of 'tablename':query which can be used to further restrict access to the records displayed in the 'tablename' grid.

- There is a extra argument ``linked_tables`` which is a list of tablenames of tables that should be accessible via the smartgrid.

- All the arguments but the table, ``args``, ``linked_tables`` and ``user_signatures`` can be dictionaries as explained below.

Consider the previous grid:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'])

It allows one to access both a ``db.parent`` and a ``db.child``. Apart for navigation controls, for each one table, a smarttable is nothing but a grid. This means that, in this case, one smartgrid can create a grid for parent and one grid for child. We may want to pass different sets of parameters to these grids. For example different sets of ``searchable`` parameters.

While for a grid we would pass a boolean:

``

grid = SQLFORM.grid(db.parent,searchable=True)

for a smartgrid we would pass a dictionary of booleans:

``

grid = SQLFORM.smartgrid(db.parent,linked_tables=['child'],

searchable= dict(parent=True, child=False))

In this way we made parents searchable but children for each parent not searchable (there should not be that many to need the search widget).

-------

The grid and smartgrid gadgets are here to stay but they are marked experimental because the actual html layout of what they return and the exact set of parameters one can pass to them may be subject to change as new functionalities are added.

-------

``grid`` and ``smartgrid`` do not automatically enforce access control like crud does but you can integrate it with ``auth`` using explicit permission checking:

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_membership('managers'),

deletable = auth.has_membership('managers'))

or

``

grid = SQLFORM.grid(db.auth_user,

editable = auth.has_permission('edit','auth_user'),

deletable = auth.has_permission('delete','auth_user'))

Notice that ``crud.messages`` belongs to the class ``gluon.storage.Message`` which is similar to ``gluon.storage.Storage`` but it automatically translates its values, without need for the ``T`` operator.

Log messages are used if and only if CRUD is connected to Auth as discussed in Chapter 9. The events are logged in the Auth table "auth_events".

#### Methods

The behavior of CRUD methods can also be customized on a per call basis. Here are their signatures:

``

crud.tables()

crud.create(table, next, onvalidation, onaccept, log, message)

crud.read(table, record)

crud.update(table, record, next, onvalidation, onaccept, ondelete, log, message, deletable)

crud.delete(table, record_id, next, message)

crud.select(table, query, fields, orderby, limitby, headers, **attr)

crud.search(table, query, queries, query_labels, fields, field_labels, zero, showall, chkall)

``:code

- ``table`` is a DAL table or a tablename the method should act on.

- ``record`` and ``record_id`` are the id of the record the method should act on.

- ``next`` is the URL to redirect to after success. If the URL contains the substring "[id]" this will be replaced by the id of the record currently created/updated.

- ``onvalidation`` has the same function as SQLFORM(..., onvalidation)

- ``onaccept`` is a function to be called after the form submission is accepted and acted upon, but before redirection.

requires = IS_MATCH('^\d{1,3}(\.\d{1,3}){3}$',

Here is an example of usage to validate a US phone number:

``

requires = IS_MATCH('^1?((-)\d{3}-?|\(\d{3}\))\d{3}-?\d{4}$',

error_message='not a phone number')

``:code

For more information on Python regular expressions, refer to the official Python documentation.

``IS_MATCH`` takes an optional argument ``strict`` which defaults to ``False``. When set to ``True`` it only matches the beginning of the string:

``

>>> IS_MATCH('a')('ba')

('ba', <lazyT 'invalid expression'>) # no pass

>>> IS_MATCH('a',strict=False)('ab')

('a', None) # pass!

``

``IS_MATCH`` takes an other optional argument ``search`` which defaults to ``False``. When set to ``True``, it uses regex method ``search`` instead of method ``match`` to validate the string.

##### ``IS_NOT_EMPTY``

``IS_NOT_EMPTY``:inxx

Notice that ``crud.messages`` belongs to the class ``gluon.storage.Message`` which is similar to ``gluon.storage.Storage`` but it automatically translates its values, without need for the ``T`` operator.

Log messages are used if and only if CRUD is connected to Auth as discussed in Chapter 9. The events are logged in the Auth table "auth_events".

#### Methods

The behavior of CRUD methods can also be customized on a per call basis. Here are their signatures:

``

crud.tables()

crud.create(table, next, onvalidation, onaccept, log, message)

crud.read(table, record)

crud.update(table, record, next, onvalidation, onaccept, ondelete, log, message, deletable)

crud.delete(table, record_id, next, message)

crud.select(table, query, fields, orderby, limitby, headers, **attr)

crud.search(table, query, queries, query_labels, fields, field_labels, zero, showall, chkall)

``:code

- ``table`` is a DAL table or a tablename the method should act on.

- ``record`` and ``record_id`` are the id of the record the method should act on.

- ``next`` is the URL to redirect to after success. If the URL contains the substring "[id]" this will be replaced by the id of the record currently created/updated.

- ``onvalidation`` has the same function as SQLFORM(..., onvalidation)

- ``onaccept`` is a function to be called after the form submission is accepted and acted upon, but before redirection.

requires = IS_MATCH('^\d{1,3}(\.\d{1,3}){3}$',

Here is an example of usage to validate a US phone number:

``

requires = IS_MATCH('^1?((-)\d{3}-?|\(\d{3}\))\d{3}-?\d{4}$',

error_message='not a phone number')

``:code

For more information on Python regular expressions, refer to the official Python documentation.

``IS_MATCH`` takes an optional argument ``strict`` which defaults to ``False``. When set to ``True`` it only matches the beginning of the string:

``

>>> IS_MATCH('a')('ba')

('ba', <lazyT 'invalid expression'>) # no pass

>>> IS_MATCH('a',strict=False)('ab')

('a', None) # pass!

``

``IS_MATCH`` takes an other optional argument ``search`` which defaults to ``False``. When set to ``True``, it uses regex method ``search`` instead of method ``match`` to validate the string.

##### ``IS_NOT_EMPTY``

``IS_NOT_EMPTY``:inxx

If a key is specified, CRYPT uses the HMAC algorithm. The key may contain a prefix that determines the algorithm to use with HMAC, for example SHA512:

``

requires = CRYPT(key='sha512:thisisthekey')

``:code

This is the recommended syntax. The key must be a unique string associated with the database used. The key can never be changed. If you lose the key, the previously hashed values become useless.

The CRYPT validator hashes its input, and this makes it somewhat special. If you need to validate a password field before it is hashed, you can use CRYPT in a list of validators, but must make sure it is the last of the list, so that it is called last. For example:

``

requires = [IS_STRONG(),CRYPT(key='sha512:thisisthekey')]

``:code

``CRYPT`` also takes a ``min_length`` argument, which defaults to zero.

#### Database validators

If a key is specified, CRYPT uses the HMAC algorithm. The key may contain a prefix that determines the algorithm to use with HMAC, for example SHA512:

``

requires = CRYPT(key='sha512:thisisthekey')

``:code

This is the recommended syntax. The key must be a unique string associated with the database used. The key can never be changed. If you lose the key, the previously hashed values become useless.

The CRYPT validator hashes its input, and this makes it somewhat special. If you need to validate a password field before it is hashed, you can use CRYPT in a list of validators, but must make sure it is the last of the list, so that it is called last. For example:

``

requires = [IS_STRONG(),CRYPT(key='sha512:thisisthekey')]

``:code

``CRYPT`` also takes a ``min_length`` argument, which defaults to zero.

#### Database validators

If you click on the link you get directed to:

``

``:code

"list_records" is the specified action, with ``request.args(0)`` set to the name of the referencing table and ``request.vars.query`` set to the SQL query string.

The query string in the URL

contains the value "dog.owner=5" appropriately url-encoded

(web2py decodes this automatically when the URL is parsed).

You can easily implement a very general "list_records" action as follows:

``

def list_records():

return dict(records=records)

``:code

If you click on the link you get directed to:

``

``:code

"list_records" is the specified action, with ``request.args(0)`` set to the name of the referencing table and ``request.vars.query`` set to the SQL query string.

The query string in the URL

contains the value "dog.owner=5" appropriately url-encoded

(web2py decodes this automatically when the URL is parsed).

You can easily implement a very general "list_records" action as follows:

``

def list_records():

return dict(records=records)

``:code

``

SQLFORM.grid(db.parent)

``:code

all children:

``

SQLFORM.grid(db.child)

``:code

and all parents and children in one table:

``

``:code

``

SQLFORM.grid(db.parent)

``:code

all children:

``

SQLFORM.grid(db.child)

``:code

and all parents and children in one table:

``

``:code

Normally uploaded files are stored into "app/uploads" but you can specify an alternate location:

``

Field('image', 'upload', uploadfolder='...')

``

``

Field('image', 'upload', uploadseparate=True)

``

#### Storing the original filename

web2py automatically stores the original filename inside the new UUID filename and retrieves it when the file is downloaded. Upon download, the original filename is stored in the content-disposition header of the HTTP response. This is all done transparently without the need for programming.

Occasionally you may want to store the original filename in a database field. In this case, you need to modify the model and add a field to store it in:

``

db.define_table('person',

Field('name', requires=IS_NOT_EMPTY()),

Field('image_filename'),

Field('image', 'upload'))

``:code

then you need to modify the controller to handle it:

``

def display_form():

Notice the SQLFORM.factory (it makes ONE form using public fields from

both tables and inherits their validators too).

On form accepts this does two inserts, some data in one table and some

data in the other.

-------

This only works when the tables don't have field names in common.

-------

#### Confirmation Forms

``confirm``:inxx

Often you needs a form with to confirma choice. The form should be accepted if the choice is accepted and none otherwise. The form may have additional options that link other web pages. web2py provides a simple way to do this:

``

form = FORM.confirm('Are you sure?')

if form.accepted: do_what_needs_to_be_done()

``:code

``

form = FORM.confirm('Are you sure?',{'Back':URL('other_page')})

if form.accepted: do_what_needs_to_be_done()

``:code

#### Form to edit a dictionary

Imagine a system that stores configurations options in a dictionary,

``

``:code

and you need a form to allow the visitor to modify this dictionary.

This can be done with:

``

form = SQLFORM.dictform(config)

if form.process().accepted: config.update(form.vars)

``:code

This works great but leave to you the logic of making the config dictionary persistent. For example you may want to store the ``config`` in a session.

``

form = SQLFORM.dictform(session.config)

if form.process().accepted:

session.config.update(form.vars)

``:code

### CRUD

``CRUD``:inxx ``crud.create``:inxx ``crud.update``:inxx ``crud.select``:inxx ``crud.search``:inxx ``crud.tables``:inxx ``crud.delete``:inxx

One of the recent additions to web2py is the Create/Read/Update/Delete (CRUD) API on top of SQLFORM.

CRUD creates an SQLFORM, but it simplifies the coding because it incorporates the creation of the form, the processing of the form, the notification, and the redirection, all in one single function.

The first thing to notice is that CRUD differs from the other web2py APIs we have used so far because it is not already exposed. It must be imported. It also must be linked to a specific database. For example:

``

from gluon.tools import Crud

crud = Crud(db)

``:code

The ``crud`` object defined above provides the following API:

The ``IS_EMPTY_OR`` validator allows this:

``

requires = IS_EMPTY_OR(IS_DATE())

``:code

##### ``CLEANUP``

``CLEANUP``:inxx

This is a filter. It never fails. It just removes all characters whose decimal ASCII codes are not in the list [10, 13, 32-127].

``

requires = CLEANUP()

``:code

##### ``CRYPT``

``CRYPT``:inxx

This is also a filter. It performs a secure hash on the input and it is used to prevent passwords from being passed in the clear to the database.

``

requires = CRYPT()

``:code

``

requires = CRYPT(key='sha512:thisisthekey')

``:code

``

requires = [IS_STRONG(),CRYPT(key='sha512:thisisthekey')]

``:code

#### Database validators

##### ``IS_NOT_IN_DB``

``IS_NOT_IN_DB``:inxx

Consider the following example:

``

db.define_table('person', Field('name'))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

It requires that when you insert a new person, his/her name is not already in the database, ``db``, in the field ``person.name``. As with all other validators this requirement is enforced at the form processing level, not at the database level. This means that there is a small probability that, if two visitors try to concurrently insert records with the same person.name, this results in a race condition and both records are accepted. It is therefore safer to also inform the database that this field should have a unique value:

``

db.define_table('person', Field('name', unique=True))

db.person.name.requires = IS_NOT_IN_DB(db, 'person.name')

``:code

Now if a race condition occurs, the database raises an OperationalError and one of the two inserts is rejected.

db.product.category.widget = SQLFORM.widgets.autocomplete(

request, db.category.name, limitby=(0,10), min_length=2)

``:code

Where ``limitby`` instructs the widget to display no more than 10 suggestions at the time, and ``min_length`` instructs the widget to perform an Ajax callback to fetch suggestions only after the user has typed at least 2 characters in the search box.

The second case is more complex:

``

db.define_table('category',Field('name'))

db.define_table('product',Field('name'),Field('category'))

db.product.category.widget = SQLFORM.widgets.autocomplete(

request, db.category.name, id_field=db.category.id)

``:code

In this case the value of ``id_field`` tells the widget that even if the value to be autocompleted is a ``db.category.name``, the value to be stored is the corresponding ``db.category.id``. An optional parameter is ``orderby`` that instructs the widget on how to sort the suggestions (alphabetical by default).

This widget works via Ajax. Where is the Ajax callback? Some magic is going on in this widget. The callback is a method of the widget object itself. How is it exposed? In web2py any piece of code can generate a response by raising an HTTP exception. This widget exploits this possibility in the following way: the widget sends the Ajax call to the same URL that generated the widget in the first place and puts a special token in the request.vars. Should the widget get instantiated again, it finds the token and raises an HTTP exception that responds to the request. All of this is done under the hood and hidden to the developer.

### ``SQLFORM.grid`` and ``SQLFORM.smartgrid``

-------

-------

These are two high level gadgets that create complex CRUD controls. They provide pagination, the ability to browser, search, sort, create, update and delete records from a single gadgets.

The simplest of the two is ``SQLFORM.grid``. Here is an example of usage:

``

@auth.requires_login()

def manage_users():

grid = SQLFORM.grid(db.auth_user)

return locals()

``

which produces the following page:

[[image @///image/en6700.png center 480px]]

The first argument of ``SQLFORM.grid`` can be a table or a query. The grid gadget will provide access to records matching the query.

Before we dive into the long list of arguments of the grid gadget we need to understand how it works. The gadget looks at ``request.args`` in order to decide what to do (browse, search, create, update, delete, etc.). Each button created by the gadget links the same function (``manage_users`` in the above case) but passes different ``request.args``. By default all the URL generated by the grid are digitally signed and verified. This means one cannot perform certain actions (create, update, delete) without being logged-in. These restrictions can be relaxed:

ui = dict(widget='',

cornerall='',

cornertop='',

cornerbottom='',

button='button',

buttontext='buttontext button',

buttonadd='icon plus',

buttonback='icon leftarrow',

buttonexport='icon downarrow',

buttondelete='icon trash',

buttonedit='icon pen',

buttontable='icon rightarrow',

buttonview='icon magnifier')

``:code

- ``search_widget`` allows to override the default search widget and we refer the reader the source code in "gluon/sqlhtml.py" for details.

- ``showbutton`` allows to turn off all buttons.

- ``_class`` is the class for the grid container.

- ``formname``, ``ignore_rw`` and ``formstyle`` are passed to the SQLFORM objects used by the grid for create/update forms.

------

-----

Normally uploaded files are stored into "app/uploads" but you can specify an alternate location:

``

Field('image', 'upload', uploadfolder='...')

``

``

Field('image', 'upload', uploadseparate=True)

``

#### Storing the original filename

web2py automatically stores the original filename inside the new UUID filename and retrieves it when the file is downloaded. Upon download, the original filename is stored in the content-disposition header of the HTTP response. This is all done transparently without the need for programming.

Occasionally you may want to store the original filename in a database field. In this case, you need to modify the model and add a field to store it in:

``

db.define_table('person',

Field('name', requires=IS_NOT_EMPTY()),

Field('image_filename'),

Field('image', 'upload'))

``:code

then you need to modify the controller to handle it:

``

def display_form():

Notice the SQLFORM.factory (it makes ONE form using public fields from

both tables and inherits their validators too).

On form accepts this does two inserts, some data in one table and some