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
