You can but it should be very inefficient.
On Wednesday, 22 August 2012 15:01:44 UTC-5, Omi Chiba wrote:
>
> Like can I query the data from the table and use IS_IN_SET istead of
> IS_IN_DB ?
>
> On Tuesday, August 21, 2012 4:54:51 PM UTC-5, Omi Chiba wrote:
>>
>> Is there any other way I can try ?
>>
>> I can see the data in the both table no problem. It just cannot use
>> IS_IN_DB function for it.
>>
>>
>> On Monday, August 20, 2012 2:33:39 PM UTC-5, Omi Chiba wrote:
>>>
>>> >Is the problem only in appadmin?
>>> No, it happnes anywhere.
>>>
>>> >Can you produce a minimum program with some sample data to reproduce
>>> this?
>>> I tried MySQL, MSSQL and DB2, and found only DB2 has this problem.
>>>
>>> This is how to reproduce the error on db2. This is very critical for my
>>> current project and
>>> I hope we can find the problem or find another way to do the dropdown
>>> which works on db2.
>>>
>>> 1. create table below
>>> models/db_tables.py
>>>
>>> -------------------------------------------------------------------------------------------------
>>>
>>> # coding: utf8
>>>
>>> db.define_table('dog',
>>> Field('dogid'),
>>> Field('name'),
>>> format='%(name)s',
>>> primarykey=['dogid'])
>>>
>>> db.define_table('cat',
>>> Field('catid'),
>>> Field('name'),
>>> format='%(name)s',
>>> primarykey=['catid'])
>>>
>>> db.define_table('person',
>>> Field('personid'),
>>> Field('name'),
>>> Field('mydog'),
>>> Field('mycat'),
>>> format='%(name)s',
>>> primarykey=['personid'])
>>>
>>> db.person.mydog.requires=IS_IN_DB(db,db.dog.dogid,'%(name)s')
>>> db.person.mycat.requires=IS_IN_DB(db,db.cat.catid,'%(name)s')
>>>
>>> -------------------------------------------------------------------------------------------------
>>>
>>> 2. create the table on db2 from dds
>>>
>>> person
>>> -------------------------------------------------
>>> A*
>>> A R PERSONR
>>> A*
>>> A PERSONID 5A
>>> A NAME 50A
>>> A MYDOG 5A
>>> A MYCAT 5A
>>> A*
>>> A* KEY FIELDS
>>> A*------------
>>> A K PERSONID
>>> A*
>>> -------------------------------------------------
>>>
>>> Dog
>>> -------------------------------------------------
>>> A*
>>> A R DOGR
>>> A*
>>> A DOGID 5A
>>> A NAME 50A
>>> A*
>>> A* KEY FIELDS
>>> A*------------
>>> A K DOGID
>>> A*
>>> -------------------------------------------------
>>>
>>> Cat
>>> -------------------------------------------------
>>> A*
>>> A R CATR
>>> A*
>>> A CATID 5A
>>> A NAME 50A
>>> A*
>>> A* KEY FIELDS
>>> A*------------
>>> A K CATID
>>> A*
>>> -------------------------------------------------
>>>
>>> 3. Insert test data
>>> -------------------------------------------------
>>> Dog
>>> dogid = '12345'
>>> name = 'HACHI'
>>>
>>> Cat
>>> catid='CAT01'
>>> name = 'TAMA'
>>> -------------------------------------------------
>>>
>>> 4. Result
>>> I can pull the data from Dog table but not Cat table (see attached)
>>> As you can see the dog and cat table is exactly the same type of table
>>> but dog has data begin with number
>>> and cat begin with alphabet.
>>>
>>>
>>>
>>>
>>> On Sunday, August 19, 2012 10:08:07 AM UTC-5, Massimo Di Pierro wrote:
>>>>
>>>> Is the problem only in appadmin? Can you produce a minimum program with
>>>> some sample data to reproduce this?
>>>>
>>>>
>>>>
>>>> On Friday, 17 August 2012 23:56:39 UTC-5, Omi Chiba wrote:
>>>>>
>>>>> Issue #939 is assigned.
>>>>> http://code.google.com/p/web2py/issues/detail?id=939
>>>>>
>>>>> On Friday, August 17, 2012 9:51:48 PM UTC-5, Massimo Di Pierro wrote:
>>>>>>
>>>>>> Please open a ticket pointing to this thread. Will look at it asap.
>>>>>>
>>>>>> On Friday, 17 August 2012 17:33:51 UTC-5, Omi Chiba wrote:
>>>>>>>
>>>>>>> I finally figured this should be a bug on the current in trunk
>>>>>>> version ("Version 2.0.0 (2012-08-15 17:30:38) dev")
>>>>>>>
>>>>>>> I can pull the data when the data in primarykey field begin with
>>>>>>> numeric numbers for example 12345 or 1ABCDE (See NG-1,2.jpg)
>>>>>>> but cannot pull the data begin with alphabet like A1234. (See
>>>>>>> OK-1,2.jpg)
>>>>>>>
>>>>>>>
>>>>>>> # MF - EDI CUSTOMER
>>>>>>> db.define_table('EDMACF00',
>>>>>>> Field('ACDIID', length=5, label="Dist Code"),
>>>>>>> Field('ACNAME', length=20, label="Dist Name"),
>>>>>>> Field('AC855F', length=1, label="PO ACK FLAG"),
>>>>>>> Field('AC856F', length=1, label="ASN FLAG"),
>>>>>>> Field('AC810F', length=1, label="INVOICE FLAG"),
>>>>>>> Field('ACUPFL', length=1, label="OZ UPLOAD FLAG"),
>>>>>>> Field('ACCRBY', length=20, default = auth.user.username.upper()
>>>>>>> if auth.user else None, label="Created by"),
>>>>>>> Field('ACCRDT', 'datetime', default =request.now, label="Created
>>>>>>> on"),
>>>>>>> Field('ACUPBY', length=20, update = auth.user.username.upper()
>>>>>>> if auth.user else None, label="Updated by"),
>>>>>>> Field('ACUPDT', 'datetime', update =request.now, label="Updated
>>>>>>> on"),
>>>>>>> primarykey=['ACDIID'])
>>>>>>>
>>>>>>>
>>>>>>> db.EDMACF00.ACDIID.requires=[IS_NOT_EMPTY(),IS_LENGTH(5,1),IS_UPPER()]
>>>>>>>
>>>>>>> db.EDMACF00.ACNAME.requires=[IS_NOT_EMPTY(),IS_LENGTH(20,1),IS_UPPER()]
>>>>>>> db.EDMACF00.AC855F.requires=IS_IN_SET(['','1'], zero=None)
>>>>>>> db.EDMACF00.AC856F.requires=IS_IN_SET(['','1'], zero=None)
>>>>>>> db.EDMACF00.AC810F.requires=IS_IN_SET(['','1'], zero=None)
>>>>>>> db.EDMACF00.ACUPFL.writable = db.EDMACF00.ACUPFL.readable = False
>>>>>>> db.EDMACF00.ACCRBY.writable = False
>>>>>>> db.EDMACF00.ACCRDT.writable = False
>>>>>>> db.EDMACF00.ACUPBY.writable = False
>>>>>>> db.EDMACF00.ACUPDT.writable = False
>>>>>>>
>>>>>>> # XR - CUSTOMER
>>>>>>> db.define_table('EDXTKF00',
>>>>>>> Field('TKDIID', length=5, label="Dist Code"),
>>>>>>> Field('TKDICD', length=20, label="Dist Office Code"),
>>>>>>> Field('TKTKSC', length=3, label="Dept"),
>>>>>>> Field('TKTKCD', length=8, label="Customer Code"),
>>>>>>> Field('TKDMCD', 'integer', label="Employee Code"),
>>>>>>> Field('TKUPFL', length=1, label="OZ UPLOAD FLAG"),
>>>>>>> Field('TKCRBY', length=20, default = auth.user.username.upper()
>>>>>>> if auth.user else None, label="Created by"),
>>>>>>> Field('TKCRDT', 'datetime', default =request.now, label="Created
>>>>>>> on"),
>>>>>>> Field('TKUPBY', length=20, update = auth.user.username.upper()
>>>>>>> if auth.user else None, label="Updated by"),
>>>>>>> Field('TKUPDT', 'datetime', update =request.now, label="Updated
>>>>>>> on"),
>>>>>>> primarykey=['TKDIID', 'TKDICD'])
>>>>>>>
>>>>>>>
>>>>>>> db.EDXTKF00.TKDIID.requires=IS_IN_DB(db,db.EDMACF00.ACDIID,'%(ACNAME)s')
>>>>>>>
>>>>>>> db.EDXTKF00.TKDICD.requires=[IS_NOT_EMPTY(),IS_LENGTH(20,1),IS_UPPER()]
>>>>>>>
>>>>>>> db.EDXTKF00.TKTKSC.requires=IS_IN_SET(['J11','J13','J14','J15','J16','J17'])
>>>>>>>
>>>>>>> db.EDXTKF00.TKTKCD.requires=[IS_NOT_EMPTY(),IS_LENGTH(8,8),IS_UPPER(),IS_IN_DB(db,db.CDMTKL00.TKTSCD)]
>>>>>>> db.EDXTKF00.TKDMCD.requires=[IS_NOT_EMPTY(),IS_INT_IN_RANGE(60000,69999,error_message='Invalid
>>>>>>>
>>>>>>> length'),IS_IN_DB(db,db.CDMSYL00.SYSYCD)]
>>>>>>> db.EDXTKF00.TKUPFL.writable = db.EDXTKF00.TKUPFL.readable = False
>>>>>>> db.EDXTKF00.TKCRBY.writable = False
>>>>>>> db.EDXTKF00.TKCRDT.writable = False
>>>>>>> db.EDXTKF00.TKUPBY.writable = False
>>>>>>> db.EDXTKF00.TKUPDT.writable = False
>>>>>>>
>>>>>>>
>>>>>>> On Friday, August 17, 2012 4:38:00 PM UTC-5, Omi Chiba wrote:
>>>>>>>>
>>>>>>>> Just in case, I changed just like you suggested but I got a same
>>>>>>>> result. I can pull the data from other tables on the same database but
>>>>>>>> I
>>>>>>>> cannot pull the data from this one.... I should be dreaming or getting
>>>>>>>> crazy... : (
>>>>>>>>
>>>>>>>> I first doubt a simple typo but I can't figure out.
>>>>>>>>
>>>>>>>> db.EDXTKF00.TKDIID.requires=IS_IN_DB(db,db.EDMACF00.ACDIID)
>>>>>>>> =>
>>>>>>>> db.EDXTKF00.TKDIID.requires=IS_IN_DB(db,'EDMACF00.ACDIID')
>>>>>>>>
>>>>>>>> On Friday, August 17, 2012 4:20:42 PM UTC-5, villas wrote:
>>>>>>>>>
>>>>>>>>> Specify the field that you wish to appear in the dropdown box
>>>>>>>>> Try what the book says, 'name' is simply the name of the field
>>>>>>>>> you wish to appear:
>>>>>>>>>
>>>>>>>>> db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s',
>>>>>>>>> zero=T('choose one'))
>>>>>>>>>
>>>>>>>>>
--
