It turns out the orderby is redundant.
Field('Account', 'reference CoA', requires=IS_IN_DB(db, 'CoA.id',
'%(Name)s')),
works the same. In the application smartgrid edit form the Account field is
a dropdown displaying the Name's of the accounts in alphabetic order, no
need for the orderby, so yes, the user is selecting by Name but of course
what's stored in Account is the corresponding id.
The orderby in Event is not redundant, as the event names need to be in
reverse chronological order in the dropdown, rather than alphabetic.
But appadmin still fails exactly the same way. Looks like a bug in appadmin
to me.
On Thursday, June 6, 2019 at 12:17:53 PM UTC-4, Ben Duncan wrote:
>
> Field('Account', 'reference CoA',
> requires=IS_IN_DB(db, 'CoA.id', '%(Name)s', orderby=
> db.CoA.Name)),
>
> To me, it looks like you are using "name" to reference the COA, instead of
> the ID.
>
> http://www.web2py.com/books/default/search/29?search=IS_IN_DB
>
> *Ben Duncan*
> DBA / Chief Software Architect
> Mississippi State Supreme Court
> Electronic Filing Division
>
>
> On Thu, Jun 6, 2019 at 10:25 AM David Manns <dgm...@gmail.com
> <javascript:>> wrote:
>
>> running Python 2.7, web2py 2.18.5
>>
>> table:
>>
>> db.define_table('AccTrans',
>> Field('Timestamp', 'datetime', default=request.now, writable=False),
>> Field('Bank', 'reference Bank_Accounts', writable=False), #e.g.
>> PayPal, Cambridge Trust, ...
>> Field('Account', 'reference CoA',
>> requires=IS_IN_DB(db, 'CoA.id', '%(Name)s', orderby=
>> db.CoA.Name)),
>> Field('Event', 'reference Events',
>> requires=IS_EMPTY_OR(IS_IN_DB(db, 'Events.id',
>> '%(Event)s', orderby=~db.Events.Date)),
>> comment='leave blank if not applicable'),
>> Field('Amount', 'decimal(8,2)',
>> comment='enter full amount of check (negative) or deposit
>> (positive); split using Edit if multiple accounts',
>> requires=IS_DECIMAL_IN_RANGE(-10000, 10000)), # >=0
>> for asset/revenue, <0 for liability/expense
>> Field('Fee', 'decimal(6,2)',
>> requires=IS_EMPTY_OR(IS_DECIMAL_IN_RANGE(-1000,100))), # e.g. PayPal
>> transaction fee, <0 (unless refunded)
>> Field('CheckNumber', 'integer', default=None,
>> requires=IS_EMPTY_OR(IS_INT_IN_RANGE(1,99999)),
>> comment='enter check number if recording a check
>> written'),
>> Field('Accrual', 'boolean', default=True, readable=True,
>> writable=False),
>> Field('FullAmt', 'decimal(8,2)', writable=False, readable=False),
>> # for an accrual, the original unsplit amount
>> Field('Reference', 'string', writable=False),
>> Field('Notes', 'text'),
>> singular='Transaction', plural='Check_Register')
>>
>> first referenced table:
>>
>> db.define_table('CoA',
>> Field('Name', 'string'),
>> Field('Notes', 'string'),
>> singular='Bank', plural='Banks', format='%(Name)s')
>> db.CoA.Name.requires = [IS_NOT_EMPTY(), IS_NOT_IN_DB(db, 'CoA.Name')]
>>
>> appadmin displays table rows just fine.
>>
>> clicking a record link in the id column to edit the record fails:
>>
>> Ticket ID
>>
>> 127.0.0.1.2019-06-06.11-23-25.64b3e296-56e8-4c9f-a875-5a0ae496f620
>> <type 'exceptions.KeyError'> 'Account.Name' Version
>> web2py™ Version 2.18.5-stable+timestamp.2019.04.08.04.22.03
>> Python Python 2.7.14: C:\Python27\python.exe (prefix: C:\Python27)
>> Traceback
>>
>> 1.
>> 2.
>> 3.
>> 4.
>> 5.
>> 6.
>> 7.
>> 8.
>> 9.
>> 10.
>> 11.
>> 12.
>> 13.
>> 14.
>> 15.
>> 16.
>> 17.
>> 18.
>> 19.
>> 20.
>> 21.
>> 22.
>>
>> Traceback (most recent call last):
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\restricted.py", line 219, in restricted
>> exec(ccode, environment)
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\applications\init\controllers/appadmin.py", line 695,
>> in <module>
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\globals.py", line 421, in <lambda>
>> self._caller = lambda f: f()
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\applications\init\controllers/appadmin.py", line 337,
>> in update
>> f='download', args=request.args[:1]))
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\sqlhtml.py", line 1550, in __init__
>> inp = self.widgets.options.widget(field, default)
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\sqlhtml.py", line 353, in widget
>> options = requires[0].options()
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\packages\dal\pydal\validators.py", line 2742, in
>> _options
>> options = self.other.options(*args, **kwargs)
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\packages\dal\pydal\validators.py", line 609, in
>> options
>> self.build_set()
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\packages\dal\pydal\validators.py", line 604, in
>> build_set
>> self.labels = [self.label % r for r in records]
>> File "C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\packages\dal\pydal\objects.py", line 103, in
>> __getitem__
>> raise KeyError(key)
>> KeyError: 'Account.Name'
>>
>> Error snapshot [image: help]
>> <http://127.0.0.1:8000/admin/default/ticket/init/127.0.0.1.2019-06-06.11-23-25.64b3e296-56e8-4c9f-a875-5a0ae496f620#>
>>
>>
>> <type 'exceptions.KeyError'>('Account.Name')
>>
>> inspect attributes
>> Frames
>>
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\restricted.py in restricted at line 219*
>> code arguments variables
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\applications\init\controllers\appadmin.py in
>> <module>
>> at line 695* code arguments variables
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\globals.py in <lambda> at line 421* code
>> arguments variables
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\applications\init\controllers\appadmin.py in update
>> at line 337* code arguments variables
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\sqlhtml.py in __init__ at line 1550* code
>> arguments variables
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\sqlhtml.py in widget at line 353* code
>> arguments variables
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\packages\dal\pydal\validators.py in _options
>> at
>> line 2742* code arguments variables
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\packages\dal\pydal\validators.py in options
>> at
>> line 609* code arguments variables
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\packages\dal\pydal\validators.py in build_set
>> at line 604* code arguments variables
>> -
>>
>> *File C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\gluon\packages\dal\pydal\objects.py in __getitem__
>> at
>> line 103* code arguments variables
>> Function argument list
>>
>> (self=<Row {'Accrued': -946.32, 'id': 1L}>, k='Account.Name')
>> Code listing
>>
>> 98.
>> 99.
>> 100.
>> 101.
>> 102.
>> 103.
>> 104.
>> 105.
>> 106.
>> 107.
>>
>> lg = BasicStorage.get(self, '__get_lazy_reference__', None)
>> if callable(lg):
>> v = self[key] = lg(key)
>> return v
>>
>> raise KeyError(key)
>>
>> __str__ = __repr__ = lambda self: '<Row %s>' % \
>> self.as_dict(custom_types=[LazySet])
>>
>> Variables
>> key 'Account.Name'
>> builtinKeyError <type 'exceptions.KeyError'>
>>
>> Context
>>
>> locals request session response
>> In file: C:\Users\David\Google Drive\My
>> Documents\OxCamNE.3.1\applications\init\controllers/appadmin.py
>>
>> 1.
>>
>> <code object <module> at 000000000BC220B0, file "C:\Users\David\Google
>> Drive\My Documents\OxCamNE.3.1\applications\init\controllers/appadmin.py",
>> line 7>
>>
>> --
>> 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 web...@googlegroups.com <javascript:>.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/web2py/bfd3002d-300b-49a5-9078-e229cafb9e3f%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/web2py/bfd3002d-300b-49a5-9078-e229cafb9e3f%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>> For more options, visit https://groups.google.com/d/optout.
>>
>
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/e857b963-67ea-4ec2-af3f-76e8ad7efe4c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
