Auto update db records behaviour per input field on release (AJAX)

[Francisco G. T. Ribeiro]

hi all,

I'm working on an app that uses forms that can be quite long and its users often interrupt their sessions for whatever reason and end up losing the information already filled. For this and other reasons I wanted to provide a different behaviour to these forms where each input field updates the record on the database as soon as its input field is released ('focusOut' event on jQuery). Ideally, the server would reply with 'success' or an error message so users know when they can move on to another field (without refreshing the whole page). By the end of the form, the user wouldn't have to review things that were written long ago since these were all already validated.

Now, I know this can be tricky due to database constrains but because i need to do this very often (multiple fields and multiple forms), I thought it would be useful to automate it, maybe even by having on the db Field something like '..auto_update=True' (merely a suggestion) but before getting there, I would like to know if anyone has faced this problem and if yes what solution did you employ? 

Thank you in advance,

Francisco

[Derek]

Try 'parsely'

http://parsleyjs.org/doc/examples/simple.html

and prompt on page close to save first.

[Francisco G. T. Ribeiro]

Thank you for stepping up to reply but 'parsely' looks more like a library for client-side form validation which is not really the major problem I am trying to address. My goal is to have a mechanism that stores (with persistence) information provided by the user as soon as possible once it is provided input field by input field (on focusOut event) , rather than just doing all at once when the form is submitted. Anyway, thanks :)

Francisco

[Francisco G. T. Ribeiro]

Replying to my own question:

1) First we need to create a record with minimal data on it upon which we can trigger updates. Suppose you want to create an entry on a table called 'task', you could do something like:

def new():
  tableName='task'
  createForm=SQLFORM(db[tableName],
                     fields=[field for field in db[tableName].fields if db[tableName][field].required])
                     
  if createForm.process().accepted:
    updateForm=crud.update(db[tableName],createForm.vars.id)
    response.view='task/updform.html'
    response.flash = T('task created')
    return dict(form=updateForm)    
 
  elif createForm.errors:
      response.flash = T('form has errors')

with a view 'task/new.html' with: 

{{=createForm}}

and another view 'task/updform.html' (displayed further below)

  

2) Now, it's where the interesting stuff happens. OnFocusOut will trigger AJAX requests to trigger updates on our table as the user fills each field. So, we need to add a function for this purpose as the following:

SUCCESS='OK'
INVALID_PARAMS='INVALID_PARAMS'
GENERIC_ERROR='UNKNOWN'
DB_ERROR='FAIL'
INVALID_DB_PARAMS=INVALID_PARAMS # should only be edited for debug purposes

UPD_TABLES_ALLOWED=['task']

@auth.requires_login()

def updateTableService():

  retValue=None

  

  if len(request.vars) <6 or not set(['tableName','rowId','fieldName','fieldValue','_formName']).issubset(set(request.vars.keys())): # basic input validation

    return INVALID_PARAMS

  tableName,rowId,fieldName,fieldValue=request.vars['tableName'],request.vars['rowId'], request.vars['fieldName'], request.vars['fieldValue']

  if not (tableName in UPD_TABLES_ALLOWED and fieldName in db[tableName].fields()):  # db params check

    return INVALID_DB_PARAMS

  formKey='_formkey[%s]' % (request.vars._formName)
  if session.has_key(formKey) and request.vars.has_key('_formKey') and request.vars._formKey in session[formKey]: # CSRF check
    retValue = db(db[tableName].id==int(rowId)).validate_and_update(**{fieldName:fieldValue})
    db.commit()
   
    if(len(retValue['errors'].keys()) > 0):
      response.flash = T('%s input field: %s' % (retValue['errors'].keys()[0],retValue['errors'].values()[0]))
      return DB_ERROR
     
    elif (retValue['updated'] > 0):
      response.flash = T('value updated!')
      return SUCCESS
 
  response.flash = T('Unauthorized request')
  return GENERIC_ERROR

and a view 'updform.html' to trigger it as follows:

{{extend 'layout.html'}}


{{=form}}


