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 [email protected].
For more options, visit https://groups.google.com/d/optout.
