function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

How to get the correct opportunity record...

I was hoping for some suggestions on how to iterate over opportunity record's child object to get a summarized total from the opportunity child record if a date on that child record meets the criteria of 02/01/xx (year would be current year).  If no records on the child fall in the 02/01/xx then I need to look at the prior year's opportunity child record and get it's 02/01/xx (last years) summary total.

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?
Anthony GiulianoAnthony Giuliano
What if you creat a Roll Up field on the Account that summarizes an Opp field?  I believe you can filter it for your date criteria so it only sums your target field from last years opportunity.  Then you have access to that Account field on any given Opp and a simple formula field could return either the Account summary field or your custom Opp summary field.

Also if your Opp child object is Master Detail you should be able to create a similar (filtered) Roll Up summary field on the Opp to replace all that apex.

If I'm misunderstanding your criteria and none of that gets the job done, you should look in to Flows.  Flows are a powerful tool that helps you utilize those tricky database relationships.
Hi Anthony, you are correct to use a rollup for the easy path of getting the child data to rollup but if that child data does not have a 02/01/14 data value then I need to get it from the child records on the last years opportunity and put that rollup on the current opportunity.  I think that can only be done in Apex--unless I am missing something.

To paint a -- hopefully more clear picture.  There are opportunities with an effective data and contrac end date.  I pull all those opps with a date range that identifies the current active opps.  Sometimes people create opp into the future so my query won't include those.  I need to know how I can easily get a list of opp ids that are the sibling to the current opps but only the prior opps when the child data on the current opp record does not have a data of 02/01/14 -- assuming 2014 is the current year.

I don't think the rollup idea will work because it has to be able to only rollup the 2/1/14 data assuming this year is 2014.  It seems that the rollup filter criteria does not let you use any functions like YEAR on a date field.
Anthony GiulianoAnthony Giuliano
Oh sorry, I thought you could use those dynamic date values in that filter like "This Year" or "Last 7 Days".