Mysterious key error

[Seraaj Muneer]

Hello everyone again, still taking web2py for a spin but encountering some really strange errors. 

Here is my data definition 

company_field_id = 'company_id'

db.define_table('bank', Field('bank_name', 'string', length=25, represent=lambda bank_name, row: bank_name.upper(),
 required=True,
 unique=True, notnull=True,
 ),
 Field('default_branch', 'string', default='', length=25,
 requires=[IS_EMPTY_OR(IS_MATCH('^[a-zA-Z][a-zA-Z ]*$'))]),
 Field(company_id_field, 'reference company', notnull=True, ondelete=on_delete_cascade,
 writable=False, readable=False), Field('request_tenant', default=auth.user_id, writable=False),
 auth.signature, common_filter=lambda query: db.bank.company_id == get_default_company_id(),
 format=lambda r: r.bank_name or 'No bank name!')
db.bank.bank_name.requires = [IS_NOT_EMPTY(error_message='You must enter a bank name'),
 IS_MATCH('^[a-zA-Z][a-zA-Z ]*$',
 error_message='Bank name can only contain alphabets (A-Z)'),
 IS_NOT_IN_DB(db, db.bank.bank_name, error_message='Bank name must be unique.')]

db.bank._before_insert.append(
 lambda f: f.update(
 company_id=get_default_company_id()))

and here is my controller

@auth.requires_login()
def show():
 db(db.company.id > 0).count() or redirect(URL('company', 'create'))
 form = SQLFORM.smartgrid(db.bank)
 return dict(form=form)





and here is my view

{{extend 'layout.html'}}
{{=form}}


Nothing I do gets this to work. Web2py always returns the error as per the screenshot attached. I've done a great deal of googling to no avail. Whats wrong? 

[Derek]

company_field_id and company_id_field are not the same. Look closely.

[Saeed]

That was just a typing error when I was writing the message here. It's actually correctly defined in my code.

--
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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/fOfE_wct24Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anthony]

What version of web2py? Can you attach a minimal app that reproduces the problem?

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Seraaj Muneer]

Web2py 2.13.4. Essentially the code in the original post here is all there is in the app. Initially, it was working just fine, then it suddenly stopped with that error. 

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Seraaj Muneer]

I think I figured it out. Using the table property 'common_filter' AND the special field 'request_filter' on the same table is what was causing it. Removing one of those fixed the issue for me. 

It appears the two multi-tenant helpers are mutually exclusive

[Seraaj Muneer]

It appears that placing the 'common_filter' in the table definition together with the speical field 'request_tenant' is problematic. However,  doing

db.bank.common_filter = lambda query: db.bank.company_id == get_default_company_id()

seem to also work with no issues.

[Anthony]

On Saturday, January 23, 2016 at 1:22:52 AM UTC-5, Seraaj Muneer wrote:

It appears that placing the 'common_filter' in the table definition together with the speical field 'request_tenant' is problematic. However,  doing

db.bank.common_filter = lambda query: db.bank.company_id == get_default_company_id()

This shouldn't make a difference, nor do I think it should be a problem to use both request_tentant and common_filter together. Above, you have specified db.bank.common_filter rather than db.bank._common_filter, so your above code isn't actually implementing a filter at all.

Note, your combination of request_tenant and common_filter requires that there are records where request_tentant matches auth.user_id and company_id matches the value returned by get_default_company_id. Is it possible that there are simply no records that meet those two conditions?

Also, why bother with request_tenant here at all? You've already got auth.signature in the table definition, and setting request_tenant equal to auth.user_id is equivalent to applying a filter where created_by (or modified_by) is equal to auth.user_id. So you could just add that to your common filter.

Finally, you can get rid of your _before_insert callback and instead simply set the default value of the company_id field directly:

Field(company_id_field, 'reference company', default=get_default_company_id())

Anthony

[Seraaj Muneer]

Unfortunately refactoring to 

db.define_table('company',
                Field('name', 'string', length=20, represent=lambda name, row: name.upper(),
                      required=True,
                      unique=True, notnull=True,

                      ),

                Field('is_default', 'boolean', label=T('Default Company'),
                      default=False),
                address,
                auth.signature, common_filter=lambda query: db.company.created_by == auth.user_id,
                format=lambda r: r.name.upper() or 'No name company')
