0

Here's what I need to do. When a new account gets created or updated within the first 30 days since creation, it needs to have a forecasted revenue field populated based on an average of all similar accounts from the last 6 months.

For example, if the new account is region = US and Segment = SMB, it should be based on the average of the revenue field from all accounts with those criteria from the past 6 months.

Is there some way to pull reporting data into APEX without having to rerun the report every single time (maybe refresh daily)? Or is there a better way to store that data in some kind of supplemental data object? Or is it better just to pull those accounts with a soql query and do the math in the trigger?

I'm just really having a hard time figuring out a good way to do this.

  • Do you need this to automatically update a field on the Account or is this something that can happen off hours on a more scheduled basis? If it can be done on a scheduled basis then you might want to look into a scheduled apex class. Also you could look into using the link below if you want to get values from your report. developer.salesforce.com/docs/atlas.en-us.apexcode.meta/… – Dan Kowalski Oct 23 '17 at 15:18
  • When exactly does "Forecasted Revenue" need to be populated? You says when the Account is created or updated so is it when the record is edited the field should be updated or is it scheduled like a daily thing? – Dan Wooding Oct 23 '17 at 15:18
  • This question is quite broad and therefore difficult to answer. Especially since it is still not explicitly clear what your data structure looks like or where data should move around in it. Accessing report data would be more difficult to test and maintain than just rebuilding any filters as SOQL. As for the 6 month averages, that's something you will likely want to stage in a different object. – Adrian Larson Oct 23 '17 at 15:54
1

Assuming you've got less than 2,000 possible summary groups, and you can reasonably build a report that shows the data you want, consider a Reporting Snapshot. Simply create a custom object, build the report, and assign the Reporting Snapshot to run periodically (daily is a possible option). In your trigger, you can simply query for records from this table as normal.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.