<script>
jQuery(':input').focusout(function(){
inputField = $(this)
var name = inputField.attr("name");
var value = inputField.val();


var dataObj = {};
dataObj['fieldName']=name
dataObj['fieldValue']=value
dataObj['tableName']=inputField.closest('form')[0]._formname.value.split('/')[0]
dataObj['rowId']=inputField.closest('form')[0]._formname.value.split('/')[1]
dataObj['_formKey']=inputField.closest('form')[0]._formkey.value
dataObj['_formName']=inputField.closest('form')[0]._formname.value
        $.ajax({
            cache: false,
            url: '{{=URL('updateTableService')}}',
            type: 'post',
            data: dataObj,
            }).done(function(responseText){


            if(responseText=='OK'){
                inputField.css('backgroundColor','green');
                inputField.css('color','white');
                $(inputField).animate({backgroundColor: "#E9F5EB", color: '#333'});
                inputField.css('background-color','inherit');
                inputField.css('color','inherit');
}       else{
                inputField.css('backgroundColor','red');
                inputField.css('color','white');
                $(inputField).animate({backgroundColor: "#FAEFDE", color: '#333'});
                inputField.css('background-color','inherit');
                inputField.css('color','inherit');
}


            }).fail(function(responseText){
                inputField.css('backgroundColor','red');
                inputField.css('color','white');
                $(inputField).animate({backgroundColor: "#FAEFDE", color: '#333'},1000);
                inputField.css('background-color','inherit');
                inputField.css('color','inherit');


            });
        });
</script>

3) Just one more thing...

The code above, should already work in the ideal scenario but what if the user interrupted the session and wants to pick it up where he or she left off? We can add a function for that which you can link with baseUrl/tableName/rowId request args, as follows:

def updForm():
  if len(request.args) != 2:
    return INVALID_PARAMS
  tableName=request.args(0, cast=str)
 
  if not (tableName in db.tables):  # db params check
    return INVALID_DB_PARAMS
  else:
    form=crud.update(db[tableName],request.args(1, cast=int))
    return dict(form=form)
  return GENERIC_ERROR

Please, let me know what you think, problems, suggestions and if you like it.

Cheers,

Francisco Ribeiro

[Derek]

If you read, I suggested that when the 'page close' or 'navigate away' event is fired, you can trigger a save then (one option). You can use parsely to manage your validators (because you don't want to save invalid data). It will do run-time validation, which you can then hook into to do the saving for you, so as soon as valid data is entered, it is saved.

Another option is to collect a small amount of information at a time. Such like a 'wizard' interface. Take a look here for what I'm talking about:

http://parsleyjs.org/doc/examples/multisteps.html

On Friday, June 13, 2014 3:29:20 PM UTC-7, Francisco Ribeiro wrote:

Thank you for stepping up to reply but 'parsely' looks more like a library for client-side form validation which is not really the major problem I am trying to address. My goal is to have a mechanism that stores (with persistence) information provided by the user as soon as possible once it is provided input field by input field (on focusOut event) , rather than just doing all at once when the form is submitted. Anyway, thanks :)

Francisco

On Friday, 13 June 2014 21:06:48 UTC+1, Derek wrote:

[Francisco Gama]

Derek,

that bit where you mention hooking "run-time" validation to be saved, is pretty much what I’m doing in my post. You need to consider that you might not have a complete record to insert, but one field at the time, hence why I create the ‘updateTableService()’. From what I understand, the only difference between what you suggest and what I did, is that on my code, the validation is fully done on the server side. The advantages are that I can apply validators such as ‘isUnique’ (among others that require the DB access) as well enforce input validation (from a security standpoint, there is no such thing as client-side input validation). The downside is obviously performance whenever things don’t even need to reach the server-side and parsley is able to do them immediately within the browser.

I guess I could add parsley to get the best of both worlds...

Thank you,

Francisco

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/v1MD3u5ZLm0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Francisco Gama]

Small errata on my own post:

I’m also doing some client side input validation at the moment since web2py provides some of it for “free" (e.g. if you have an input field that expects a number, you can’t even type any other character, it’s automatically removed) but of course that parsley would allow me to further extend this.

Francisco

[Derek]

Well, I like your idea. Do you think you could post it to web2pyslices?

[Francisco Gama]

thanks!

http://www.web2pyslices.com/slice/show/1983/auto-update-db-records-behaviour-per-input-field-on-release

Cheers,

Francisco

[Francisco Gama]

I just updated this entry because I had some irrelevant code there / code missing to make it easily reproducible. Hopefully, should be fine now..

Francisco