On Thursday, March 22, 2012 2:08:06 PM UTC-7, Anthony wrote:
>
> Yes, it works on sqlite, it doesn't seem to enforce the reference, it
>> asked me to manually type the reference, and when i put in something
>> invalid, it put it as a zero instead of giving me an error in the form.
>> According to the web2py documentation, this should be the default:
>>
>> reference <table>IS_IN_DB(db,table.field,format)
>>
>> But it's not doing any checks at all apparently.
>>
>
> I guess the book should be more clear -- the above default validator is
> only added if the referenced table includes a "_format" attribute. So, if
> you do:
>
> db.define_table('customers',
> Field('name', 'string',length=50),
> Field('sapID', 'string', length=10),
> Field('city', 'string',length=50),
> Field('state', 'string', length=2),
> Field('country','string',length=3),
> Field('active','integer'),
> format='%(name)s')
>
> or
>
> db.customers._format = '%(name)s'
>
> then when you create a SQLFORM based on the db.contacts table, the
> "customer" field will be a dropdown showing the names of existing customers
> in the db.customers table and the validator will enforce the reference. You
> can also add the IS_IN_DB validator manually.
>
> Anyway, I'm still not sure why you're getting the invalid table error in
> MSSQL. What happens if you only define the "customers" table and then try
> some operations with it -- does that work? Can you create two very simple
> tables with a reference from one to the other in MSSQL, or does that always
> generate this error?
>
> Anthony
>
Thanks for your help Anthony. I'll try adding the format to see if that
helps. I was using 'database administration' so maybe it's not using a
SQLFORM there. I'll make a page with sqlform and see if that works better.
yes, the table without references works just fine.
the 'dog' and 'person' tables seem to work okay. This is what gives me an
error, and I copied what I did with the person and dog tables (see that
below).
db.define_table('customers',
Field('name', 'string',length=50),
Field('sapID', 'string', length=10),
Field('city', 'string',length=50),
Field('state', 'string', length=2),
Field('country','string',length=3),
Field('active','integer'))
db.define_table('contacts',
Field('customer', db.customers),
Field('lastname', 'string', length=25),
Field('firstname', 'string', length=25),
Field('phone','string',length=15),
Field('email','string',requires=IS_EMAIL()),
Field('active','integer'))
db.contacts.customer.requires = IS_IN_DB(db, db.customers.id, '%(name)s')
-- this fails with the SQL error.
#db.define_table('person',
# Field('name', requires=IS_NOT_EMPTY()))
#db.define_table('dog',
# Field('owner', db.person),
# Field('name', requires=IS_NOT_EMPTY()))
#db.dog.owner.requires = IS_IN_DB(db,db.person.id,'%(name)s')
-- this works fine.
I don't know what is going on... the dog and person tables work, but the
customers and contacts tables do not.
