If you benefit from web2py hope you feel encouraged to pay it forward by contributing back to society in whatever form you choose!

Setting up

Let's create a model with interdependent values and populate it with sample data.

db.define_table('state',
                Field('name'),format='%(name)s')
db.define_table('city',
                Field('name'),
                Field('state',db.state),format='%(name)s')
db.define_table('zipcode',
                Field('value'),
                Field('city',db.city),format='%(value)s')
db.define_table('areacode',
                Field('value'),
                Field('zipcode',db.zipcode),format='%(value)s')

db.define_table('phone',
                Field('areacode',db.areacode),
                Field('number'))

if db(db.state.id>0).count() == 0:
    db.state.truncate()
    db.city.truncate()
    db.zipcode.truncate()
    db.areacode.truncate()
    db.state.insert(name='Texas')
    db.state.insert(name='Illinois')
    db.state.insert(name='California')

    db.city.insert(name='Austin',state=1)
    db.city.insert(name='Dallas',state=1)
    db.city.insert(name='Chicago',state=2)
    db.city.insert(name='Aurora',state=2)
    db.city.insert(name='Los Angeles',state=3)
    db.city.insert(name='San Diego',state=3)

    db.zipcode.insert(value='78704',city=1)
    db.zipcode.insert(value='78745',city=1)
    db.zipcode.insert(value='75001',city=2)
    db.zipcode.insert(value='75038',city=2)
    db.zipcode.insert(value='60606',city=3)
    db.zipcode.insert(value='60607',city=3)
    db.zipcode.insert(value='60504',city=4)
    db.zipcode.insert(value='60505',city=4)
    db.zipcode.insert(value='90005',city=5)
    db.zipcode.insert(value='90006',city=5)
    db.zipcode.insert(value='92101',city=6)
    db.zipcode.insert(value='92102',city=6)

    db.areacode.insert(value='512',zipcode=1)
    db.areacode.insert(value='511',zipcode=1)
    db.areacode.insert(value='345',zipcode=2)
    db.areacode.insert(value='456',zipcode=2)
    db.areacode.insert(value='567',zipcode=3)
    db.areacode.insert(value='678',zipcode=3)
    db.areacode.insert(value='789',zipcode=4)
    db.areacode.insert(value='890',zipcode=4)
    db.areacode.insert(value='901',zipcode=5)
    db.areacode.insert(value='321',zipcode=5)
    db.areacode.insert(value='432',zipcode=6)
    db.areacode.insert(value='534',zipcode=6)
    db.areacode.insert(value='645',zipcode=7)
    db.areacode.insert(value='765',zipcode=7)
    db.areacode.insert(value='876',zipcode=8)
    db.areacode.insert(value='987',zipcode=8)
    db.areacode.insert(value='141',zipcode=9)
    db.areacode.insert(value='252',zipcode=9)
    db.areacode.insert(value='363',zipcode=10)
    db.areacode.insert(value='474',zipcode=10)
    db.areacode.insert(value='585',zipcode=11)
    db.areacode.insert(value='686',zipcode=11)
    db.areacode.insert(value='797',zipcode=12)
    db.areacode.insert(value='898',zipcode=12)

Notice that areacode depends on zipcode which depends on city which depends on state.
Now let's add the widget to our model

