Looking for a way to combine auto-complete with adding a non-existing item.

[Remco K]

Hello everyone,

I'm looking for a way to get an auto-complete field combined with adding non-existing item to the DB. I've seen a website which
uses this functionality (demo: http://www.tellmehow.nl/video.html )

I have already tried SELECT_OR_ADD_OPTION but i don't like drop-downs...

Thanks in advance!
Remco

[Anthony]

Are you trying to auto-complete a reference field, and you want to be able to add new records to the referenced table? The built-in autocomplete widget doesn't handle that out of the box, but upon submission, you could check the request variables, and if the submitted value doesn't belong to an existing record in the referenced table, you could do an insert:

Model:

db.define_table('category', Field(name))

db.define_table('article',
    Field('title'),
    Field('body', 'text'),
    Field('category', db.category,
          widget=SQLFORM.widgets.autocomplete(request, db.category.name, id_field=db.category.id)))

Controller:

def myfunction():
    if request.post_vars._autocomplete_name_aux and not request.post_vars.category:
        request.post_vars.category = db.category.insert(name=request.post_vars._autocomplete_name_aux)
    return dict(form=SQLFORM(db.article).process())

When you use the autocomplete widget on a reference field, it submits the display value in a special field named _autocomplete_[display_field_name]_aux, and the actual field value is the record ID of the referenced record associated with the display value. However, if you enter and submit a display value that does not have an associated record in the referenced table, the reference field value will simply be empty (though the _autocomplete_..._aux field will contain the submitted display value).

For example, when submitting a new article, suppose you enter "programming" in the category input field, but "programming" does not yet exist in the db.category table. In that case, request.post_vars.category will be empty, and request.post_vars._autocomplete_name_aux will contain the word "programming". In that case, the above code inserts "programming" into the db.category table and stores the new record ID in request.post_vars.category. When the form is then processed, the new db.article record will be created, with the new db.category record ID stored in the db.article.category field.

Perhaps the autocomplete widget should handle this automatically (with an optional setting).

Anthony

[Remco K]

Thank you very much, Anthony! This was exactly what i was looking for. Problem solved.

Op vrijdag 11 mei 2012 19:07:32 UTC+2 schreef Anthony het volgende:

Op vrijdag 11 mei 2012 19:07:32 UTC+2 schreef Anthony het volgende:

[Richard Vézina]

I merge autocomplete with SELECT_OR_ADD_OPTION and name it : AutocompleteWidgetSelectOrAddOption

It available here at the bottom of the thread :

https://groups.google.com/forum/?fromgroups#!topic/web2py/9KamKgHKUwU

Or I will create a web2py slice soon, I hope...

Richard

[Remco K]

That would be nice. I think there are more people who can use this.

2012/5/14 Richard Vézina <ml.richa...@gmail.com>

--
Met vriendelijke groet,

Remco Klappe

[Richard Vézina]

I want to improve it before because I don't like the way the Select _OR_ADD_Otion works... It changes the select and options class name and it prevent the use of other jQuery plugin with the autocomplete widget... For example, I want to use my AutocompleteWidgetSelectOrAddOption and asmSelect or bsmSelect (http://www.ryancramer.com/journal/entries/asmselect_v104/ or https://github.com/vicb/bsmSelect) but I just can't the way all those plugins are implemented...

Also, autocomplete widget I used is maybe not the last version of it, I heard that there is a newer version of it, I implement the AutocompleteWidgetSelectOrAddOption base on the autocomplete widget available in web2py 1.99.4...

Richard

[Bill Thayer]

If this works it will be very usefull!!!!

Is there a way to modify myfunction so that it can be passed to onvalidate or oncreate fields of SQLFORM.grid?

This is as close as I got but stiil get form validation error (value not in database) on the referenced field: add_wiki() is my myfunction()

def add_wiki():
    table=db[request.args(0)]
    form=SQLFORM(table)
    if request.post_vars._autocomplete_name_aux and not request.post_vars.wiki_page_id:
        request.post_vars.wiki_page_id=db.wiki_page.insert(slug=IS_SLUG()(request.post_vars._autocomplete_name_aux))
    return form.process()

def manage():
    table=db[request.args(0)]
    table.id.readable = table.id.writable = False
    left=None
    oncreate=None
    if table.wiki_page_id:
        left=db.wiki_page.on(db.wiki_page.id==table.wiki_page_id)
        oncreate=add_wiki()
       
    content=SQLFORM.grid(
        table,left=left,
        details=False,editable=True,deletable=False,create=True,
        oncreate=oncreate,
        args=request.args,
        user_signature=False)
       
    return dict(content=content)

Thank you. The SELECT_OR_ADD_OPTION never did work for me plus this will be a more user friendly option!

Regards,
Bill

[Bill Thayer]

I still haven't gotten this to work but do need this functionality desperatley. Anyone else have any luck?

[Richard Vézina]

Hello Bill,

SELECT OR ADD OPTION is begining to be old, I have been look at this last week and there is conflict between Bootstrap style and jQueyr UI style so the add botton beside the field is not working if boostrap is used in your form.

I the coming weeks I will try to create a new autocomplete_select_or_add_option widget base on Bootstrap Typeahead... I play a bit with BS Typeahead last week and I found it pretty nice, but the API is not well documented, so I had to google a lot.

Richard

On Sun, Nov 4, 2012 at 8:51 PM, Bill Thayer <bill....@live.com> wrote:

I still haven't gotten this to work but do need this functionality desperatley. Anyone else have any luck?

--
 
 
 

[Bill Thayer]

Thank youRichard. I've have exhausted everything I could think of to get it to work.

[Richard Vézina]

I never try with SQLFORM.grid, maybe the form is a bit different than CRUD or SQLFORM one...

Did you try the dummy app that I attach?

Richard

On Mon, Nov 5, 2012 at 11:44 AM, Bill Thayer <bill....@live.com> wrote:

Thank youRichard. I've have exhausted everything I could think of to get it to work.

--
 
 
 

[Richard Vézina]

sorry, I thought I had attach a example app, but I didn't...

Richard

[Richard Vézina]

Dummy app attach here !!!

Look at a_widget.py in model, db.py, the end of default.py controller and the create_update.html (I think I didn't make much change there).

I include jQuery UI 1.9.1 in /static/plugin_added/THERE

I use in the create_update() function.

NOTICE : The autocomplete class altered for create the AutocompleteWidgetSelectOrAddOption widget (that is a combination of SELECT_OR_ADD_OPTION slice and AutocompleteWidget) is old, the autocomplete class used is the one of web2py 1.99.4 I think. But it still work properly under 2.2.1

The only glitch is that there is no jQuery botton, because of a conflict with Bootstrap (didn't have time to investigate).

Hope you will figure out how to make it works now.

Richard

[Bill Thayer]

Now that is impressive. I did get it to work but not with SQLFORM or SQLFORM.smartgrid so that was a large part of my problem. 

When renderred in the view created by SQLGRID (clicking the ADD+ button) I would get the SQLFORM and the person fields were displayed with it's own submit button. This looked like it could be a bonus however the Add New link did not work (doesn't really need to if the form is already rendered) and information was not saved to the database. Confirmed this by visiting the grid again. I could see all of the users you added before.

Looks like I'll drop use of SQLFORM in favor of this widget.Thank you for the sample app. It made a big difference.

Regards,

Bill

[Bill Thayer]

This widget wouldn't break appadmin? would it? These are the many unknown things that really frustrate me about web2py.

I edited the models file to test my current troubles adding columns to tables that are already created. With your sample app, I was able to add the new table and then add columns to cats. However when i edited the cat to add a color or collar those new values were not saved.

db.define_table('collars',

                Field('material', requires=IS_IN_SET(['leather', 'chain'])),

                Field('size', requires=IS_IN_SET(['small', 'med', 'large'])),

                format='%(size)s %(material)s')

db.define_table('cat',

    Field('name','string'),

    Field('color', 'string'),

    Field('collar', 'reference collars'),

    Field('person_id', 'reference person', 

        widget=AutocompleteWidgetSelectOrAddOption(request, 

            db.person.first_name, 

            id_field=db.person.id, 

            limitby=(0,10), 

            min_length=2, 

            form_title=T('Add new person'), 

            controller="default", 

            function="add_person", 

            button_text = T('Add new'))

        ),

    format='%()s'

    )

Regards,

Bill

[Richard Vézina]

I don't think appadmin is taking care of widget...

I don't follow you, are you talking that you add a column and the old entry are NULL? 

--
 
 
 

[Bill Thayer]

Hi Richard,

In short...for myself and future searches...This widget works in a straight up crud form but with SQLFORM it has different behavior. Whether or not it is the source of appadmin not storing values is yet to be determined.

After my columns were successfully added, values were set to null automatically of course. When I tried using appadmin to change the values (note this has nothing to do with the widget except the widget happens to be rendered on the edit or create page) the values do not update. Not saying it's the widget, I just don't know.

Regards,

Bill

[Richard Vézina]

ok, yes I think there is a issue... In my app it working, but in the dummy app it's not don't know why...

Maybe with SQLFORM you need to set the formstyle to divs.

Richard

--
 
 
 

[Bill Thayer]

Gave it a try...no luck.

[Richard Vézina]

I just try with SQLFORM and it works

form = SQLFORM(db[request.args(0)], request.args(1))

Richard

On Tue, Nov 6, 2012 at 11:27 AM, Bill Thayer <bill....@live.com> wrote:

Gave it a try...no luck.

--