Format representation in foreign table.

[rahulserver]

I have two tables:
db.define_table('Account_Master',Field('Account',requires=IS_NOT_EMPTY()),Field('Sewadari1'),Field('Mb1'),Field('Sewadari2'),Field('Mb2'),Field('City'),Field('District'),Field('State'),Field('Email'),Field('Remark'),format='%(Account)s %(State)s ')

db.define_table('Transaction_Master',Field('Account',db.Account_Master,requires=IS_IN_DB(db,'Account_Master.id', '%(Account)s %(State)s',zero=T('choose one'))),Field('Exam_Date','date'),Field('Entry_Date','date',default=request.now),Field('Form_1','upload'),Field('Form_1_Name'),Field('Schoolwise_Form','upload'),Field('Schoolwise_Form_Name'),format='%(Account)s %(Exam_Date)s')

When i try to represent the account field of Transaction_Master table in sqlform.factory as:
 form=SQLFORM.factory(Field('TID',requires=IS_IN_DB(db,db.Transaction_Master.id,'%(Account)s %(Exam_Date)s %(id)s')))

I am getting the id field of account in the sqlform.factory form. Is there a way so that i may get the account name which that id field refers to in the account master?
for example i am getting something like this in the selection scroll bar:

5 2012-05-08 1 where 5 is the Account_Master's id field. What if i want the Account at id 5 to be shown in the sqlform.factory instead?   Also i think it neccessary to mention that I am using sqlform.factory because i am seeking some parameters from users and i am storing those parameters in session object to generate some report based on those.

  

[Anthony]

db.define_table('Account_Master',Field('Account',requires=IS_NOT_EMPTY()),Field('Sewadari1'),Field('Mb1'),Field('Sewadari2'),Field('Mb2'),Field('City'),Field('District'),Field('State'),Field('Email'),Field('Remark'),format='%(Account)s %(State)s ')

db.define_table('Transaction_Master',Field('Account',db.Account_Master,requires=IS_IN_DB(db,'Account_Master.id', '%(Account)s %(State)s',zero=T('choose one'))),Field('Exam_Date','date'),Field('Entry_Date','date',default=request.now),Field('Form_1','upload'),Field('Form_1_Name'),Field('Schoolwise_Form','upload'),Field('Schoolwise_Form_Name'),format='%(Account)s %(Exam_Date)s')

When i try to represent the account field of Transaction_Master table in sqlform.factory as:
 form=SQLFORM.factory(Field('TID',requires=IS_IN_DB(db,db.Transaction_Master.id,'%(Account)s %(Exam_Date)s %(id)s')))

I am getting the id field of account in the sqlform.factory form. Is there a way so that i may get the account name which that id field refers to in the account master?

Your IS_IN_DB validator refers to '%(Account)s', but the Account field in the Transaction_Master table is a reference field and therefore just stores integers (i.e., record ID's), so that's what you're getting -- the format does not propagate from the db.Account_Master table to the db.Transaction_Master table. Instead, though, the format argument to IS_IN_DB can be a callable (e.g., a lambda) that takes a record and returns the representation you want. So, try:

IS_IN_DB(db, db.Transaction_Master.id,
    lambda r: '%s %s %s' % (db.Account_Master[r.Account].Account, r.Exam_Date, r.id))

Note, the format argument to define_table() can also be a callable.

Anthony

[rahulserver]

Thanks Anthony!

I did as u said but now i get the following error:

<type 'exceptions.AttributeError'> 'NoneType' object has no attribute 'Account'

Here is the line which was found erroraneous(in pink):

def viewreport():
    form=SQLFORM.factory(Field('TID',requires=IS_IN_DB(db, db.Transaction_Master.id,

    lambda r: '%s %s %s' % (db.Account_Master[r.Account].Account, r.Exam_Date, r.id))

))

With Regards,
rahulserver.

[Anthony]

On Sunday, May 20, 2012 11:49:41 AM UTC-4, rahulserver wrote:

Thanks Anthony!

I did as u said but now i get the following error:

<type 'exceptions.AttributeError'> 'NoneType' object has no attribute 'Account'

Here is the line which was found erroraneous(in pink):

def viewreport():
    form=SQLFORM.factory(Field('TID',requires=IS_IN_DB(db, db.Transaction_Master.id,
    lambda r: '%s %s %s' % (db.Account_Master[r.Account].Account, r.Exam_Date, r.id))


))

With Regards,
rahulserver.

Are there records in db.Account_Master and db.Transaction_Master, and do all values in db.Transaction_Master.Account reference existing records in db.Account_Master? If not, you could add a conditional:

IS_IN_DB(db, db.Transaction_Master.id,
    lambda r: '%s %s %s' % \

    (db.Account_Master[r.Account].Account if db.Account_Master[r.Account] else '',
    r.Exam_Date, r.id))

Anthony