Ciao Massimo! Thank you so much for your extremely quick response!
I followed your suggestions and it works now. Gonna design a controller... I encountered problems during automatic migrations several times but can't yet explain why (»extreme programming«). The model an the MSSQL DB came out of sync. If this occurs again I'll try to post a reproducible example. About the initials: I think your lambda generates them automatically from the first letters of name_first and name_last. This is not intended - what I translated as »initials« could be composed of various letters from name_first and name_last, manually chosen to ensure they're distinct. That's common in corporate environments, I even know complex examples like XXX#yy where XXX refers to a corporation's subsidiary code, # to the hierarchical departement level, yy could be the classical initials (first letter of first name and last name) or simply the first two letters of last name. How would you call such a field in English? In online dictionary discussions this is kind of an unresolved topic; one suggested »paraph«, while a native speaker replied, he would have to look up »paraph« and rather use »initials«. Thank you! L. On Nov 17, 8:56 pm, mdipierro <[email protected]> wrote: > The problem is here > > > db.task.id_person_released.requires = IS_IN_DB( db,'person.initials', > > '%(initials)s | %(name_first)s %(name_last)s') > > and 'person.initials' shoud be 'person.id' for a reference field. > > You also have a | instead of an &. > > Anyway, here is a more compact notation that should work > > # Table Definitions > > db.define_table("person", > Field("initials",length=16, notnull=True,unique=True), > Field("name_last",length=128, notnull=True), > Field("name_first",length=128, notnull=False,default=''), > format='%(initials)s | %(name_first)s %(name_last)s' > ) > > db.define_table("category", > Field("name",length=128, notnull=True,unique=True), > format='%(name)s' > ) > > db.define_table("task", > Field("title", length=256, notnull=True), > Field("description", "text"), > Field("id_person_assigned", db.person, ondelete='NO ACTION'), > Field("id_person_responsible", db.person, ondelete='NO ACTION'), > Field("id_person_released", db.person, ondelete='NO ACTION'), > Field("date_due", "datetime"), > Field("date_nextcheck", "datetime"), > format='%(title)s' > ) > > db.define_table("task_category", > SQLField("id_category", db.category), > SQLField("id_task", db.task) > ) > > task_category = > db((db.category.id==db.task_category.id_category)&(db.task.id==db.task_category.id_task)) > > I would also replace > > Field("initials",length=16, notnull=True,unique=True), > > with > > Field("initials",length=16, compute=lambda row: ''.join(x[0] for x > in (row.first_name+' '+row.last_name).split())), > > On Nov 17, 1:22 pm, asklucas <[email protected]> wrote: > > > Hello! > > > I'm new to web development and my trying web2py because of DRY and, > > most importantly, Python. > > > I created a model, however I can't insert records in the admin > > interface because of a constraints-error I don't understand. > > > When trying to insert a record > > here:http://127.0.0.1:8000/task/appadmin/insert/db/task > > > I get the Error Snapshot: > > <class 'pyodbc.IntegrityError'> > > ( > > ('23000', > > '[23000] [Microsoft][ODBC SQL Server Driver][SQL Server] > > The INSERT statement conflicted with the FOREIGN KEY > > constraint "task_id_person_assigned__constraint". > > The conflict occurred in database "taskdb", table > > "dbo.person", column \'id\'. (547) (SQLExecDirectW); > > [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The > > statement has been terminated. (3621)' > > ) > > ) > > > What I really want is to have dropdown fields in [task] for all three > > person-fields, where only persons from the list can be chosen or no > > one (a task could be created without yet assigning it to anybody). > > > Additionally I'd like to have a field in [task] where I can choose 0 > > to n categories. > > > Thank you very much! > > > Lucas. > > > Model: > > > <code> > > # Table Definitions > > > db.define_table("person", > > Field("initials", "string", length=16, notnull=True, default=None, > > unique=True), > > Field("name_last", "string", length=128, notnull=True, > > default=None), > > Field("name_first", "string", length=128, notnull=False, > > default=None) > > ) > > > db.define_table("category", > > Field("name", "string", length=128, notnull=True, default=None, > > unique=True) > > ) > > > db.define_table("task", > > Field("title", "string", length=256, notnull=True, default=None), > > Field("description", "text"), > > Field("id_person_assigned", db.person, ondelete='NO ACTION'), > > Field("id_person_responsible", db.person, ondelete='NO ACTION'), > > Field("id_person_released", db.person, ondelete='NO ACTION'), > > Field("date_due", "datetime"), > > Field("date_nextcheck", "datetime") > > ) > > > db.define_table("task_category", > > SQLField("id_category", db.category), > > SQLField("id_task", db.task) > > ) > > > # Table Relations > > # (remove fields you don't need from requires) > > > db.task.id_person_assigned.requires = IS_IN_DB( db, > > 'person.initials', '%(initials)s | %(name_first)s %(name_last)s') > > db.task.id_person_responsible.requires = IS_IN_DB( db, > > 'person.initials', '%(initials)s | %(name_first)s %(name_last)s') > > db.task.id_person_released.requires = IS_IN_DB( db, > > 'person.initials', '%(initials)s | %(name_first)s %(name_last)s') > > > task_category = db( > > (db.category.id == db.task_category.id_category) | (db.task.id == > > db.task_category.id_task) > > ) > > </code> > >
