Display the actual data instead of a table using SQLFORM.grid

Question

Good day!

I decided to ask new question since Anthony solve my problem.

Based on the given code here. I want to display the actual value not the ID when displaying the data in SQLFORM.grid.

School Year    Adviser   Student
1              walker    1

Note: walker is the username

this data retrieving is ok but I want is more in informative way.

I want to display in SQLFORM.grid are like this:

School Year    Adviser         Student
2015-16-1      Walker, Paul    Doe, John

is this possible in SQLFORM.grid?

these are the codes before calling the SQLFORM.grid

db.stud_adviser.sy_id.default= db.school_year.sy
db.stud_adviser.adv_id.default= db.auth_user.last_name
db.stud_adviser.stud_id.default= db.student_list.lastname

but still the result is the same:

School Year    Adviser   Student
1              walker    1

nothing happen. BUT when editing the data using SQLFORM.grid the result are working. Because these are the codes I used.

db.stud_adviser.sy_id.requires= IS_IN_DB(db, 'school_year.id', '%(sy)s')
db.stud_adviser.adv_id.requires=IS_IN_DB(db,'auth_user.id', '%(last_name)s')
db.stud_adviser.stud_id.requires=IS_IN_DB(db,'student_list.id', '%(lastname)s')

but as a programmer you don't want the user to view the students (currently we have 300+ infoTech students) one by one by clicking the edit in SQLFORM.grid just to know who is Student # 1, what School Year is # 1 and who is the Adviser that uses the walker username.

Answer 1

First, don't do this:

db.stud_adviser.sy_id.default= db.school_year.sy
db.stud_adviser.adv_id.default= db.auth_user.last_name
db.stud_adviser.stud_id.default= db.student_list.lastname

Defaults must be either actual values or functions that return values -- they cannot be Field objects (and it's not even clear what the above assignments would mean).

Second, there is no need to explicitly set the requires attributes of those fields -- if you instead set the format attribute of each table, any fields that reference those tables will receive a default requires attribute and a default represent attribute based on the format attribute of the referenced table.

The code in this answer is almost exactly what you need -- the only difference is that you now want slightly different formatting of the display values (i.e., you now want "Lastname, Firstname" instead of just "Lastname"). So, instead of setting the format attributes to '%s(lastname)s' and '%s(last_name)s', change them to '%s(lastname)s, %(firstname)s' and '%s(last_name)s, %(first_name)s'.

Answer 2

@Anthony while I'm on school experimenting the code, I successfully (hope so) manage to show what output I want. For the Adviser and Student column it shows the Last name and First name but the School Year still ID, here's the code:

db1.py

db.define_table('school_year',
            Field('sy',),
            Field('current_year', 'boolean'),
            format='%(sy)s')

db.define_table('adviser',
            Field('sy_id', 'reference school_year', label='School Year'),
            Field('adv_id', 'reference auth_user', label='Adviser'))

db.define_table('student_list',
            Field('lastname'),
            Field('firstname'),
            Field('middlename'),
            Field('address'),
            format='%s, %s' % ('%(lastname)s', '%(firstname)s'))
#           format=('%(lastname)s', '%(firstname)s'))

db.define_table('stud_adviser',
            Field('sy_id', 'string', 'reference school_year', label='School Year'),
            Field('adv_id', 'reference auth_user', label='Adviser'),
            Field('stud_id', 'reference student_list', label='Student', unique=True))

controller:

def myStudent():

    db.stud_adviser.id.readable=False
    db.auth_user._format = '%s, %s' % ('%(last_name)s', '%(first_name)s')
    form = SQLFORM.grid(db.stud_adviser, csv=False, create=False, details=False, editable=False)
    return locals()

for the Full name on the Adviser table I use your code:

db.auth_user._format = '%(last_name)s', '%(first_name)s'

the result the Adviser change to IDs but if I change it to:

db.auth_user._format = '%s, %s' % ('%(last_name)s', '%(first_name)s')

and it worked.

same also in the student_list, the data change to IDs if I use this code:

format=('%(lastname)s', '%(firstname)s'))

I want to post an image here but my reputation is not enough.