Smartgrid with multiple references between tables - Undocumented - Solved

[Paul Ellis]

When using Smartgrid and there are multiple links between two tables. For example a user_id might appear multiple times in db.offer because of created_by, updated_by, belongs_to fields.

db.define_table(

    'offer',

    Field('offer_number', unique=True, label = T('Offer Number')),

    Field('user_id','reference auth_user', label = T('Belongs to'), ondelete = 'SET NULL'),           # <- HERE

    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('created_by', 'reference auth_user', label=T('Created By')),                                # <- HERE

    Field('updated_on', 'datetime', update=request.now, writable=False, label=T('Updated On')),

    Field('updated_by', 'reference auth_user', writable=False),                                       # <- HERE

        format = '%(offer_number)s',

        singular = T('Offer'),

        plural = T('Offers'),

        )

When viewing db.auth_user Smartgrid makes a button for each relationship i.e Customers(Belongs To), Customers(Created By), Customers(Updated By). This can clutter the grid a bit.

This is not in the book but it is possible for the linked_tables argument, rather than a List, to be a Dict of:

{'tablename' : [Fields]}

an empty list allows all references to be used. This is handy for tables where there is already only one reference. The Table still needs to be included in the dict. Otherwise Smartgrid will exclude it entirely.

linked_tables = {

    'reseller' : [

        db.auth_user.reseller_id,

        db.customer.reseller_id,

        db.offer.reseller_id

        ],

    'auth_user' : [

        db.customer.created_by,

        db.offer.user_id,

        db.auth_membership.user_id,

        ],

    'auth_membership' : [],

    'customer' : [

        db.offer.customer_id,

        ],

    'offer' : [],

    },

Source: Web2Py Version 2.18.5 gluon/sqlhtml.py Lines [3316 : 3353]