web2py[:]


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

Single form for linked tables
Recipe by Massimo Di Pierro (mdipierro) on 2010-10-23 in Model

It often happens that you have two tables (for example 'client' and 'address') which are linked together by a reference and you want to create a single form that allows to insert info about one client and its default address. Here is how.

Model

db.define_table('client',
     Field('name'))
db.define_table('address',
    Field('client',db.client,writable=False,readable=False),
    Field('street'),Field('city'))

Controller

def register():
    form=SQLFORM.factory(db.client,db.address)
    if form.accepts(request.vars):
        id = db.client.insert(**db.client._filter_fields(form.vars))
        form.vars.client=id
        id = db.address.insert(**db.address._filter_fields(form.vars))
        response.flash='Thanks for filling the form'
    return dict(form=form)

Notice the SQLFORM.factory (it makes ONE form using public fields from both tables and inherits their validators too).

alt text

On form.accepts this does two inserts (some data in one table and some data in the other).

Massimo

reference table link form

Related slices

Comments (6)

  • Login to post



  • 0
    vid-ogris-10382 5 years ago

    SQLFORM. factory does not work with

    auth.settings.registration_requires_verification = True

    auth.settings.registration_requires_approval = False
    auth.settings.reset_password_requires_verification = True
     
    settigns. Also mail is not send. Any workarounds?

  • 0
    yuvall 8 years ago
    Its working nicely, but I found that if I try to use it to extend the out of the box auth, I lose all the build in functionality. e.g if I want to add multiple address as lined table form = SQLFORM.factory(db.auth_user, db.address) It looks as if I must follow this example, I will have to rewrite myself all the registration logic such as password_line2, email verification, etc. Is it true? or is there a solution to achieve the above while still keeping the out of box auth functionality?

  • 0
    gilsondev 8 years ago
    This code not work: def update(): record = db.client.id[1] form = SQLFORM.factory(db.client, db.address, record) This error: SyntaxError: define_table argument is not a Field or Table: SUBSTR(grupos.id,2,(3 - 2))

  • 0
    roaldosinga 9 years ago
    Brilliant! just what I needed

  • 0
    cfhowes 9 years ago
    Very useful. How can this be extended for updating/deleting? I keep getting errors when I try the following (as per SQLFORM docs): def update(): record = db.client.id[1] form = SQLFORM.factory(db.client, db.address, record)

  • 0
    murphy 9 years ago
    Great and useful slice. I was wondering how to do linked forms and now seems easier and fits very well with the Model-View-Controller design. Thank you.
Hosting graciously provided by:
Python Anywhere