0

A reference field in a web2py record contains a 0 before it is inserted despite it’s representation does not equal 0.

To explain in more detail, you see below a function and a table definition in a web2py model. The table contains bookings of staff members against an assignment of staff to a project element, “SB_AS_ref” refers to the corresponding record in “db.IT_C_AssignedStaff”, which in turn points to a staff record, and the staff record contains hourly rates needed for the value calculation (see "compute" in field SB_Value), as defined in calcSB_value(refTo_SB_AS_ref,hours).

This works during editing of a record, but when I add a record (in Data Administration or a grid, doesn’t make a difference), row['SB_AS_ref'] contains a zero, despite the fact that I’ve already selected a referred record “db.IT_C_AssignedStaff” in the front end (not so "hours", they correctly contain the value I’ve entered).

Which measures could I take to get the correct reference value before inserting a new record (or did I detect a bug)? Thanks in advance!

def calcSB_Value(refTo_SB_AS_ref,hours):
    rows = \ 
        db(db.IT_C_AssignedStaff.id==int(refTo_SB_AS_ref)
           ).select()
    staffID = rows[0].AS_ST_ref
    rows = db(db.IT_C_Staff.id==staffID).select()
    hourlyRate = rows[0].ST_HourlyRate
    return hours * hourlyRate

#----------------------------------------------
# MODEL
db.define_table('IT_C_StaffBooking',
                Field('SB_AS_ref', db.IT_C_AssignedStaff,
                      required=True,
                      notnull=True,
                      label='Reference to staff assignment',
                      authorize=False,
                      uploadfield=False,
                      uploadseparate=False,
                      ondelete='CASCADE',
                      ),
                Field('SB_Date', type='date',
                      required=True,
                      notnull=True,
                      comment='Latest booking date',
                      label='Date of activity',
                      authorize=False,
                      uploadfield=False,
                      uploadseparate=False,
                      ondelete='CASCADE',
                      ),
                Field('SB_hours', type='integer',
                      required=True,
                      notnull=True,
                      comment='Latest booking date',
                      label='duration of activity',
                      authorize=False,
                      uploadfield=False,
                      uploadseparate=False,
                      default='0',
                      ondelete='CASCADE',
                      ),
                Field('SB_Value', type='decimal(10,2)',
                      compute=
                          lambda row: calcSB_Value(row['SB_AS_ref'],row['SB_hours']),
                      notnull=True,
                      comment='Will be calculated',
                      label='Value = duration * hourly rate',
                      authorize=False,
                      uploadfield=False,
                      uploadseparate=False,
                      default='0',
                      ondelete='CASCADE',
                      ),
                format='%(SB_AS_ref)s, %(SB_Date)s '
                )
Richard
  • 721
  • 5
  • 16
JDi
  • 131
  • 1
  • 1
  • 5
  • You set `default='0'` which has the utility to populate the form field with a default value which in your case is `'0'` so that why you have already an input value in your form... This has nothing to do with representation at this point... The purpose of representation is to represent a ID field with a given column of the referencing table other then the ID column. – Richard Sep 08 '15 at 14:32
  • Here : `db.IT_C_AssignedStaff` you should use `reference IT_C_AssignedStaff` it is the prefered way of defining a reference table... Also, you shoulf follow PEP8 (https://www.python.org/dev/peps/pep-0008/) – Richard Sep 08 '15 at 14:46
  • I would see the code of your form for better understand your issue... – Richard Sep 08 '15 at 14:54
  • You specifiying `uploadfield=False` on every field definition if your fields are not of type `upload`... It make you model definition overly verbose for nothing... – Richard Sep 08 '15 at 15:03
  • Better `staff_id = db(db.IT_C_AssignedStaff.id == int(refTo_SB_AS_ref)).select(db.IT_C_AssignedStaff.‌​AS_ST_ref).first().AS_ST_ref` then `... rows[0].AS_ST_ref` – Richard Sep 08 '15 at 15:10
  • Thanks, Richard, it works now: Fresh inserted records now carry the right value (hours * hourly rate). I also appreciate your other comments, only PEP 8 will take a while. Btw. the verbosity comes from the fact that I define the tables and table fields in two tables (like MS Access) and a program I've written creates the source code. If I can make that program more mature, I'll release it to the public domain. Thanks again and regards! – JDi Sep 09 '15 at 05:39

0 Answers0