Re: [web2py] Show in select list a field of another table

[Richard Vézina]

For this purpose I do a view at database level, but you can do it in web2py
and use it in your requires or represent.

Here is my solution :

## Model ##

db.define_table('company',
    Field('name', readable=False))

db.define_table('person',
    Field('person_id','id'),
    Field('first_name',readable=False),
    Field('last_name'),
    Field('company',db.company,label='Firma',readable=False),
    Field('role',label='Rolle',readable=False))

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

db.define_table('person_company',
    Field('person_id','id'),
    db.person,
    db.company,
    format='%(first_name)s %(last_name)s %(name)s')

db.define_table('document',
    Field('name'),
    Field('file','upload'),
    Field('filename','string',writable=False,label='Dateiname'),
    Field('person',db.person))

db.document.person.requires=IS_EMPTY_OR(IS_IN_DB(db,'person_company.id','%(first_name)s
%(last_name)s %(name)s' %))


## Controller ## In default or anywhere esle

def create_company():
    form = crud.create(db.company)
    return dict(form=form)

def create_person():
    form = crud.create(db.person)
    if form.accepts(request.vars, session):
        __trigger_person_company_insert(form.vars.id,request.vars) # Here we
trigger the insertion in db.person into a db.person_company that will be use
as an "SQL view"
        session.flash = 'form accepted'
        redirect(URL())
    return dict(form=form)

def __trigger_person_company_insert(person_id,vars):
    db.person_company.insert(person_id=person_id,
        first_name=vars.first_name,
        last_name=vars.last_name,
        company=vars.company,
        role=vars.role,
        name=db(db.company.id==vars.company).select(db.company.name
).first().name)
    db.commit()


Since the all the information from both table (person and company) are
available into person_company you can use it for requires and
representation...

How it sounds?

Richard

On Thu, Mar 10, 2011 at 6:20 AM, Norbert Klamann <
norbert.klam...@googlemail.com> wrote:

> Hello all,
> I want to shoe in the select list a field from another table and don't know
> how to do it.
>
> I want to show a select list for persons with their name and the name of
> the company to which they belong
>
> Consider the following model
> db.define_table('document',
>     Field('name'),
>     Field('file','upload'),
>     Field('filename','string',writable=False,label='Dateiname'),
>     Field('person',db.person),
>  )
>
> db.document.person.requires=IS_EMPTY_OR(IS_IN_DB(db,'person.id', '%(name)s
> %(company)s')) ###### here i can only show  the number !
>
> db.define_table('person',
>     Field('name',readable=False),
>     Field('company',db.company,label='Firma',readable=False),
>     Field('role',label='Rolle',readable=False),
> )
> db.define_table('company',
>     Field('name', readable=False),
> )
>
> Thanks a lot
>
> Norbert
>
>