Retrieving a record into the Masterlist using SQLFORM.grid selectable constructor

Question

Good day to all members here in stackoverflow and to all expert level in web2py!

This is about the School Year, Student List (Masterlist) and Organization Fines using the existing code that @Anthony Anthony gave me.

these are my database:

lets start on School Year:

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

next for the student list

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

and last for the Organization Fines table

db.define_table('org_fines',
            Field('sy_id','reference school_year'),
            Field('stud_id', 'reference student_list'),
            Field('total_fines', 'double'),
            Field('date', 'datetime'),
            Field('paid', 'boolean'))

this is the original code by Anthony

db.stud_adviser.sy_id.default = db.school_year(current_year=True).id
db.stud_adviser.adv_id.default = auth.user_id
def add_students(ids):
    for id in ids:
        db.stud_adviser.insert(stud_id=id)
form = SQLFORM.grid(db.student_list, create=False, selectable=add_students, csv=False, paginate=13)

and this the code I modified and still I can't make it work.

db.org_fines.sy_id.default = db.school_year(current_year=True).id
def members_fines(ids):
    for id in ids:
        db.org_fines.insert(stud_id=id)
form = SQLFORM.grid(db.student_list, create=False, selectable=members_fines, csv=False)

Anthony said on the comment and still some of his comment I don't understand (because Finals is coming):

The selectable argument is a callback function, which receives the list of record IDs selected in the grid. The add_students function supplied as that argument loops through the IDs and inserts a new record in the stud_adviser table for each one. Because the school year and adviser IDs should be the same for each record, they are set by setting the default attributes of their respective fields (for the school year, I assume you want the ID of the current school year) -- by excluding those fields from the .insert() call, the default values will be inserted automatically.

based on this comment:

Because the school year and adviser IDs should be the same for each record

since I don't have adviser ID in the org_fines table is it possible to use the code that Anthony gave it to me?

Direct question for Anthony

I still I don't get it: what are the difference on this codes: auth_user_id, auth_user.id and auth.user_id? when and where will I use these code? please enlighten me thanks in advance

What do you mean by "can't make it work"? What are you trying to achieve, and what is happening instead? — Anthony, Oct 12 '15 at 15:24
I want is the org officer will retrieve all the students in the student_list and put it in the orgs_fines table so that the org officer can put a fines on each students.... so I got an idea that I will use your code based on the code that you provide on my last post... since the org_fines table don't have adv_id so I remove the "db.stud_adviser.adv_id.default = auth.user_id" and change the "db.stud_adviser.insert(stud_id=id)" to "db.org_fines.insert(stud_id=id)" the result is error: "foreign key constraint failed" — MeSH, Oct 12 '15 at 15:40
@Anthony I mimic your code.. I add the auth_user in the org_fines table so this is the fields in org_fines: "'sy_id', 'reference school_year', label='School Year'", "'adv_id', 'reference auth_user', label='Org Officer'" and "'stud_id', 'reference student_list', label='Student'"... the function that will use in selectable: def add_students(ids): for id in ids: db.org_fines.insert(stud_id=id) and last the query: "form = SQLFORM.grid(db.student_list, create=False, csv=False, deletable=False, details=False, editable=False, selectable=add_students)" but it give me error... why? — MeSH, Oct 12 '15 at 16:56

score 0 · Answer 1 · answered Oct 12 '15 at 17:23

Problem Solved:

old code:

db.define_table('org_fines',
        Field('sy_id','reference school_year'),
        Field('stud_id', 'reference student_list'),
        Field('total_fines', 'double'),
        Field('date', 'datetime'),
        Field('paid', 'boolean'))

new code:

db.define_table('org_fines',
        Field('sy_id','reference school_year'),
        Field('studID', 'reference student_list'),
        Field('total_fines', 'double'),
        Field('date', 'datetime'),
        Field('paid', 'boolean'))

db.org_fines.sy_id.default = db.school_year(current_year=True).id
def add_students(ids):
    for id in ids:
        db.org_fines.insert(studID=id)
grid = SQLFORM.grid(db.student_list, create=False, csv=False, deletable=False, details=False, editable=False, selectable=add_students, 
                    paginate=13, orderby=db.student_list.lastname)

I just change the stud_id to studID in the org_fines... I don't know why is that the reason the code of Anthony won't work... so it need an explanation to enlighten me

I discovered new problem... why is this code not working? "maxtextlength={'org_fines.studID':128}" there are (...) three dots at the end of their name but the column width for the student is big... it can fit the full name of the student — MeSH, Oct 12 '15 at 18:01

Retrieving a record into the Masterlist using SQLFORM.grid selectable constructor

1 Answers1