Resolved! This is the sql string needed to produce a selection for my reference field in IS_IN_DB:
select vehicle.id,vehicle.manufacturer,vehicle.name,UPPER(vin) as vin from vehicle inner join (select a.own_veh_fk from owner a where a.own_end_date is not null and a.own_veh_fk not in (select b.own_veh_fk from owner b where b.own_end_date is null)) c on vehicle.id=c.own_veh_fk union select vehicle.id,vehicle.manufacturer,vehicle.name,UPPER(vin) as vin from vehicle left join owner on vehicle.id=own_veh_fk where own_veh_fk is null; This is converted version to web2py: q3=(db.vehicle.id==db.owner.own_veh_fk)&(db.owner.own_end_date!=None)&(~db.owner.own_veh_fk.belongs(db(db.owner.own_end_date==None)._select(db.owner.own_veh_fk))) q4=~db.vehicle.id.belongs(db(db.owner.own_veh_fk>0)._select(db.owner.own_veh_fk,left=db.vehicle.on((db.vehicle.id==None)))) db.owner.own_veh_fk.requires=IS_IN_DB(db(q3|q4),'vehicle.id', ' %(name)s %(vin)s',distinct=True) Gee it works! Just being on this forum helped. Thanks. There are probably still other ways of doing it! More efficient but....this is only my passtime. On Wednesday, May 28, 2014 7:47:36 PM UTC-4, [email protected] wrote: > > Thank you Mr. Massimo and your colleagues for bringing the web2py to the > masses. > I need to have a field that will present a choice of values that result > from a union of query1 and query2 > How can i create a set that would go where the question mark is? Thanks > for any help! > > query1=((db.vehicle.id > ==db.owner.own_veh_fk)&(db.owner.own_end_date!=None)) > > query2=(~db.vehicle.id.belongs(db(db.owner.own_veh_fk==db.vehicle.id > )._select(db.vehicle.id))) > > db.owner.own_veh_fk.requires=IS_IN_DB((db(?)),'vehicle.id', > '%(manufacturer)s %(vin)s') > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
