Thanks for your response Anthony! I have obviously misunderstood a reference I read somewhere saying that reference fields were given special attributes and gave an example something like db.fieldname.name I assumed the trailing .name came from the referenced table attributes.
So I have modified the code ... def generate_inv_details(): session.inv_details = [] session.inv_total = 0 today = datetime.date.today() query = db( (db.task.person.belongs( session.company_persons)) & (db.task.start_time <= today ) & (db.task.charge_to == 'COMPANY' ) & (db.task.task_status == 'BILLABLE') & (db.person.id==db.task.person)) rows=db(query).select( db.task.person, db.person.name, db.task.title, db.task.start_time, db.task.duration, db.task.task_type, db.task.task_status, db.task.charge_to, db.task.charge, db.task.payment_status ) session.inv_details=rows for row in session.inv_details: session.inv_total += row.task.charge - added the join (I hope correctly) in the query - used full "tablename.fieldname" format to reference the person.name in the select statement - removed the newline slashes The relevant table excerpts... db.define_table('person', Field( 'name' , requires=IS_NOT_EMPTY() , comment="*"), Field( 'status' , requires=IS_IN_SET(PERSON_STATUS) , comment="*") , Field( 'type' , requires=IS_IN_SET(PERSON_TYPES) , comment="*") , Field( 'company' , 'reference company' , comment="*"), Field( 'role' , 'string' , comment='for CONTACTS not CLIENTS'), Field( 'address' , comment="Use ',' to seperate address lines"), Field( 'mobile_phone' , label='Mobile' , requires=IS_EMPTY_OR(is_phone), comment="Can use '-' to separate prefix for clarity" ), Field( 'office_phone' , label='Office' , requires=IS_EMPTY_OR(is_phone), comment="Can use '-' to separate prefix for clarity" ), Field( 'home_phone' , label='Home' , requires=IS_EMPTY_OR(is_phone), comment="Can use '-' to separate prefix for clarity" ), Field( 'email' , requires=IS_EMPTY_OR(IS_EMAIL()) ), Field( 'date_of_birth' , 'date' ), Field( 'referrer_ref' , 'string' , default=None), Field( 'notes' , 'text' ), Field( 'created_by' , db.auth_user , default=me , writable=False , readable=False ), Field( 'created_on' , 'datetime' , default=request.now , writable=False , readable=False ), format=' %(type) %(name)s Referrer Ref %(referrer_ref)s') db.define_table('task', Field( 'task_type' , requires=IS_IN_SET(TASK_TYPES) , comment="*" ), Field( 'task_status' , requires=IS_IN_SET(TASK_STATUS) , comment="*"), Field( 'start_time' , 'datetime' , default=request.now ,requires=IS_NOT_EMPTY() , comment="*"), Field( 'duration' , default='1Hr' ), Field( 'title' , default="Session ##" , requires=IS_NOT_EMPTY() , comment="*"), Field( 'person' , 'reference person' , comment="*"), Field( 'description' , 'text' ), Field( 'charge' , 'double' , default=0.0), Field( 'charge_to' , default='COMPANY' , requires=IS_IN_SET(CHARGE_TO) , comment="* [COMPANY for Invoicing, CONTACT for Receipting]"), Field( 'payment_status' , default='BILLABLE' , requires=IS_IN_SET(PAYMENT_STATUS) , comment="* [BILLABLE for Invoicing or Receipting]"), Field( 'invoice_number' , 'integer' , default=None , writable=False ), Field( 'invoice_date' , 'date' , default=None , writable=False ), Field( 'created_by' , db.auth_user , default=me , writable=False , readable=False ), Field( 'created_on' , 'datetime' , default=request.now , writable=False , readable=False ), format= '%(person)s %(start_time)s %(task_type)s %(task_status)s %(charge)s') with the modified code I am still getting the same error Traceback Traceback (most recent call last): File "/home/peter/web2py/gluon/restricted.py", line 227, in restricted exec ccode in environment File "/home/peter/web2py/applications/PAPAIM/controllers/default.py" <http://127.0.0.1:8000/admin/default/edit/PAPAIM/controllers/default.py>, line 566, in <module> File "/home/peter/web2py/gluon/globals.py", line 417, in <lambda> self._caller = lambda f: f() File "/home/peter/web2py/gluon/tools.py", line 4241, in f return action(*a, **b) File "/home/peter/web2py/applications/PAPAIM/controllers/default.py" <http://127.0.0.1:8000/admin/default/edit/PAPAIM/controllers/default.py>, line 347, in preview_invoice generate_inv_details() File "/home/peter/web2py/applications/PAPAIM/models/extras.py" <http://127.0.0.1:8000/admin/default/edit/PAPAIM/models/extras.py>, line 25, in generate_inv_details db.task.payment_status ) File "/usr/local/lib/python2.7/dist-packages/pydal/objects.py", line 2020, in select return adapter.select(self.query, fields, attributes) File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/sqlite.py", line 123, in select return super(SQLiteAdapter, self).select(query, fields, attributes) File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 1296, in select return self._select_aux(sql,fields,attributes) File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 1253, in _select_aux self.execute(sql) File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 1388, in execute return self.log_execute(*a, **b) File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 1382, in log_execute ret = self.get_cursor().execute(command, *a[1:], **b) OperationalError: near "<": syntax error -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.