class CascadingSelect(object):
    def __init__(self, *tables):
        self.tables = tables 
        self.prompt = lambda table:str(table)   
    def widget(self,f,v):
        import uuid
        uid = str(uuid.uuid4())[:8]
        d_id = "cascade-" + uid
        wrapper = TABLE(_id=d_id)
        parent = None; parent_format = None; 
        fn =  '' 
        vr = 'var dd%s = [];var oi%s = [];\n' % (uid,uid)
        prompt = [self.prompt(table) for table in self.tables]
        vr += 'var pr%s = ["' % uid + '","'.join([str(p) for p in prompt]) + '"];\n' 
        f_inp = SQLFORM.widgets.string.widget(f,v)
        f_id = f_inp['_id']
        f_inp['_type'] = "hidden"
        for tc, table in enumerate(self.tables):             
            db = table._db     
            format = table._format            
            options = db(table['id']>0).select()
            id = str(table) + '_' + format[2:-2]             
            opts = [OPTION(format % opt,_value=opt.id,
                                 _parent=opt[str(parent)] if parent else '0') \
                                  for opt in options]
            opts.insert(0, OPTION(prompt[tc],_value=0))
            inp = SELECT(opts ,_parent=str(parent) + \
                                  "_" + str(parent_format),
                                  _id=id,_name=id,
                                  _disabled="disabled" if parent else None)
            wrapper.append(TR(inp))
            next = str(tc + 1)
            vr += 'var p%s = jQuery("#%s #%s"); dd%s.push(p%s);\n' % (tc,d_id,id,uid,tc)            
            vr += 'var i%s = jQuery("option",p%s).clone(); oi%s.push(i%s);\n' % (tc,tc,uid,tc)
            fn_in = 'for (i=%s;i<%s;i+=1){dd%s[i].find("option").remove();'\
                    'dd%s[i].append(\'<option value="0">\' + pr%s[i] + \'</option>\');'\
                    'dd%s[i].attr("disabled","disabled");}\n' % \
                           (next,len(self.tables),uid,uid,uid,uid)
            fn_in +='oi%s[%s].each(function(i){'\
                    'if (jQuery(this).attr("parent") == dd%s[%s].val()){'\
                    'dd%s[%s].append(this);}});' % (uid,next,uid,tc,uid,next)            
            fn_in += 'dd%s[%s].removeAttr("disabled");\n' % (uid,next)
            fn_in += 'jQuery("#%s").val("");' % f_id
            if (tc < len(self.tables)-1):
                fn += 'dd%s[%s].change(function(){%s});\n' % (uid,tc,fn_in) 
            else:
                fn_in = 'jQuery("#%s").val(jQuery(this).val());' % f_id
                fn += 'dd%s[%s].change(function(){%s});\n' % (uid,tc,fn_in)
                if v:
                    fn += 'dd%s[%s].val(%s);' % (uid,tc,v)                       
            parent = table
            parent_format = format[2:-2]

        wrapper.append(f_inp)
        wrapper.append(SCRIPT(vr,fn))
        return wrapper

Using the widget

Let's instantiate the widget with the dependent fields in order from least to most specific.

cascade = CascadingSelect(db.state,db.city,db.zipcode,db.areacode)

You can customize the prompt per field. Here I will account for using the proper article if the field starts with a vowel.

cascade.prompt = lambda table: "Pick "  + ("an " if str(table)[0] in 'aeiou' else "a ") + str(table)

Now apply it to our area code reference field in the phone table

db.phone.areacode.widget = cascade.widget

Test it out in a controller

def index():
    form = SQLFORM(db.phone)  
    if form.accepts(request.vars,session):
        response.flash = "Got it"
    elif form.errors:
        response.flash = str(form.errors) 
    return dict(form=form)

alt text

There you have it. The widget requires each table to have a single parameter format attribute included in the define_table function. The actual field value is stored in a hidden field and populated via javascript.

Related slices

Comments (11)

  • Login to post



  • 0
    tom 9 years ago
    How can I save the 'zipcode' in DB Phone also ? Here is a demonstration of the db: ------- id.phone zipcode.phone areacode.phone regards

  • 0
    mrfreeze 9 years ago
    Not sure I understand. There's no need to store it in the phone table. You can get to the zipcode of a db.phone record by doing:
    phn = db(db.phone.id>0).select().first()
    zip = phn.areacode.zipcode.value
    

  • 0
    tom 9 years ago
    I want to store it for another project. This is only an example db.define_table('phone', Field('zip',writable=False,readable=False) Field('areacode',db.areacode), def index(): form = SQLFORM(db.phone) if form.accepts(request.vars,session, dbio=False): form.vars.zip= ?? zip.areacode ?? form.vars.id = db.phone.insert(**dict(form.vars)) response.flash = 'record inserted' .... How can I do it ? thanks

  • 0
    mrfreeze 9 years ago
    Everything you need should be in request.vars

  • 0
    tom 9 years ago
    when one of the fields needs to be computed from the value of other input fields : Error Message: invalid field names: ['areacode_value', 'zipcode_value']
show more comments

Hosting graciously provided by:
Python Anywhere