Thanks - I haven't used _select - I'm checking into it now. Thanks, Philip
On Feb 3, 2:54 pm, DenesL <[email protected]> wrote: > 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
