old web2py blog

SQLFORM keeps the values retrieved from the DB in custom objects that can be referenced by field name and used directly in custom forms without the need to re-access the DB.

The custom objects are:

• self.custom.label.fieldname : holds the label of the field.

• self.custom.dspval.fieldname : form-type and field-type dependent display representation of the field (value).

• self.custom.inpval.fieldname : form-type and field-type dependent values to be used in HTML form code.

• self.custom.comment.fieldname: holds any comments

• self.custom.widget.fieldname: form-type and field-type dependent widget for the field, eg the INPUT tags

• self.custom.linkto.fieldname: any found linkto references

• self.custom.deletable: holds the 'delete record' checkbox if the record is deletable

• self.custom.submit: holds the submit button is the the form is (partial) writeable

• self.custom.begin: holds the opening tag for the form, including type and action

• self.custom.end: holds the closing of the form. Currently the hidden fields (for the formkey etc) are not yet included, these must be retrieved by calling the self.hidden_fields() method.

All the custom objects are of type gluon.Storage, so they can be accesed with the dot notation used above.

In Create forms all dspvals are equal to   and all inpvals are empty.

In Read, Update and Delete forms, dspval holds the current record display values, while inpval holds the value or code used to display the dspvals.

The custom values are the same as those created by SQLFORM and have all the widget, represent and formatter modifications if applicable.

For example, a reference field's inpval holds the HTML code of the generated options, which can be used in a custom form as follows (note this is true for any field which has a requires with options (IS_IN_DB, IS_IN_SET) not only reference fields, as shown in example 2 at the bottom):

<FORM name="custom">
...
<DIV id="aref__label" style="...">{{=form.custom.label.areffld}}
<SELECT id="x" name="y" style="...">{{=form.custom.inpval.areffld}}</SELECT>
</DIV>
...
</FORM>

or if you prefer to re-use the form tags generated by SQLFORM

{{=form.custom.begin}}
...
<DIV id="aref__label" style="...">{{=form.custom.label.areffld}}
{{=form.custom.widget.areffld}}
</DIV>
...
{{=form.hidden_fields()}}
{{=form.custom.end}}

The long names can be shortened in the view by using something like:

{{dsp=form.custom.dspval}}
{{inp=form.custom.inpval}}
{{lbl=form.custom.label}}
{{wid=form.custom.widget}}

so the SELECT statement above now becomes:

<SELECT id="x" name="y" style="...">{{=inp.areffld}}</SELECT>

or just

{{=wid.areffld}}

An additional parameter of type list named keepopts controls which reference inpvals are kept, because only some might be needed and they can be quite large. The example above would have included it as follows:

form=SQLFORM(..., keepopts=['areffld'], ...)

To familiarize yourself with these new SQLFORM attributes you can display them in your view using the following code:

{{extend 'layout.html'}}
{{dsp=form.custom.dspval}}
{{inp=form.custom.inpval}}
{{lbl=form.custom.label}}
{{wid=form.custom.widget}}
<table>
<thead>
<th>field</th>
<th>label</th>
<th>widget</th>
<th>record</th>
<th>dspval</th>
<th>inpval</th>
</thead>
<tbody>{{for f in form.fields:}}
<tr>
<td>{{=f}}</td>
<td>{{=lbl[f]}}</td>
<td>{{v=wid[f]}}{{if hasattr(v,'xml'):}}{{=v.xml()}}{{else:}}{{=v}}{{pass}}</td>
<td>{{if form.record:}}{{=form.record[f]}}{{pass}}</td>
<td>{{v=dsp[f]}}{{if hasattr(v,'xml'):}}{{=v.xml()}}{{else:}}{{=v}}{{pass}}</td>
<td>{{v=inp[f]}}{{if hasattr(v,'xml'):}}{{=v.xml()}}{{else:}}{{=v}}{{pass}}</td>
</tr>{{pass}}
<tr><td colspan=5><b>full form:</b>{{=dir(form)}}</td></tr>
</tbody>
</table><hr>
{{=form}}

Example 2

model

db=SQLDB("sqlite://db.db")

db.define_table("country"
    ,SQLField("code","string",length=4,notnull=True)
    ,SQLField("name","string",length=32,notnull=True)
    )

db.country.code.requires=[IS_NOT_IN_DB(db,'country.code'),IS_NOT_EMPTY()]
db.country.name.requires=IS_NOT_EMPTY()

db.define_table("state"
    ,SQLField("country","string",length=4)#db.country.code)
    ,SQLField("code","string",length=2,notnull=True)
    ,SQLField("name","string",length=32,notnull=True)
    )

db.state.country.requires=IS_IN_DB(db,'country.code','%(name)s')
db.state.code.requires=[IS_NOT_EMPTY(),
    IS_NOT_IN_DB(db(db.state.country==request.vars.country),'state.code')]
db.state.name.requires=IS_NOT_EMPTY()

controller

def create_state():
    form=SQLFORM(db.state,keepopts=['country'])
    if form.accepts(request.vars,session):
        session.flash="state added!"
    elif form.errors:
        responst.flash="please correct and re-submit"
    return dict(form=form)

view

In your custom form you can now include something like:

<select name="country">{{=form.custom.inpval.country}}</select>

or shorter:

{{=form.custom.widget.country}}

and obtain all the options from the current database entries for country.code in the format:

<option value="CA">Canada</option>