Could I use SQLFORM.factory to update 2 tables in a single form

[Rudy]

Hi there,

I am building an accounting system. When I create a quotation, if the item is in 'subscription' category, i want to use SQLFORM.factory(db.item, db.subscription_item) to generate a create form for submitting the necessary values. Now i want to edit these 2 tables(item and subscription_item have 1-1 relationship), does SQLFORM.factory() takes any constraint or query to specify which item I want to edit?  If not, what's the best way to do it? Thanks in advance!

Below is the simplified database tables:

db.define_table('company',

                Field('company_name', requires=IS_NOT_EMPTY()), # unique=True

                format='%(company_name)s')

db.define_table('quotation',

                Field('company', 'reference company'),

                Field('project_name', requires=IS_NOT_EMPTY()),

                Field('quote_amount', 'double', default=0, writable=False),

                auth.signature)

db.define_table('category',

                Field('category_name', ondelete='NO ACTION'), # eg. project or subscription

                format='%(category_name)s')

db.define_table('item',

                Field('quotation', 'reference quotation',  writable=False, label='Quote Id'),

                Field('category', 'reference category', requires=IS_IN_DB(db, 'category.id', '%(category_name)s')),

                Field('description'),

                Field('amount', 'double', default=0),

                auth.signature)

db.define_table('subscription_item',

                Field('item', 'reference item',  writable=False, label='Item Id'),

                Field('start_date', 'date'),

                Field('end_date', 'date'),

                auth.signature)

[Massimo Di Pierro]

You can do form = SQLFORM.factory(db.item, db.subscription_item) as long the two tables do not have fields with the same name. Then after

if form.process().accepted:

    db.item.insert(....)
    db.subscription_item.insert(....)

where ... should be replaced by the proper fields in form.vars

[Rudy]

Hi Massimo,

Thanks for your help, i indeed used db.item.insert(**db.item._filter_fields(form.vars)) and db.subscription_item.insert(**db.subscription_item._filter_fields(form.vars)) to generate a CREATE FORM in a separate action. Now I want to create another action with an UPDATE FORM for user to modify the values, with a single table, I can use item_form=SQLFORM(db.item, item_row).process(), but with 2 tables in the same form using SQLFORM.factory, can SQLFORM.factory() take the item_row and subscription_item_row to populate the values in the UPDATE FORM from database? I tried, but didn't work. What's the best way to create an UPDATE FORM for 2 tables?

assumption above..... subscription_item_row=db(db.subscription_item.item==item.id).select().first()

[Rudy]

Hi Massimo,

I tried below, it could populate database values into the UPDATE FORM and updated the 2 tables correctly, not sure if it's the best way to do it. Any comment is appreciated, thanks again!

    item_id = request.args(0)

    item_row = db.item(item_id)

    quote_row = db.quotation(item_row.quotation)

    

    subscription_row=db(db.subscription_item.item==item_id).select().first()

    item_form = SQLFORM.factory(

                                    Field('category', default=item_row.category),

                                    Field('description', default=item_row.description),

                                    Field('amount', 'double', default=item_row.amount),

                                    Field('start_date', 'date', default=subscription_row.start_date),

                                    Field('renew_date', 'date', default=subscription_row.renew_date),

                                   ).process()

    if item_form.accepted:

            item_row.update_record(category=item_form.vars.category)

            item_row.update_record(description=item_form.vars.description)

            item_row.update_record(amount=item_form.vars.amount)

            subscription_row.update_record(start_date=item_form.vars.start_date)

            subscription_row.update_record(renew_date=item_form.vars.renew_date)