Dropbox based on a none unique column.

[annet]

I have got the following model:

db.define_table('training',
SQLField('trainingnummer', type='integer', length=6, notnull=True,
unique=True),
SQLField('training', length=40, notnull=True, unique=True),
migrate='training.table')


db.define_table('bedrijftraining',
SQLField('bedrijf', db.bedrijf, notnull=True),
SQLField('training', length=40, notnull=True),
migrate='bedrijftraining.table')

db.bedrijftraining.bedrijf.requires=IS_IN_DB(db, 'bedrijf.id', '%
(bedrijfsnaam)s')
db.bedrijftraining.training.requires=IS_IN_DB(db,
'training.training','%(training)s')


In a controller I build a form using form_factory:

form=form_factory(SQLField('plaatsnaam',label='Type een
plaatsnaam',requires=IS_NOT_EMPTY()),
SQLField('training', label='Selecteer een
activiteit',requires=IS_IN_DB(db,'training.training','%(training)s')))


Instead of the IS_IN_DB validator based on the training table I would
like the validator to be based on the bedrijftraining table, to
prevent visitors from entering a training that no bedrijf offers. I
tried this:

training=db().select(db.bedrijftraining.training,distinct=True)
form=form_factory(SQLField('plaatsnaam',label='Type een
plaatsnaam',requires=IS_NOT_EMPTY()),
SQLField('training', label='Selecteer een
activiteit',requires=IS_IN_DB(training,)))


Which results in the following error:

S'Traceback (most recent call last):\n File "/Library/Python/2.5/site-
packages/web2py/gluon/restricted.py", line 62, in restricted\n exec
ccode in environment\n File "/Library/Python/2.5/site-packages/web2py/
applications/b2c/controllers/clubs.py", line 77, in <module>\n File "/
Library/Python/2.5/site-packages/web2py/gluon/globals.py", line 55, in
<lambda>\n self._caller=lambda f: f()\n File "/Library/Python/2.5/
site-packages/web2py/applications/b2c/controllers/clubs.py", line 36,
in byactivity\n SQLField(\'training\', label=\'Selecteer een
activiteit\',requires=IS_IN_DB(training)))\nTypeError: __init__()
takes at least 3 arguments (2 given)\n'


Does one of you how to solve this problem.


Best regards,

Annet

[mdipierro]

I have a hard time understanding because these workds make no sense
to me (what language is it?). M guess is that you want:

form=form_factory(SQLField('plaatsnaam',label='Type
eenplaatsnaam',requires=IS_NOT_EMPTY()),

SQLField('training', label='Selecteer

eenactiviteit',requires=IS_IN_DB(db,'bedrijftraining.training','%
(training)s')))

Massimo

[annet]

Massiomo,

I apologize for not translating the extracts from my code into English
(the words are from the Dutch language).

The problem is that in the bedrijftraining table, which links
companies with forms of training, a training can occur more than once
in this table, whereas in the dropbox the forms of training should
only appear once. That's the problem I want to solve.

Annet.

[DenesL]

Annet, from your original post:

training=db().select(db.bedrijftraining.training,distinct=True)
form=form_factory(SQLField('plaatsnaam',label='Type een
plaatsnaam',requires=IS_NOT_EMPTY()),
SQLField('training', label='Selecteer een
activiteit',requires=IS_IN_DB(training,)))

you can not give an IS_IN_DB validator the options (its parameters are
dbset, field, label and error_message).

You could use an IS_IN_SET validator with a list, but the select in
the first line of the code above returns a SQLRows object not a list;
however the list can be created from it:
thelist=[training[i]['training'] for i in range(len(training))]
(I would change the name of the SQLRows object, too many 'training'
can be confusing).

so now: ...,requires=IS_IN_SET(thelist)...

But this begs the question, how useful is that field if it does not
have the id of the entry in the db?. Only you can answer that.

[annet]

Denes,

Thanks for your reply, it was very helpful.


As to your question, the answer is in my comment to this post:

http://groups.google.com/group/web2py/browse_thread/thread/f24fa2b73cc927f2/e2fad1db1810b75e#e2fad1db1810b75e


I hope you will help me solve the following problem as well. The drop
boxes for training and facility do not contain more than 20 to 30
items, so using drop boxes is OK. However, when it comes to searching
by place, the drop box would contain more than 400 items, therefore, I
would like to use an auto-complete field instead of a drop box. I had
a look at the auto-completion examples in the web2py manual (page
196-198), but they are based on FORM, whereas I use form_factory to
build forms.

The idea is much the same as the one in my post above. I have got a
table address (adres), which contains a column 'city' (plaatsnaam).
The auto-complete list should be based on this column.

At the moment my controller reads like:

def byplace():

form=form_factory(SQLField('plaatsnaam',label='Type een

plaatsnaam',requires=IS_NOT_EMPTY()))
if form.accepts(request.vars,session):
response.message='Geen match op plaatsnaam'
clubs=db((db.bedrijf.id==db.adres.bedrijf)&
(db.adres.plaatsnaam==request.vars.plaatsnaam)
.select
(db.bedrijf.id,db.bedrijf.bedrijfsnaam,db.bedrijf.ranking,db.adres.straatnaam,
\
orderby=db.bedrijf.ranking|db.bedrijf.bedrijfsnaam)
else:
response.message='Formuleer een zoekopdracht'
clubs=[]
return dict(form=form,clubs=clubs)


I do not know much about JavaScript and AJAX, so your help is much
appreciated.


Annet.

[DenesL]

Annet,

to create an auto-complete field in form_factory I would use a widget,
as in:

form=form_factory(SQLField('plaatsnaam',
label='Type eenplaatsnaam',
requires=IS_NOT_EMPTY(),
widget=lambda self, value:TAG[''](
INPUT(_class="string",
_id="plaatsnaam",
_name="plaatsnaam",
_type="text",
_onkeyup="ajax('ctl_that_returns_citylist',
['plaatsnaam'],'citylist')"),
DIV(_id="citylist")
)
))

[annet]

Denes,

Thanks for pointing me in the right direction. This is what I have got
so far:

The following functions:

def ctl_that_returns_plaatsnaamlist():
if not request.vars.plaatsnaam: return ‘’
pattern=request.vars.plaatsnaam.capitalize()+’%’
selected=[row.plaatsnaam for row in db(db.adres.plaatsnaam.like
(pattern)).select(db.adres.plaatsnaam,distinct=True)]
return ‘’.join([DIV(k,_onclick=”$(‘#plaatsnaam’).attr
(‘value’,’%s’)”%k).xml() for k in selected])


def byplace():
response.view=’clubs/view.html’
response.functionname=T('Club locator op plaats')

form=form_factory(SQLField('plaatsnaam',

 label='Type eenplaatsnaam',
requires=IS_NOT_EMPTY(),
widget=lambda self, value:TAG[''](

 INPUT(_class="string",

 _id="plaatsnaam",

 _name="plaatsnaam",

 _type="text",


 _onkeyup="ajax('ctl_that_returns_plaatsnaamlist',
['plaatsnaam'],'plaatsnaamlist')"),
DIV(_id="plaatsnaamlist")

)))
if form.accepts(request.vars,session):
response.message=’Geen match op plaatsnaam’
clubs=db((db.bedrijf.id==db.adres.bedrijf)&

(db.bedrijf.id==db.bedrijfbranche.bedrijf)&\
(db.adres.plaatsnaam==request.vars.plaatsnaam)&
(db.adres.adressoort==2)&\(db.bedrijfbranche.branche==2))\

.select
(db.bedrijf.id,db.bedrijf.bedrijfsnaam,db.bedrijf.ranking,db.adres.straatnaam,
\
orderby=db.bedrijf.ranking|db.bedrijf.bedrijfsnaam)
else:
response.message='Formuleer een zoekopdracht'
clubs=[]
return dict(form=form,clubs=clubs)

The byplace() function resides in a clubs controller, together with
byactivity and byfacility functions which are similar in
functionality. Since I also need the auto-complete field in an event
list controller I wonder where to put the
ctl_that_returns_plaatsnaamlist() function. In my models I have got a
db.py with table definitions and a navigation.py with application and
controller menus, so, is it a good idea to create a .py file in my
models and put the ctl_that_returns_plaatsnaamlist() function in
there?

Furthermore there are two lines of code I do not understand, could you
please explain what their parameters are and what happens when they
are executed.


return ‘’.join([DIV(k,_onclick=”$(‘#plaatsnaam’).attr
(‘value’,’%s’)”%k).xml() for k in selected])

_onkeyup="ajax('ctl_that_returns_plaatsnaamlist',
['plaatsnaam'],'plaatsnaamlist')"),DIV(_id="plaatsnaamlist")



In the web2py manual, Massimo mentions a jQuery auto-complete plugin,
at the jQuery site I found 4 of them, however, I haven't got a clue
whether I am better of using a plugin than using a widget , what do
you think?


Annet.

[DenesL]

Very good Annet, glad to be of help.

> return ‘’.join([DIV(k,_onclick=”$(‘#plaatsnaam’).attr
> (‘value’,’%s’)”%k).xml() for k in selected])

This creates a series of DIVs with the values returned by the ajax
call (these are the options of the autocomplete that you see) and sets
each option onclick event to transfer the value to the autocomplete
element (#plaatsnaam) when clicked.

> _onkeyup="ajax('ctl_that_returns_plaatsnaamlist',
> ['plaatsnaam'],'plaatsnaamlist')"),DIV(_id="plaatsnaamlist")

Sets the onkeyup event of the element to call the ajax function (which
you can find inside web2py_ajax.html). The func arguments are:
1) url of controller function to call,
2) list of arguments to pass,
3) id of the element whose innerHTML gets replaced with the ajax
response (in this case the following DIV).
Note: the ajax func in web2py calls jQuery's ajax which has more
options.

> In the web2py manual, Massimo mentions a jQuery auto-complete plugin,
> at the jQuery site I found 4 of them, however, I haven't got a clue
> whether I am better of using a plugin than using a widget , what do
> you think?

The widget is needed independently of the plugin selected, what varies
is the setup required to get the plugin working. Since there are
several autocomplete plugins available you would have to choose the
one that fits your needs best.
My choice and how-to are in this thread:
http://groups.google.com/group/web2py/browse_thread/thread/e1034df0091b5bfd#

> The byplace() function resides in a clubs controller, together with
> byactivity and byfacility functions which are similar in
> functionality. Since I also need the auto-complete field in an event
> list controller I wonder where to put the
> ctl_that_returns_plaatsnaamlist() function. In my models I have got a
> db.py with table definitions and a navigation.py with application and
> controller menus, so, is it a good idea to create a .py file in my
> models and put the ctl_that_returns_plaatsnaamlist() function in
> there?

The ctl_that_returns_plaatsnaamlist() has to be a function inside a
controller (the name is a bit misleading) as in:
.../application/controller/ctl_that_returns_plaatsnaamlist/...
we are just lucky that the ajax func fills in the blanks somewhere.
You could define a function in a model file and call it from the
ctl_that_returns_plaatsnaamlist to have only one defined, just be
careful with element ids.

[annet]

Denes,

Thanks for your thorough explanation.

In my application I got another unsolved problem, probably you know
how to solve it.

In one of my views I have got a table in which one of the <td></td>
element reads like:

<td>{{=A(club.bedrijf.bedrijfsnaam,_href=URL
(r=request,f='clubdetails',args =[club.bedrijf.id]),target="_blank")}}
</ 
td>


Instead of just opening clubdetails in a new window, I would like to
resize the new window and open it without a menubar. I have the
following script to achieve this:

<script type="text/javascript">

function detailsview() {

    window.open('/', 'mywindow', height=600,
width=400,menubar=false);
}
</script>


I do know how to call this script from an <a></a> element:

<a href="javascript: detailsview();">details</a>

However, I have no idea how to call it from the A helper. I hope you
do.


Best regards,

Annet.

[DenesL]

Annet, you have to pass the url to the javascript function:

<script type="text/javascript">
function detailsview(url) {
window.open(url, 'mywindow', 'height=600,
width=400,menubar=false');}
</script>

<td>{{=A(club.bedrijf.bedrijfsnaam,
_href="javascript:detailsview('%s')"
%URL(r=request,f='clubdetails',args =[club.bedrijf.id]),
target="_blank")}}
</td>

[annet]

Denes,

Thanks, problem solved, and something learned again.

I have just downloaded the pengoworks auto-complete files, and am
trying to figure out from the post you reference above how to get all
this to work properly in case of my plaatsnaam auto-complete field.

Since I do not need an id, just the plaatsnaam will do, I guess the
implementation will be a bit easier than what you describe in your
post on the pengoworks auto-complete.


Best regards,

Annet.

[annet]

Denes,

This is what I got so far,

From your reply on Ma8 8, 11:32 pm:

I put the jquery.autocomplete.js and jquery.autocomplete.css into
their respective folders in the static folder of my application and
added the following links to my modified copy of web2py_ajax.html:

<link href="{{=URL(r=request,c='static',f='css/
jquery.autocomplete.css')}}" rel="stylesheet" type="text/css"
media="screen" charset="utf-8" />
<script src="{{=URL(r=request,c='static',f='scripts/
jquery.autocomplete.js')}}" type="text/javascript"></script>


Then, in the same file I inserted the following line of code into the $
(document).ready(function() {}

$('#city').autocomplete("/b2c/handlers/cityAC",{maxItemsToShow:12});


I changed the form_factory form in my controller to read like:

form=form_factory(SQLField('plaatsnaam',

 label='Type een plaatsnaam',


 requires=IS_NOT_EMPTY(),

 widget=lambda self, value:TAG[''](


 INPUT(_id="plaatsnaam",

 _name="plaatsnaam",
_class=”ac_input”,

 _type="text")

 )))


From your post on May 9, 2:42 pm:

Question 1) in your post you say that when you type something into the
INPUT field that triggers the autocomplete,... How does the
application know it is an autocomplete field, (_class="ac_input" ?)

Question 2) then you continue ... The autocomplete creates an ajax
call to the supplied handler function (in my case /handlers/cityAC the
first parameter in $('#city').autocomplete("/b2c/handlers/cityAC",
{maxItemsToShow:12});) sending it whatever was typed in a parameter
named q. I am using the pengoworks plugin so I guess its q in my
application too?


From your post on May 9, 4:22 pm:

In my controller 'handlers' I have the function cityAC (taken from
your example):

def cityAC()
q=""
if request.vars:
q=request.vars.q
if not q:
return q
rows=db (db.adres.plaatsnaam.like(request.vars.q.captitalize()+'%s%
%'%q))./
select(db.adres.plaatsnaam,distinct=True)
r=""
for row in rows:
r='%s%s'%(r,row.option)
return r


Question 3) In your post on May 8, 11:32 I read the following lines:

You can also have cascaded fields by adding:
...maxItemsToShow: 10, onItemSelect: function(itm) { ... the code to
modify the cascaded fields here ...} } ) ;

I defined the autocompletes and their handlers in the controller,
passing them in the response dict along with a function that will
create the JS code in myajax.html view:

in ctl: return dict{ ACs=ACs, ACfunc=ACfunc, ...}
in myajax view (loaded by mylayout.html):
{{try
ACs
except NameError:
pass
else:}}
{{=XML(ACfunc(ACs,request))}}
{{pass}}

The ACfunc() returns a string with all the jQuery code for each
autocomplete:
$("#field_id").autocomplete(...) ;

But you can just place the JS code directly there using:
$("#{{=field_id}}").autocomplete(...) ;


I haven't got a clue where to put these lines of code in my
application and what their purpose is. I hope you will help me getting
this auto-complete field to work, thanks for your time and effort.

Annet.

[DenesL]

Annet,

> Question 1) in your post you say that when you type something into the
> INPUT field that triggers the autocomplete,... How does the
> application know it is an autocomplete field, (_class="ac_input" ?)

Yes.

> Question 2) then you continue ... The autocomplete creates an ajax
> call to the supplied handler function (in my case /handlers/cityAC the
> first parameter in $('#city').autocomplete("/b2c/handlers/cityAC",
> {maxItemsToShow:12});) sending it whatever was typed in a parameter
> named q. I am using the pengoworks plugin so I guess its q in my
> application too?

Yes.

> Question 3) In your post on May 8, 11:32 I read the following lines:

> ...

> I haven't got a clue where to put these lines of code in my
> application and what their purpose is. I hope you will help me getting
> this auto-complete field to work, thanks for your time and effort.

In my app I needed autocompletes (ACs) and some had cascaded fields.
The javascript to handle it all was getting complex so I coded a
couple of functions that create the jQuery code for my ACs and the
cascades. In your case you don't need any of that, you are hardcoding
the ACs as mentioned:

> But you can just place the JS code directly there using:
> $("#{{=field_id}}").autocomplete(...) ;

So you did an excellent job again, is it working?.

Denes.

[annet]

Denes,

No it isn't. When I expose the function, it doesn't throw an error, so
I haven't got a clue why it isn't working. Here's my code, I hope you
see why it isn't working (Compared to my code above I changed #city to
#plaatsnaam, captitalize() to capitalize() and def cityAC to def
cityAC: but that didn't solve the problem)

In fitwise_ajax.html:

<link href="{{=URL(r=request,c='static',f='css/
jquery.autocomplete.css')}}" rel="stylesheet" type="text/css"
media="screen" charset="utf-8" />
<script src="{{=URL(r=request,c='static',f='scripts/
jquery.autocomplete.js')}}" type="text/javascript"></script>

and in the $(document).ready(function() {}:

$('#plaatsnaam').autocomplete('/b2c/handlers/cityAC',{maxItemsToShow:
12});




The form_factory form in my controller:

form=form_factory(SQLField('plaatsnaam',label='Type een

plaatsnaam',requires=IS_NOT_EMPTY(),\

widget=lambda self, value:TAG[''](
INPUT

(_id='plaatsnaam',_name='plaatsnaam',_class='ac_input',_type='text'))))




The function in my handlers.py:

def cityAC():
q=''
if request.vars:
q=request.vars.q
if not q:
return q

rows=db(db.adres.plaatsnaam.like(request.vars.q.capitalize()+'%s%
%'%q))\
.select(db.adres.plaatsnaam,distinct=True)

r=''
for row in rows:
r='%s%s'%(r,row.option)
return r

Best regards,

Annet.

[DenesL]

Annet,

everything seems fine except the following things in the cityAC
function:

> rows=db(db.adres.plaatsnaam.like(request.vars.q.capitalize()+'%s%
> %'%q))\

rows=db(db.adres.plaatsnaam.like('%s%%'%q.capitalize()))\

so that when q=='city' the like argument will be 'City%'.

> r=''
> for row in rows:
> r='%s%s'%(r,row.option)
> return r

here it is important to keep the '\n' to separate the returned values
and use your own field name:

r='%s%s\n'(%r,row.plaatsnaam)


Best regards,

Denes.

[annet]

Denes,

I adjusted the cityAC function according to your directions. It still
isn't working, but at least the application is issuing a ticket.

When I expose the function and type a plaatsnaam in the auto complete
field, after I type the first letter the drop box displays: 'Internal
error Ticket issued:..' I have not seen this behaviour before,
normally this message appears on a blank page.

The ticket reads like:

(dp1
S'output'
p2
S''
sS'layer'
p3
S'/Library/Python/2.5/site-packages/web2py/applications/b2c/
controllers/handlers.py'
p4
sS'code'
p5
S"def cityAC():\n q=''\n if request.vars:\n
q=request.vars.q\n if not q:\n return q\n rows=db
(db.adres.plaatsnaam.like('%s%%'%q.capitalize()))\\\n .select
(db.adres.plaatsnaam,distinct=True)\n r=''\n for row in rows:
\n r='%s%s\\n'(%r,row.plaatsnaam)\n return r\n\n
\nresponse._vars=response._caller(cityAC)"
p6
sS'traceback'
p7

S'Traceback (most recent call last):\n File "/Library/Python/2.5/site-

packages/web2py/gluon/restricted.py", line 61, in restricted\n
else: ccode=compile(code.replace(\'\\r\\n\',\'\\n\'),layer,\'exec\')
\n File "/Library/Python/2.5/site-packages/web2py/applications/b2c/
controllers/handlers.py", line 11\n r=\'%s%s\\n\'(%r,row.plaatsnaam)
\n ^\nSyntaxError: invalid syntax\n'
p8
s.


My cityAC function reads like:

def cityAC():
q=''
if request.vars:
q=request.vars.q
if not q:
return q

rows=db(db.adres.plaatsnaam.like('%s%%'%q.capitalize()))\
.select(db.adres.plaatsnaam,distinct=True)

r=''
for row in rows:

r='%s%s\n'(%r,row.plaatsnaam)
return r


I guess we have almost got it working, since the problem seems to be
in this last line of code.


Best regards,

Annet.

[DenesL]

My bad, it is a typo:

> r='%s%s\n'(%r,row.plaatsnaam)

r='%s%s\n'%(r,row.plaatsnaam)

[annet]

Denes,

I apologize for my late reply, after correcting the typing error, the
auto-complete is working.

Thanks for being so patient with me.


Best regards,

Annet.