<type 'exceptions.ValueError'> invalid literal for long() with base 10: when linking 2 tables

[mostwanted]

I have 2 tables one table references the other table.

db.define_table('store_registration',
                Field('first_name', requires=IS_NOT_EMPTY()),
                Field('last_name', requires=IS_NOT_EMPTY()),
                Field('contact_number', label=SPAN('Cell Number'), requires=IS_NOT_EMPTY()),
                Field('id_number', requires=IS_NOT_EMPTY()),
                Field('program', requires=IS_NOT_EMPTY()),
                Field('level_is', label=SPAN('Level'), requires=IS_NOT_EMPTY()),
                Field('module_is', label=SPAN('Module'), requires=IS_NOT_EMPTY()),
                Field('branch', label=SPAN('Branch'), requires=IS_NOT_EMPTY()),
                Field('residential_place', label=SPAN('Residence'), requires=IS_NOT_EMPTY()),
                Field('region', 'reference auth_group', default=get_group(), writable=False), #*Here*
                Field('recorded_by', 'reference auth_user', default=auth.user_id, writable=False),
                format="%(first_name)s %(last_name)s"
               )

db.define_table('temperatures',
                Field('employee', 'reference store_registration', writable=False),
                Field('branch', 'reference store_registration', writable=False),
                Field('temperature', requires=IS_NOT_EMPTY()),
                Field('ID_No', 'reference store_registration', writable=False),
                Field('Cell_No', 'reference store_registration', writable=False),
                Field('residential', 'reference store_registration', writable=False))

I am trying to have the referenced fields pre-populated with their default values in a table form so that I don't have to populate them manually like this:

def client_details():
    details=db.store_registration(request.args(0, cast=int))

    db.temperatures.employee.default=details.id
    db.temperatures.ID_No.default=details.id_number
    db.temperatures.Cell_No.default=details.contact_number
    db.temperatures.branch.default=details.branch
    db.temperatures.residential.default=details.residential_place
    form=SQLFORM(db.temperatures)
    if form.process().accepted:
        response.flash=T('Temp Recorded')
    return locals()

But this gives me an error: 

<type 'exceptions.ValueError'> invalid literal for long() with base 10:

I read somewhere that this occurs because


reference fields are intended to store the integer record ID's of the referenced records, not copies of string
fields from referenced records.


Is there a way I can achieve my above task without causing this error?!

Regards;




Mostwanted


[Dave S]

I think you want to use an inner join.

<URL:https://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Inner-joins>

and to have just one field referencing the other table (a "points_to" field),..

/dps

 

[mostwanted]

Hey Dave thanks for replying. I think i'm going about this the wrong way. The temperature table already references the store_registration table via the employee variable. By my understanding the employee variable already holds all the information in the store_registration table & I need some values from the store_registration table(contact_number, id_number, branch, residential_place) to be unpacked & stored with the new details (temperature) in the temperature table without me having to re-enter them again, isnt there a way for this?

I have been looking at the inner-joins I just dont get how I can apply them here, a small example with regards to issue would go a long way.

Thank you 😊

[Annet]

Hi,

I think you'd better reference your store_registration table once by id in the temperature table.

Field('store_registration_id', 'reference store_registration', writable=False),


In that way you comply with the rule of single_point-of_definition. The way you define your temperatures

table, when a user updates his contact_number it has to cascade to the temperatures table.

What you want can be achieved by 'reference store_registration' you could remove the reference and

replace it with an IS_IN_DB() validator for instance for Cell_No:

IS_IN_DB(db, 'store_registration.contact_number', '%(contact_number)s')

and then write your own ON UPDATE CACADE functions

Kind regards,

Annet