Ajax Form For Editing Multiple Records of a Non-Normalized Database Table

[James McGlynn]

TL;DR

I want to make a form that submits via ajax that allows simultaneous editing of multiple records from the same table. The table is not normalized, so for fields that contain the same information between different records, I only want to show those to the user in one place.

What are my options?

1. Normalize the database and go from there. 
   
1. If I do this I'll have to rewrite some axon code that already uses the table in its current form. 
2. What is the general method for generating a form containing a parent record and multiple child records, and in my case, records for children of children?
2. Have multiple forms per page. 
   
1. I tried this way and had multiple forms for the same point, with different fields showing in each form.
2. Its also possible to show all fields for one of the records and hide them for the rest. Is this better?
3. Create a single form.
   
1. I also tried this method. To deal with multiple records I made the name attributes of the fields unique when creating the form. Doing this means they don't match the database field names. When submitting I parse out the unique part of the fieldname. 
2. I can't figure out how to handle form and db level validation with this method. 

Even without reading the rest of the post, I would be happy with an answer that just points me in the general direction based on the above. But I do have a lot more info below on my attempts so far if you want to look at it. 

Thank you. 

---------------------------------

I am working with a software called Skyspark that deals with timeseries data. In Skyspark you can write 'axon' code to make calculated points, where you take existing timeseries data streams and make new data streams. 

In order to not have to write code to make a new calculation, I wrote some axon code to look at a database table and perform the calculation dictated by the database table. 

I'm using web2py as the application for interfacing with the database table. 

My database is not normalized (may be part of the problem) and is set up as follows. 

db.define_table('calculated_meters',
 ## Info about a data stream that is part of a calculation
 Field('point_ref'), ## Skyspark qualified point reference or the name of intermediate result to be used in subsequent step
 Field('point_multiplier'),
 Field('point_adder'),
 Field('point_sign'),
 Field('point_power'),
 
 ## A calculation can have multiple steps. Below are the fields that describe a step of a calculation
 Field('calculation_operation'), ## sum, avg, standardDeviation, etc.
 Field('calculation_order'), ## 1,2,3,4,etc
 Field('result_name'), ## results of previous steps can be used in subsequent steps of the calculation. Specify the name of a result here.

 
 ## Data streams involved in a calculation are grouped by the destination point in Skyspark
 Field('result_frequency'), ## Timeseries frequency of the result
 Field('result_point_ref'), ## what point does the final result get written to
 Field('notes'), ## Notes about the whole calculation.
 )

db.calculated_meters.point_multiplier.default=1

db.calculated_meters.point_adder.default=0

db.calculated_meters.point_sign.default=1

db.calculated_meters.point_power.default=1

db.calculated_meters.calculation_operation.default="sum"

db.calculated_meters.calculation_order.default=0

db.calculated_meters.result_frequency.default="15min"

## Some example requirements

db.calculated_meters.point_sign.requires = IS_IN_SET(["1","-1"])

db.calculated_meters.calculation_operation.requires=IS_IN_SET(['sum','avg'])v

For all of the points in a given calculation, the result_frequency, result_point_ref, and the notes will all be the same.

For all the points in a given step of a calculation, the calculation_operation, calculation_order, and result name will be the same.

So my question is, what is the best way to show a form to the user that allows editing of all of the points in a given calculation at the same time, but doesn't contain multiple fields that will end up being the same value?

My first attempt was to have multiple forms on a page, some forms for the same records, just displaying different fields. On forms that affected multiple records, I would add some logic to update multiple records if the form was successfully submitted. The code for that attempt is below. I ended up leaving this method in lieu of generating a single form because it was taking me too long to figure how to use ajax with multiple forms (this is my first project in a while). 

def calculated_meters():

    myquery = (db.calculated_meters.result_point_ref == "point5")
    myset = db(myquery)
    rows = myset.select()

    ## Start the form grid
    form_list=[]

    ## Create a form to hold the global calc parameters
    calculation_form=SQLFORM(db.calculated_meters,
                                record=rows[0]['id'],
                                fields=['result_point_ref', 'result_frequency', 'notes'],
                                formstyle='bootstrap3_inline'
                                )

    ## Give the form a name to allow for multiple forms on one page
    calculation_form.process(formname='calculation_form')

    ## Append to form_list
    form_list.append(calculation_form)


    ## The calculation form affects multiple records so I update all affected records when the form is accepted.
    if calculation_form.accepted:
        print("main form updated")
        print(calculation_form)
        for row in rows:
            row.result_frequency=calculation_form.vars.result_frequency
            row.result_point_ref=calculation_form.vars.result_point_ref
            row.notes=calculation_form.vars.notes
            row.update_record()

    ## Get unique list of steps
    calc_steps=[]
    for row in rows:
        calc_steps.append(int(row.calculation_order))
    calc_steps=set(calc_steps)

    ## Loop through the steps
    for calc_step in calc_steps:

        ## Get records associated with the step of this calculation
        calc_step_records=rows.find(lambda row: int(row.calculation_order)==calc_step)

        ## Pick the first record and make a form for the step of the calculation
        calc_step_form=SQLFORM(db.calculated_meters,
                                        record = calc_step_records[0],
                                        fields = ['calculation_order', 'calculation_operation', 'result_name'],
                                        )

        ## Give the form a name and add it to the list
        calc_step_form.process(formname='calc_step_'+str(calc_step))
        form_list.append(calc_step_form)


        #Create forms that are specific to a record    
        for row in calc_step_records:
   
            form=SQLFORM(db.calculated_meters,
                        record=row['id'],
                        fields=['point_ref','point_multiplier','point_adder','point_sign','point_power',],
                        show_id=False,
                        deletable=True,
                        formstyle='bootstrap3_inline',
                        )
            ## name and add to list
            form.process(formname='form' + str(row['id']) )
            form_list.append(form)

    ## Return the form list
    return dict(form_list=form_list)

