I cant believe this kind of functionality doesn't come out of the box with SF. If anyone could help in any way it would be much appreciated.
This is what I have in my mind from concept. Using batch apex, I am getting all the opportunities that fall in the date range that the business requires as current opps. I used those ids to build a aggregate on the child records to get the summary totals and the child date 02/01/xx. If it exists I get that summary and put it on the opportunity parent record.
The part I can't figure out as of yet is how to handle getting the last years child summary on to the current years opportunity when the above does not have a 02/01/xx (current year records on the child object). What I was thinking is another job that would run after the first (described above) and this time get the current years opportunities but only if the field that I wan the summary to be on is blank. That would give me all opps that didn't get the summary total for the 02/01/xx records on the child object. But some how I have to the prior years opportunities so that I can get to the related child records to get the summary of the 02/01/xx (last years date on the child records) and put that total on the current years opportunity that is also related to the same account record.
Any ideas on a best approach for something like this?
Does the Opportunity Stage History object have any link to the opportunity? When I look in the schema browser in Eclipse and do a query on the Opportunity Stage History I don't see any link to the Opportunity that the history record is associated with.I need to query that object in Apex but I don't see a way to know what record is assocated with what opportunity.
Please let me know if there is something I am missing