Web2py SQLFORM update or insert depending on record existence checked at callback

Question

I got stuck with the following challenge.

Can I use the callback function to decide whether to update a record or to insert a new one at form.process() call?

Simply put, I need a SQLFORM that users will fill with a textual reference identifier, and optionally a file to upload. That is, they should be able to upload a file with its reference or just insert a new reference and add its file later but through the same SQLFORM.

Model:

db.define_table('t_object_occurence',
    Field('f_refid', type='string', label=T('Reference')),
    Field('f_object_id', db.t_object, label=T('Object')),
    Field('f_temp_file', type='upload', label=T('File'), autodelete=True)
    #more fileds
)

Whenever users select a file, some jQuery will update the ref id field based on the file name (which is formally defined).

View:

{{if auth.has_permission('Object_occurence_UL'):}}
<h2>Add a new occurence</h2>
    {{=upload_form}}
{{pass}}
<h2>Latest occurences</h2>
{{=grid}}
<!-- some jQuery -->

Then, the following checks are performed (and others that are out of question's scope) through callback function called by form.process():

1. If a record with same ref id does not exist, then this form will have to generate a db insert. With or without the uploaded file.
2. If it does, 2 main cases:
   1. Either the existing record already got an uploaded file, then should raise a form error => that's done
   2. Or, if this record does not have its file, then I'd like it to get updated by the file form the upload filed.

Controller:

def object_browse():
    obj = db.t_object(request.args[0])
    upload_form = SQLFORM(db.t_object_occurence, fields=['f_refid', 'f_temp_file'])
    # recall object ID in form parameters
    upload_form.vars.f_object_id = long(request.args[0])
    # perform field values checks with specific function analyse_occurence_data()
    if upload_form.process(onvalidation=analyse_occurence_data).accepted:
        if upload_form.vars.f_temp_file != '':
            response.flash = "File upload OK, occurence " + str(upload_form.vars.f_refid) + " added to repository"
        else:
            response.flash = "New forcasted occurence added " + str(upload_form.vars.f_refid)
        redirect(URL('object_browse', args=[str(obj.id)], user_signature=True))
    # display occurence list
    query = (db.t_object_occurence.f_object_id==long(request.args[0]))
    grid = SQLFORM.grid(...)
    return dict(upload_form=upload_form, grid=grid)

Checks callback function:

def analyse_occurence_data(form):
    import types
    if isinstance(form.vars.f_temp_file, types.StringType):
        filename = None
    else:
        filename = form.vars.f_temp_file.filename
    occurence = form.vars.f_refid
    object_id = form.vars.f_object_id
    obj = db.t_object(object_id)
    # several checks and complementary form.vars.field = value
    potential_entry = db((db.t_object_occurence.f_refid==occurence)&(db.t_object_occurence.f_object_id==object_id)).select().first()
    if potential_entry is None:
        # let's insert a new record!
        return
    elif (filename is None) or (potential_entry.f_temp_file is not None):
        form.errors.f_temp_file = "The occurence "+occurence+" of "+obj.f_refid+" already exists in repository."
    elif potential_entry is not None:
            # THIS IS WHERE I'd like to decide to update instead of insert!

As my application cannot know before calling its callback if it will have to insert or update a record, I'm looking for the right instructions to give, so that my SQLFORM will do the right thing.

Thanks a lot.

Answer 1

Here a working example that can help you adapt your above code :

def onvalidation_insert_or_update(form):
    row = db(db.mytable.f1 == form.vars.f1).select(db.mytable.ALL).first()
    if row is not None:
        id = row.id
        db(db.mytable.id == id).update(f2=form.vars.f2)
        session.flash = 'Record updated'
    else:
        id = db.mytable.insert(**form.vars)
        session.flash = 'Record inserted'
    db.commit()
    session.flash = 'Record inserted'
    redirect(URL(c='default', f='read_form', args=id))
    return


def basic_controller():
    form = SQLFORM(db.mytable)
    if form.process(dbio=False, onvalidation=onvalidation_insert_or_update).accepted:
        pass
    elif form.errors:
        response.flash = 'form has errors'
    else:
        response.flash = 'please fill the form'
    grid = SQLFORM.grid(db.mytable)
    return dict(form=form, grid=grid)

def read_form():
    form = SQLFORM(db.mytable, record=request.args(0), readonly=True)  # readonly=True make form not modifiable
    return dict(form=form)

I can't attach the working app... But perhap, I can attach it with email if you ask your question to the web2py user google group, which is more convenient for this kind of question anyway.