My next attempt as said above was to create custom form using FORM. When building the form, because I have multiple records in the same form, to name the fields for serialization, I'm using a syntax like "fieldname__stepindex__pointindex" e.g. "fieldname__0__1. Then I pre-populate the known fields and send the form out. I use jQuery to serialize the form and submit with ajax, and then process the form myself. The problems I'm running into right now are that I can't figure out how to handle validation at either the form or database level. At the moment, the database will accept any value or empty field during form submission. Also, IS_IN_SET() requirements at the form level obviously don't produce a dropdown for that field.  The code for this attempt is below.

 Generating the form

def create_form(result_point_ref):
    import json

    field_mapping_dod=dict(
    calc=[
        dict( name='result_point_ref',
              classes='col-md-3', ),
        dict( name='result_frequency',
              classes='col-md-3', ),
        dict( name='notes',
              classes='col-md-6', ),
        ],
    step=[
        dict( name='calculation_order',
              classes='col-md-3',
              requires=IS_IN_SET([
                    'sum',
                    'avg',]) ),
        dict( name='calculation_operation',
              classes='col-md-3',
              requires=IS_NOT_EMPTY(), ),


        dict( name='result_name',
              classes='col-md-3',
              requires=IS_NOT_EMPTY(), ),
        ],
    point=[
        dict( name='id',
              classes='col-md-1',
              readonly=True, ),
        dict( name='point_ref',
              classes='col-md-3',
              readonly=False, ),
        dict( name='point_multiplier',
              classes='col-md-1',
              readonly=False, ),
        dict( name='point_adder',
              classes='col-md-1',
              readonly=False, ),
        dict( name='point_sign',
              classes='col-md-1',
              readonly=False, ),
        dict( name='point_power',
              classes='col-md-1',
              readonly=False, ),
        ])


    ## Query the databased for all the associated points
    myquery = (db.calculated_meters.result_point_ref == result_point_ref)
    myset = db(myquery)
    rows = myset.select()

    ## From the associated records, figure out how many unique steps there are
    calc_steps=[]
    for row in rows:
        calc_steps.append(int(row.calculation_order))
    calc_steps=list(set(calc_steps))

    ## Get a representative row for 'calc' fields from above
    calc_data=rows[0] # eventually make this a copy or something.

    ### Create the form
    form=FORM(
        _class='calculation_form',
        _id='calculation_form',
        )

    ## These get appended as direct children of the form element
    form.append( LABEL('CALCULATION FOR POINT: '+str(calc_data.result_point_ref), _class='control-label'), )
    form.append( DIV(_class='calculation',), )# End main calc div which will be form[1]

    ## Create the calc fields and add them to the form
    #calc_field_divs=[]
    for field_name in field_mapping_dod['calc']:

        form[1].append( DIV(
                    DIV(
                        LABEL(field_name['name'], _class='control-label'),
                        INPUT(_name=field_name['name'], _value=calc_data[field_name['name']], _type='text', _class='form-control', requires=IS_NOT_EMPTY()),
                        _class='d-flex flex-column',),
                    _class='form-group'+' '+str(field_name['classes'])), )

    ## Add a div to hold the calculation steps this will be form[1][-1]
    form[1].append( DIV( _class='calculation_steps d-inline-block',) )

    ## Add a div to contain the calculation steps
    for j in range(len(calc_steps)):
        calc_step_records=rows.find(lambda row: int(row.calculation_order)==calc_steps[j])
        step_data=calc_step_records[0]
        form[1][-1].append(  DIV(
                                LABEL('STEP '+str(j+1)+': '),
                                DIV(_class='calculation_step'),
                                _class='calculator_step_container' ), )

        for field_name in field_mapping_dod['step']:
            ## Put the step info into the div this is the calc order and result name and frequency and stuff
            form[1][-1][-1][-1].append(
                            DIV(    
                                DIV(
                                    LABEL(field_name['name'],_class='control-label'),
                                    INPUT(
                                        _name=field_name['name']+'__'+str(j),
                                        _value=step_data[field_name['name']],
                                        _type='text',
                                        _class='form-control',
                                        requires=IS_EMPTY_OR(IS_IN_SET(
                                            [(1,'Method1'), (2,'Method2'), (3,'Method3')], zero='Select'))
                                        ),
                                    _class='d-flex flex-column',),
                                _class='form-group'+' '+field_name['classes']),
                                )

        ## Once the calc info is in, add the points
        for i in range(len(calc_step_records)):
            ## Add a place to put the points the place to tput the points will now be 4 -1s deep
            form[1][-1][-1][-1].append(DIV(_class='d-inline-block calculation_step_point_container'))
            form[1][-1][-1][-1][-1].append( LABEL('POINT '+str(i+1)+': '), )

            ## Start div for a point
            form[1][-1][-1][-1][-1].append( DIV( _class='calculation_step_point',), )

            for field_name in field_mapping_dod['point']:
                form[1][-1][-1][-1][-1][-1].append(
                            DIV(
                                DIV(
                                    LABEL(field_name['name'], _class='control-label'),
                                    INPUT( _name=field_name['name']+'__'+str(j)+'__'+str(i),
                                           _value=calc_step_records[i][field_name['name']],
                                           _type='text',
                                           _class='form-control',
                                           _readonly=field_name['readonly']),
                                    _class='d-flex flex-column',),
                                _class='form-group'+' '+field_name['classes']), )

            ## Add a delete button to the end of the point
            form[1][-1][-1][-1][-1][-1].append(
                                DIV(
                                    DIV(
                                        DIV('DEL',_point_id=int(calc_step_records[i].id), _type='text', _class='remove-point-btn btn'),
                                        _class='d-flex flex-column',),
                                    _class='form-group col-md-1'),
                                )

        ## Add an add point button to the end of a step
        form[-1][-1][-1][-1].append(DIV(DIV('Add Point',_class='add-point-btn btn', _step=str(calc_step_records[0].calculation_order), _point=str(i+1)),_class='d-block'))    

    ## Add an add step button to the end of the calc
    form[-1][-1].append(DIV(DIV('Add Step',_class='add-step-btn btn d-block',_step=str(int(calc_step_records[0].calculation_order)+1))))

    ## submit button
    form[-1].append(DIV(BUTTON('Submit',_class='submit-whole-form btn d-block')))

    return form

