Referencing another table

[James Booth]

Hey guys,

Excuse me if I'm missing something (or a lot), but I'm pretty experienced with web2py.

I have the following defined in my model:

db.define_table('bursary_users',
                Field('forename', type='string', requires=IS_NOT_EMPTY()),
                Field('surname', type='string', requires=IS_NOT_EMPTY()),
                Field('studentId', type='string', requires=IS_NOT_EMPTY(), unique=True),
                Field('barcode', type='string', requires=IS_NOT_EMPTY(), unique=True),
                Field('email', type='string', requires=IS_EMAIL(), unique=True),
                Field('bursary', requires=IS_IN_SET(['Free school meals','Bursary','Disabled'])),
                Field('notes', type='text'),
                format='%(studentId)s'
)
db.define_table('bursary_entries',
                Field('barcode', 'reference bursary_users', requires = [IS_IN_DB(db, 'bursary_users.barcode')]),
                Field('entry_date', type='datetime', default=request.now, readable=False, writable=False),
                format='%(barcode)s'
)

When I use the SQLFORM on a page, I can enter a barcode and it validates it (Fails if not in bursary_users, pass if it is). However, when I come to check the database, I'm getting a '0' (Althought it is hyperlinked to nothing) for the barcode value and a correct value for the entry_date.

Any ideas why this is happening?

Kind regards,

James. 

[James Booth]

And that was meant to be INexperienced with web2py. It's been a long week.

[Val K]

'reference bursary_users' and requires = [IS_IN_DB(db, 'bursary_users.barcode')]  - I think, that  it's mutually exclusive requirements 

'reference bursary_users' == requires = IS_IN_DB(db, 'bursary_users.id')

[James Booth]

How can I make the 'barcode' field in bursary_entries reference/depend upon a 'barcode' entry in bursary_users? Do I just have to alter my field to:

type='string', requires=IS_IN_DB(db,'bursary_users.barcode')

I was trying to make sense of what was needed here and I thought 'requires' was the best way of doing it, but does that only work for the primary key?

Thanks for your help.

[James Booth]

Okay, that seems to work fine, I guess I just tried to implement into incorrectly. Thanks for your help!

[Val K]

Keep in mind, that 
'reference ....' - realized at DB-level as FK-constraint, web2py allows to define reference to PK only (you try to refer to not-PK field) 

requires=...   - just web2py validator  and has no effect on underlying DB and there isn't an autovalidation of DB manipulation from code (except form.process()/validate()/accepts() ).  I mean  db.bursary_entries.insert( barcode  = 'invalid value') - doesn't cause any error

[vValentine1337]

Ah okay. It should only be done via. A validated form anyway but I see the potential risk. Is the other option to set the barcode as the PK in my bursary_users table and 'reference' it?

[黄祥]

i think it's can be done by record representation format, either in table format or in requires validation format representation. (the table bursary_entries just save the id for that refered to bursary_users, but it will shown the barcode, if you set the format correctly either in table format or requires validation)
ref:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#format--Record-representation

best regards,

stifan

[Val K]

If you want to have strict DB-constraints, you can: 

db.define_table('barcode',
                Field('code', type='string', unique=True, notnull=True), #  consider just 'code', not 'barcode' - it's necessary to prevent possible hard-to-catch bugs during SQLFORMs processing   
                format='%(code)s'
)

db.define_table('bursary_users',
         ...
                Field('barcode', type='reference barcode', notnull=True, unique=True),
         ...)

db.define_table('bursary_entries',
                db.bursary_users.barcode.clone(), # - .clone() is undocumented, but why?   

                Field('entry_date', type='datetime', default=request.now, readable=False, writable=False),
                format='%(barcode)s'
)

#be careful - above code could break your existing db-tables  

# this solution require additional logic to process insert in bursary_users,  look for   One form for multiple tables   

def new_user():

   #this would be invoked before insert in  db.bursary_users
   def reg_bar_code(form):
       new_code = form.vars.pop('code', None) #get user input for  db.barcode.code
       #any additional logic goes here
       form.vars.barcode =  db.barcode.insert(code = new_code)  # returns id of  new_code
            # so, form.vars.barcode would be used to  insert in  db.bursary_users.barcode

   db.bursary_users.barcode.writable = False  
     db.barcode.code.label  = 'barcode'  # 
   form=SQFORM.factory(db.barcode, db.bursary_users) 
   if form.process(onvalidation = reg_bar_code).accepted:
             db.bursary_users.insert(**db.bursary_users._filter_fields(form.vars))

   ....