form grid Order by a Field Value in the Parent Table

[rafi farchi]

Hi

I have a parent chile relationship Vessels / Voyages .

Each vessel may have 1 or more Voyage .

The Voyage Grid form attached is working fine except that i want the Order to by Vessel name(from Vessels ) and then VoyageNumber (from voyages) .

As it is written now  the order is by Vessel.id , VoyageNumber .

Model

---------

　

db.define_table('EQ_Vessels',

Field('VesselCode', 'string' , length=3 , notnull = True,unique = True) ,

Field('VesselName', 'string', length=50, notnull = True,unique = True) ,

format='%(VesselName)s' )

db.EQ_Vessels._plural='Vessels'

　

　

db.define_table('EQ_Voyages',

Field('Vessel','reference EQ_Vessels' ,required=True ) ,

Field('VoyageNumber', 'string' , notnull = True , required=True )

)

db.EQ_Voyages._plural='Voyages'

　

　

db.EQ_Voyages.VoyageNumber.requires=IS_NOT_IN_DB(

db(db.EQ_Voyages.Vessel==request.vars.Vessel), 'EQ_Voyages.VoyageNumber')

　

　

Controller

-------------

　

def Voyages():

Voyages = SQLFORM.grid(db.EQ_Voyages,csv=False , orderby=db.EQ_Voyages.Vessel| db.EQ_Voyages.VoyageNumber)

return dict(form=Voyages)

　

[tim.n...@conted.ox.ac.uk]

Your grid should be using a query that brings in both tables, rather than just the Voyages table:

def Voyages():
    Voyages = SQLFORM.grid(db.EQ_Voyages.Vessel == db.EQ_Vessels.id, fields=[db.EQ_Vessels.VesselName, db.EQ_Voyages.VoyageNumber], csv=False, orderby=[db.EQ_Vessels.VesselName, db.EQ_Voyages.VoyageNumber])
    return dict(form=Voyages)

If you're using the grid to do built in view/edit/delete, you might want to set field_id

[rafi farchi]

On Tuesday, September 12, 2017 at 12:12:53 PM UTC+3, tim.n...@conted.ox.ac.uk wrote:

Your grid should be using a query that brings in both tables, rather than just the Voyages table:

def Voyages():
    Voyages = SQLFORM.grid(db.EQ_Voyages.Vessel == db.EQ_Vessels.id, fields=[db.EQ_Vessels.VesselName, db.EQ_Voyages.VoyageNumber], csv=False, orderby=[db.EQ_Vessels.VesselName, db.EQ_Voyages.VoyageNumber])
    return dict(form=Voyages)

If you're using the grid to do built in view/edit/delete, you might want to set field_id

Thanks , it solved the problem