This explanation worked. I will mention that the code
db.rooms.property.requires=IS_IN_DB(db(query),"properties.id")
caused the dropdown to display the property id. Changing it to
properties.short_description resulted in a perfect dropdown of only
the properties that the query selected.
I want to take a few bytes to than everybody for their assistance in
this forum.
Ed
On Feb 15, 9:47 am, villas <[email protected]> wrote:
> Not this:
> query = "select id from properties where customer = %d" %
>
> Try something like this instead:
> query = (db.properties.customer==customer_id)
>
> On Feb 15, 2:13 pm, Ed Greenberg <[email protected]> wrote:
>
> > Sorry to be back with a request for clarification.
>
> > My relationship is that a customer has properties and a property has
> > rooms.
>
> > I did this...
> > customer_id = session.customer_id
> > query = "select id from properties where customer = %d" %
> > customer_id
> > db.rooms.property.requires=IS_IN_DB(db(query),"properties.id")
> > form = SQLFORM(db.rooms)
>
> > So while creating the rooms form, I want to limit the properties
> > dropdown to those properties owned by the customer, whose id is in the
> > session.
>
> > My query above produces a list of valid property IDs (when run outside
> > this context). When run with the above code, it generates a sql
> > error:
> > <class 'gluon.contrib.pymysql.err.ProgrammingError'>((1064, u"You have
> > an error in your SQL syntax; check the manual that corresponds to your
> > MySQL server version for the right syntax to use near 'select id from
> > properties where customer = 2 ORDER BY properties.id' at line 1"))
>
> > Pasting that exact sql into msyql works fine.
>
> > First question: was I correct in writing a query to produce a list of
> > IDs? Were other columns needed?
>
> > Second question: am I using the field argument (second arg in the call
> > to IS_IN_DB correctly?
>
> > Third question concerns the generated sql : Why is the same sql
> > failing in web2py that is succeeding in the mysql program. Perhaps I'm
> > only seeing a fragment of what web2py submitted to mysql? How can I
> > see the whole thing?
>
> > Many thanks.
>
> > Ed Greenberg
>
> > On Feb 14, 4:19 pm, Massimo Di Pierro <[email protected]>
> > wrote:
>
> > > def index():
> > > query = ....
> > > db.table.field.requires=IS_IN_DB(db(query),....)
> > > form=SQLFORM(...)
> > > if form.accepts(...)
> > > ...
> > > return dict(form=form)
>
> > > On Feb 14, 3:06 pm, Ed Greenberg <[email protected]> wrote:
>
> > > > When SQLFORM constructs an HTML form with a dropdown of foreign keys,
> > > > I need to limit the records to only those that match a certain where
> > > > clause.
>
> > > > This is a master-detail problem. For instance, when displaying a
> > > > detail form, we only want some of the maser records to be selectable
> > > > (and even visible).
>
> > > > A crud won't work reliably -- it's too generic. Do I have to create my
> > > > own form and populate my dropdown, or can I have SQLFORM do the work
> > > > in some fashion?
>
> > > > Thanks
> > > > Ed Greenberg