db.company.name.requires = [IS_NOT_EMPTY(error_message='You must enter a company name'),
                            IS_MATCH('^[a-zA-Z0-9][a-zA-Z0-9 ,-.]*$'),
                            IS_NOT_IN_DB(db, db.company.name, error_message='This company name is in use')]

def set_all_default_company_to_false(fields):
    if fields.get('is_default'):
        rows = db(db.auth_user.id == auth.user_id).select(db.auth_user.ALL)
        for row in rows:
            for company in row.company.select(db.company.id, db.company.is_default):
                if company.is_default:
                    db(db.company.id == company.id).update(is_default=False)


db.company._before_insert.append(lambda f: set_all_default_company_to_false(f))
db.company._before_update.append(lambda s, f: set_all_default_company_to_false(f))


def get_default_company_id():
    rows = db(db.auth_user.id == auth.user_id).select(db.auth_user.ALL)
    for row in rows:
        for company in row.company.select(db.company.id, db.company.is_default):
            if company.is_default:
                return company.id

db.define_table('bank', Field('bank_name', 'string', length=25, represent=lambda bank_name, row: bank_name.upper(),
                              required=True,
                              unique=True, notnull=True,
                              ),
                Field('default_branch', 'string', default='', length=25,
                      requires=[IS_EMPTY_OR(IS_MATCH('^[a-zA-Z][a-zA-Z ]*$'))]),

                Field(company_id_field, 'reference company', notnull=True, default=get_default_company_id(),
                      ondelete=on_delete_cascade,
                      writable=False, readable=False),

                auth.signature, common_filter=lambda query: db.bank.company_id == get_default_company_id(),
                format=lambda r: r.bank_name or 'No bank name!')
db.bank.bank_name.requires = [IS_NOT_EMPTY(error_message='You must enter a bank name'),
                              IS_MATCH('^[a-zA-Z][a-zA-Z ]*$',
                                       error_message='Bank name can only contain alphabets (A-Z)'),
                              IS_NOT_IN_DB(db, db.bank.bank_name, error_message='Bank name must be unique.')]

returns me back to the mysterious 

<type 'exceptions.KeyError'>

Version

with this traceback 

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18.

Traceback (most recent call last): File "/home/seeraj/PycharmProjects/payroll/web2py/gluon/restricted.py", line 227, in restricted exec ccode in environment File "/home/seeraj/PycharmProjects/payroll/web2py/applications/payroll/controllers/bank.py", line 18, in <module> File "/home/seeraj/PycharmProjects/payroll/web2py/gluon/globals.py", line 412, in <lambda> self._caller = lambda f: f() File "/home/seeraj/PycharmProjects/payroll/web2py/gluon/tools.py", line 4236, in f return action(*a, **b) File "/home/seeraj/PycharmProjects/payroll/web2py/applications/payroll/controllers/bank.py", line 4, in banks grid = SQLFORM.smartgrid(db.bank) File "/home/seeraj/PycharmProjects/payroll/web2py/gluon/sqlhtml.py", line 3056, in smartgrid user_signature=user_signature, **kwargs) File "/home/seeraj/PycharmProjects/payroll/web2py/gluon/sqlhtml.py", line 2721, in grid value = row[str(field)] File "/home/seeraj/PycharmProjects/payroll/web2py/gluon/packages/dal/pydal/objects.py", line 76, in __getitem__ raise KeyError KeyError 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. @auth.requires_login() def banks(): not db(db.company.created_by == auth.user_id).isempty() or redirect(URL('company', 'create')) grid = SQLFORM.smartgrid(db.bank) return dict(grid=grid) @auth.requires_login() def create(): not db(db.company.created_by == auth.user_id).isempty() or redirect(URL('company', 'create')) form = SQLFORM(db.bank) if form.process().accepted: response.flash = 'Bank saved successfully' else: response.flash = 'Use the form to create a new bank' return dict(form=form) response._vars=response._caller(banks)

web2py™	Version 2.13.4-stable+timestamp.2015.12.26.04.59.39

[Seraaj Muneer]

To be clear, I didn't insert the last line 

response._vars=response._caller(banks)

in the controller.  Web2py did that. 

[Seraaj Muneer]

If I take out common_filter=lambda query: db.bank.company_id == get_default_company_id() from the table definition, everthing works just fine.