Foreign Key fields as null?

[joe]

Is there any way to make a field in a form referencing a foreign key null, while storing a foreign key as a foreign key, but making dropdowns corrospond to the format.  Here is my example:

Model:

db = DAL('sqlite://storage.sqlite')

db.define_table('person',
    Field('name'),
    Field('email'),
    format = '%(name)s')
    
db.define_table('dog',
    Field('name'), 
    Field('owner', requires = IS_EMPTY_OR(IS_IN_DB(db,db.person))), #I have also tried default=None, and required = False, no difference
    format = '%(name)s')

Controller:

from gluon.tools import Crud
crud = Crud(db)

def index():
    form = SQLFORM(db.person)
    if form.process().accepted:
        response.flash = 'success'
    return dict(form=form)
    
def add_dog():    
    form = SQLFORM(db.dog)
    if form.process().accepted:
        response.flash = 'success'
    return dict(form=form)

def view():
    grid = SQLFORM.smartgrid(db.dog)
    grid2 = crud.select(db.person)
    return dict(grid=grid, grid2=grid2)

When I have the above code, a foreign key will be stored in the database, but all views of that key will be rendered as the numerical value of the foreign key, and not the name.  In the table definition, if I just have db.person instead of requires = IS_EMPTY_OR(IS_IN_DB(db,db.person)), It requires the field.  

Are there any workarounds?

-Joe Peacock

[Anthony]

You should probably make it a reference field. By default, if you don't specify any "requires", you'll automatically get an IS_IN_DB validator as well as a "represent" attribute that displays whatever is specifying by the "format" attribute of the referenced table. However, if you specify your own validator(s), then you don't get any default "represent" -- so just add your own explicit represent:

db.define_table('dog',
    Field('name'),

    Field('owner', db.person, requires=IS_EMPTY_OR(IS_IN_DB(db, 'person.id', '%(name)')),
          represent=lambda id, row: db.person(id).name),
    format = '%(name)s')

Anthony

[joe]

That hasn't worked for me.  When I try, I get the error:

<type 'exceptions.KeyError'> 'name'

It points to the smartgrid I made, with this code:  grid = SQLFORM.smartgrid(db.dog)

Thanks!

-Joe Peacock

[Anthony]

Oh, right, since you allow empty values, you'll need to test for that before doing the query:

represent=lambda id, row: db.person(id).name if id else 'Nobody owns this dog'

Anthony

[Anthony]

Actually, I would think that would have caused an AttributeError (trying to access an attribute of a NoneType object). Not sure where the KeyError is arising.

Anthony

[joe]

The key error was because it was '%(name)' not '%(name)s'.  Everything works now! Thanks!

[Mandar Vaze]

I too am getting similar error (am using sqlform.grid, not smartgrid)

I've posted a query here : https://groups.google.com/d/msg/web2py/SxTKewH-nS8/G5iT8ycUAKgJ

Waiting for feedback

Thanks,

-Mandar