This might be doable by using _select (note the underscore) for the inner selection. Can you post the SQL command that you wish to accomplish?.
On Feb 3, 2:14 pm, Philip <[email protected]> wrote: > Here's an example of what I am trying to do - suppose I have an app > that tracks a sales pipeline in which each opportunity moves through a > series of stages (ending with either a closed sale or a lost sale). > In most daily usage, all I need to know is the current status. The > challenge is that I also want to be able to look back and see how each > opportunity progressed (how far and how fast did it go before it > resolved as either closed or lost). > > The approach I tried is to have a separate 1-many table with the > related_opportunity_id, the stage, and the date moved. That way, each > time an opportunity moved to the next stage, a record would be > created. This is the approach I tried, but I cannot find a way to > select only the most recent entry for each opportunity to show in > reports or edit forms. > I once wrote a similar application in MS Access and used this > method; I created a query to show me only the most recent stage for > each opportunity, and joined that query (rather than the whole table > of stage records) back to the opportunities table. As I understand > it, based on a thread from last April on this list, web2py does not > have a similar mechanism to join the result of a db().select() back to > another table in a subsequent select. Am I correct in that? If so, > is there a better way to do this in web2py? > > any ideas on either making this approach work in web2py or a totally > different approach would be much appreciated. > > Thanks, > Philip
