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 <
[email protected]> 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
>
>
