0

Our team has created Forecast__c which is a custom object related to the Parent Account of any hierarchy. Each Parent Account has a Forecast for every month of the account. So, for example, there is a "Account A, Month 10/22" forecast.

Additionally, each Account, and all its children, have various Opportunities. Opportunities similarly have a date, and a Spend. So, for example, Account A has Opportunities x and y: Opportunity x is for 10/22 and lists Spend = $100, and Opportunity y is also for 10/22 and lists Spend = $50.

I would like to create a field on the related month's forecast that sums up the opportunity spend values for that month. i.e., Total Spend for Account A of 10/22 Forecast = $150.

The goal is to avoid Apex. Is there a way to create a filter that matches the (Month and Account) of the Opportunity to the (Month and Account) of the Forecast? Perhaps through use of a Workflow?

Tried so far: Workflow triggered by the creation/update of the Opportunity. From there, I know I can search for the Forecast, and also the other Opportunities of the same Month/Account. Is there a way to use a formula of the opportunities to place in the forecast?

Or else, if you know a better way, I am open to suggestions. However, again, the goal is no Apex.

Additional Info: Attempts to use Master-Detail Relationship fail because records have already been created. Attempts to use formula fields fail because filters don't seem to be advanced enough. Attempts to use workflows have caused issues in calculating the summations (maybe there is a way that I don't know of?). We have previously used Rollup Helper App to allow for the summation of child accounts to parent, but this does not let you filter based on details of another object. I am very new to Salesforce, and do not have experience with Workflows.

2
  • DLRS or build your own Flow. There are plenty of tutorials on Flows, give it a shot.
    – identigral
    Oct 20, 2022 at 21:54
  • P.S. You just missed it. Workflow rules are going away, the path forward is to use Flows. I wouldn't spend any more time investigating workflow rules.
    – sfdcfox
    Oct 20, 2022 at 22:13

1 Answer 1

0

This is easy enough in Salesforce, but will need two different flows. The first flow would be a Before Save flow that checks for the presence of the Account/Month combination; if not found, creates a new Forecast. After that, assign the Forecast to the Opportunity via a lookup field on the Opportunity. The second flow would be a Record-Triggered flow. The goal here would be to sum all of the opportunities for that month and account, and then set the value for the Forecast to the newly summed value.


That said, have you seen Forecasting? Salesforce has a few different models available, and can do some pretty cool stuff. Combine that with the ability to run reports that include forecasting data, and you might find that all the information you need is already available out of the box. It would even be possible to add this report to an Account layout, allowing you to visualize the data when viewing records directly. Without knowing what your business logic endgame is, this may not work for you after all, but I'd consider it at least worth looking at.

7
  • Interesting! I did not know about Forecasting! It seems that it is a feature of the Manufacturing Cloud? This would need a license? I'm going to look more into it and see if I can bring it up the ladder as a suggested solution.
    – user123801
    Oct 21, 2022 at 18:07
  • @AMtrying Salesforce has two basic models built-in (Standard Forecasting and Customizable Forecasting). These should be a part of Sales Cloud. There are extra options for, e.g. Manufacturing, but the basic bundle might be enough for you. See Setup | Feature Settings | Sales | Forecasts | Forecasts Settings to get started.
    – sfdcfox
    Oct 21, 2022 at 18:16
  • @AMtrying P.S. Make sure you test this out in a Sandbox first, since turning it off isn't nearly as easy as turning it on.
    – sfdcfox
    Oct 21, 2022 at 18:17
  • ah, seems they've retired the Customizable Forecasting version. help.salesforce.com/s/articleView?id=000383459&type=1 I'll give the flows a shot.
    – user123801
    Oct 21, 2022 at 18:48
  • I can't seem to find a way to create the sum.
    – user123801
    Oct 21, 2022 at 20:21

You must log in to answer this question.