[ic] Re: select widget with lookup_select and sql

Peter peter at pajamian.dhs.org
Wed Mar 21 17:44:21 EST 2007


On 03/21/2007 01:10 PM, Kevin Walsh wrote:
> "Aaron Berg" <ir.gath at gmail.com> wrote:
>> This is now solved with some help from Mike Heins.
>> Mike gave me this solution:
>>
>> [table-editor
>> 	widget.room_id=select
>> 	lookup_query.room_id=`
>> 				$Scratch->{listing_item_id} =~ s/\D+//g;
>> 				return qq{
>> 			    SELECT room_id, room_name
>> 			    FROM listing_rooms
>> 			    WHERE listing_id = $Scratch->{listing_item_id}
>> 				};
>> 			`
>> 	no_top=1
>> ]
> 
>     [pragma interpolate_itl_references 1]
> 
>     [table-editor
>         no_top=1
>         widget.room_id=select
>         lookup_query.room_id=|
>             SELECT  room_id, room_name
>             FROM    listing_rooms
>             WHERE   listing_id = [scratch name=listing_item_id filter=digits]
>     |]


Note that neither of the above will work if your listing IDs contain
alpha (non numeric) chars in them.  In that case you will want to do
this instead:

WHERE listing_id = [scratch name=listing_item_id filter=dbi_quote]

Also note that all of the above will alter your item_id scratch, if you
don't want it altered do:

WHERE listing_id = [scratch name=listing_item_id filter=dbi_quote keep=1]

For more info, see:
<http://www.interchange.rtfm.info/icdocs/pragma/interpolate_itl_references.html>
<http://www.interchange.rtfm.info/icdocs/tags/scratch.html>
<http://www.interchange.rtfm.info/icdocs/filters/dbi_quote.html>
<http://www.interchange.rtfm.info/icdocs/filters/digits.html>

Peter


More information about the interchange-users mailing list