smartgrid - limit dropdown menu items in edit form

[Paul Ellis]

I want to start using a smartgrid to browse a couple of tables as an admin tool.
There are a couple of companies using the same database.
When an admin edits an 'Offer' I want them to be able to change the user_id. Effectively assigning the offer to a different staff member in the event of staff changes or holidays etc.

The problem is the drop down menu shows all users in the database, not just the users which belong to the company in question.

db.define_table(
    'offer',
    Field('offer_number', 'integer', unique=True, label = T('Offer Number')),
    Field('user_id','reference auth_user', label = T('Created By'), ondelete = 'SET NULL'),
    Field('customer_id','reference customer', requires=IS_NOT_EMPTY(), label = T('Customer Name')),
    Field('reseller_id','reference reseller', requires=IS_NOT_EMPTY(), label = T('Business Name')),
    Field('created_on', 'datetime', default=request.now, writable=False, label=T('Created On')),
    Field('updated_on', 'datetime', default=request.now, writable=False, label=T('Updated On')),
    singular = T('Offer'),
    plural = T('Offers'),
    )

db.define_table(
    'remarks',
    Field('offer_id', 'reference offer', requires=IS_NOT_EMPTY()),
    Field('remorder', 'integer', label=T('Postion')),
    Field('remark', 'text', label=T('Remarks')),
    singluar = T('Remark'),
    plural = T('Remarks'),
    )

and the grid:

@auth.requires_membership('business leader')
def offers():
    pagetitle = 'offers'
    buttongroup = []
   
   
    db.offer.offer_number.writable = False
    db.offer.reseller_id.writable = False
    db.offer.reseller_id.readable = False
    # db.offer.user_id.writable = False
   
    pagecontent = SQLFORM.smartgrid(
        db.offer,
        details = False,
        constraints = {
            'offer' : db.offer.reseller_id == session.auth.user.reseller_id,
            },
        linked_tables = [
            'offer',
            'remarks',
            ],
        fields = {
            'offer' : [
                db.offer.offer_number,
                db.offer.user_id,
                db.offer.customer_id,
                db.offer.created_on,
                db.offer.updated_on,
                ],
            },
        )
       
    response.view = 'tooladmin_core.html'
    return dict(
        pagetitle = pagetitle,
        buttongroup = buttongroup,
        pagecontent = pagecontent,
        )

[Jim S]

I'm assuming you have a company_id added to your auth_user table to identify which company a user belongs to.

If that is correct, then I'd add the following ahead of the SQLFORM.smartgrid call

db.offer.user_id.requires = IS_IN_DB(db(db.auth_user.company_id == auth.user.company_id), db.auth_user, '%(first_name)s %(last_name)s', zero='Select User...')

...or something like that.  I didn't test the solution.  Read more about it here http://web2py.com/books/default/chapter/29/07/forms-and-validators#Database-validators

Another option would be to add a common filter, possibly in your db.py file to apply this rule to the entire site.

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=common+filter#Common-filters

[Paul Ellis]

Your assumption is correct and the solution worked perfectly. I didn't realise I could use the requires method in that way. I have now read more about it.

Thanks

--
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/3VwXtWiCqP8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.