Whoo Hoo! Victory Here is the way I got it working:
In my controller I have the following:
techSet = db((db.auth_user.id==db.helpdeskTech.userId) & \
(db.helpdeskTech.helpdeskId==ticket.helpdeskId))
db.ticket.assignedTo.requires = IS_NULL_OR(IS_IN_DB(techSet,
db.helpdeskTech,
lambda r:
db.helpdeskTech(r.helpdeskTechId).userId.lastFirst,
zero='...choose...'))
Thanks so much for your help Richard. I couldn't have done it without
the help you provided.
The techSet filters the dropdown list to only the user names that are available for the specific helpdesk. And then the lambda function allows me access to the helpdeskTech id for the specific row in the SELECT widget and from there I can grab any info I need.
Thanks again for all the help.
-Jim
On 5/8/2012 3:39 PM, Richard Vézina wrote:
Also, if you just don't want to use auth_group and auth_membership, you can just add a flag to auth_user if you define your own custom auth_user and then refer to this flag to create your set for IS_IN_DB...RichardOn Tue, May 8, 2012 at 4:38 PM, Richard Vézina <[email protected] <mailto:[email protected]>> wrote:Since virtual field are calculated each query I don't think you can refer to them in IS_IN_DB as a table field... Could be a solution if it works. Richard On Tue, May 8, 2012 at 2:10 PM, Jim Steil <[email protected] <mailto:[email protected]>> wrote: Anyone know if I can use a virtual field in my IS_IN_DB validator? -Jim On 5/8/2012 12:48 PM, Jim Steil wrote:Richard First off, thanks for all the help, I certainly appreciate it. I'm trying to work with the second suggestion below, but am having trouble as you suspected with getting the row.id <http://row.id>. I'm shying away from the auth_group idea because I prefer to enforce within the db that the users on that ticket can only be a predefined subset of users. I think that is more of a personal preference, but for now I'm going to continue going down the other path. Any other pointers are appreciated. I'll continue digging in to this for the afternoon. -Jim On 5/8/2012 8:22 AM, Richard Vézina wrote:Ok, I think I understand... You create a kind of materialized view with helpdeskTech??? If you only need a "subset" of auth_user, you can create a auth_group for this. I mean, you create a auth_group "tech" and you assign user to this group then you will be able to make a set of those users like this : tech_set = db((db.auth_group.role=='tech') & (db.auth_user.id <http://db.auth_user.id>==db.auth_membership.user_id) & (db.auth_membership.group_id==db.auth_group.id <http://db.auth_group.id>)) Then use this set in ticket requires directly instead of passing by the helpdeskTech. requires=IS_IN_DB(tech_set,'auth_user.id <http://auth_user.id>','%(first_name)s %(last_name)s',\ cache=(cache.ram, 3600)), There is other solution with you helpdeskTech, but I am not sure it works : requires=IS_IN_DB(db,'helpdeskTech.id',db.auth_user(db.helpdeskTech(row.id <http://row.id>).id).first_name + db.auth_user(db.helpdeskTech(row.id <http://row.id>).id).last_name,\ cache=(cache.ram, 3600)), This work in represent : db.auth_user(db.helpdeskTech(row.id <http://row.id>).id).first_name + db.auth_user(db.helpdeskTech(row.id <http://row.id>).id).last_name Because represent is a lambda and you pass row, but in case of requires I think that you can't get the ID of the not yet created record, that I why I use group assignment and set and don't create kind of materialized view like you... I think you can do both, have a materialized view if you need it for some purpose and have a group assigment and set approach at the same time. Hope I am more clear. Richard On Mon, May 7, 2012 at 5:37 PM, Richard Vézina <[email protected] <mailto:[email protected]>> wrote: Sorry for the lazy example, I have to go, I can try to write the proper code if you didn't solve it tomorrow, just ask. Richard On Mon, May 7, 2012 at 5:36 PM, Richard Vézina <[email protected] <mailto:[email protected]>> wrote: Ok, now I understand... You should allways use id instead of name or other field. Anyway I think you can figure it out from this example : dog name person_id person name stat_about_dog_and_person stat1 stat2 dog_name person_name If I want to get dog_name or person_name in stat_about_dog_and_person.dog_name I could something like this : db.dog(ID).name You just have to replace the ID by the id or a query that return the id... You can even do : db.dog(db.auth_user(db.ham(ID).id)).id).name Richard On Mon, May 7, 2012 at 5:09 PM, Jim Steil <[email protected] <mailto:[email protected]>> wrote: Sorry, not sure I follow. In my example, helpdeskTech is a subset of auth_user. My 'ticket' has a helpdeskTechId (in the assignedTo field), not an id from the auth_user table. I want my select tag to display the auth_user name, but return the helpdeskTechId from the helpdeskTech table. Maybe I don't fully grok the IS_IN_DB and the way the parms work. I'll go back and look that over again. -Jim On 5/7/2012 3:59 PM, Richard Vézina wrote:auth_user_rows = db().select(db.auth_user.id <http://db.auth_user.id>) make a set : auth_user_set = ((db.auth_user.id <http://db.auth_user.id> == rows.first().id)|(db.auth_user.id <http://db.auth_user.id> == rows.last().id)) IS_IN_DB(auth_user_set,...) Richard On Mon, May 7, 2012 at 4:37 PM, Jim Steil <[email protected] <mailto:[email protected]>> wrote: Hi I am having trouble getting my list to display the way I want it to. Given the following definition: ----------------------- helpdeskTech = db.define_table('helpdeskTech', Field('helpdeskTechId', 'id', readable=False), Field('helpdeskId', db.helpdesk, required=True, label='Helpdesk'), Field('userId', db.auth_user, required=True, label='User'), format='%(userId.lastFirst)s') helpdeskTech.helpdeskId.requires = IS_IN_DB(db, db.helpdesk,'%(name)s', zero='...choose...')helpdeskTech.userId.requires = IS_IN_DB(db, db.auth_user,'%(lastFirst)s', zero='...choose...')helpdeskTech['_plural'] = 'Technicians' ticket = db.define_table('ticket', Field('ticketId', 'id', readable=False), Field('helpdeskId', db.helpdesk, required=True, label='Helpdesk'), Field('name', length=100, required=True), Field('description', 'text'), Field('createdOn', 'date', label='Created'), Field('createdBy', db.auth_user, required=True, label='Creator'), Field('assignedTo', db.helpdeskTech, label='Assigned To'), Field('priority', length=10, required=True), Field('status', length=10, required=True), format='%(name)s') ticket.helpdeskId.requires = IS_IN_DB(db, db.helpdesk,'%(name)s', zero='...choose...')ticket.name.requires = IS_NOT_EMPTY() ticket.createdOn.requires = IS_DATE('%m/%d/%Y') ticket.createdBy.requires = IS_IN_DB(db, db.auth_user,'%(lastFirst)s', zero='...choose...')ticket.assignedTo.requires = IS_NULL_OR(IS_IN_DB(db, db.helpdeskTech,zero='...choose...'))----------------------- I want my IS_IN_DB validator in the last line to display the last name and first name from the auth_user table. But, what I'm getting is the helpdeskTechId field displaying. I'm wondering how I can refer back to the auth_user table to get the names to display in the dropdown instead of the helpdeskTechId without custom-coding the view. Thoughts? -Jim