The jQuery function to submit the form. This is inside the field calculation_form.html, which just displays the form and then has this script tag below it. 

<script type="text/javascript">
   
$(function () {

    $("#form_container").on("submit","#calculation_form", function (e) {

        var url = "ajax_form_submit";

        $.ajax({
               type: "POST",
               url: url,
               data: $("#calculation_form").serialize(),
               dataType : 'json',
               //async:false,
               success: function(data)
               {
                   $('#calculation_form').replaceWith(data)
               }
             });
        e.preventDefault();
    });
});

</script>

Submitting the form. This function takes the serialized form data and converts it into dictionaries with keys matching the database field names and then submits the records 1 at a time. 

def ajax_form_submit():

    import json

    ## Start an empty list to store dictionaries in
    point_list_of_dicts=[]

    ## Cycle through all the fields
    for key in request.vars.iterkeys():

        ## Figure out how many points you are dealing with and populate the calc and step info
        if key.find("id__")!=-1:

            ## This is the syntax of the name attribute of each field __ separates the name, the calc step, and the point number
            key_split=key.split("__")
            point_list_of_dicts.append(dict(point_no=key_split[2], calc_no=key_split[1], record_info=dict()))

    ## Now go through each field and put it in the appropriate container
    for key, value in request.vars.iteritems():

        key_split=key.split("__")

        ## If the length of the split is one, it goes in all points
        if len(key_split)==1:
            for p in point_list_of_dicts:
                p['record_info'][key.strip()]=value.strip()

        ## If the length of the split is 2, the info goes in all points with the same calc step.
        elif len(key_split)==2:
            for p in point_list_of_dicts:
                if p['calc_no']==key_split[1]:
                    p['record_info'][key_split[0].strip()]=value.strip()


        ## If the length of the split is 3, the info is point specific.
        elif len(key_split)==3:
            for p in point_list_of_dicts:
                if p['calc_no']==key_split[1] and p['point_no']==key_split[2]:
                    p['record_info'][key_split[0].strip()]=value.strip()

    print (point_list_of_dicts)


    ## Finally, for each record we have constructed from the form, submit or create as necessary.
    for item in point_list_of_dicts:

        ## Get the record ID
        point_id=item['record_info']['id']
       
        ## If the ID is TBD, that means you have to create the record
        if point_id=="TBD":
            db.calculated_meters.insert(**db.calculated_meters._filter_fields(item['record_info']))
       
        ## Otherwise try to create a new record.
        else:
            db(db['calculated_meters']._id == int(point_id)).update(**db.calculated_meters._filter_fields(item['record_info']))


    form = create_form(request.vars['result_point_ref'])
    form=json.dumps(str(form))

    ## Give some user feedback
    response.flash="DB update successful!"  

    ## Build the form again from scratch and return the entire form
    return form

Thank you for you time.