Best approach(es) for creating/updating child records from parent?

Question

Trying to find the best approach for managing child records based upon a parent record being created & modified. Specifically I am working on the Opportunity object, and a custom object I created (Opportunity Months) so that we can look at forecasting for month by month.

When Opportunity is created, create up to 12 child records (months). For example if close date = 1/1/2020, create 12 child records for each month of that year. 1/1/2020, 2/1/2020 etc.... If Close Date = 10/1/2020, only create 3 child records. When those are created, populate some fields (forecast amount = [Opportunity].Annual_Revenue__c /12)*[Opportunity].Probability).

When Parent Opportunity is updated (Probability), update the child records Forecast amount. (forecast amount = [Opportunity].Annual_Revenue__c /12)*[Opportunity].Probability)

When Parent Opportunity is updated (Close date), create new records (if close date is sooner) or delete records where the close date was pushed back. For example, if our close date was 1/1/2020 and we changed it to 2/1/2020 delete the record for where the month was for 1/1/2020.

I'm able to achieve some of this within process builder (creating records), but I'm not sure about how to achieve updates / deletes after the fact.

Is this something that would be better suited for Apex?

Answer 1

You're going to need to build in either Apex or a couple of different Flows to get these two requirements done:

When Opportunity is created, create up to 12 child records (months). For example if close date = 1/1/2020, create 12 child records for each month of that year. 1/1/2020, 2/1/2020 etc.... If Close Date = 10/1/2020, only create 3 child records. When those are created, populate some fields (forecast amount = [Opportunity].Annual_Revenue__c /12)*[Opportunity].Probability).

When Parent Opportunity is updated (Close date), create new records (if close date is sooner) or delete records where the close date was pushed back. For example, if our close date was 1/1/2020 and we changed it to 2/1/2020 delete the record for where the month was for 1/1/2020.

That's both because you cannot delete records in Process Builder (Flow and Apex are the only automation tools that support deletion) and because you will not be able to build this complex logic in Process Builder, which doesn't support allow you to write queries.

I could see these potentially being one Flow fired by a Process upon either a new record or an update to the Close Date field. That Flow's job would be to create all required records based on the new Close Date and then query for and delete all records that do not fall within the appropriate date range for the parent Opportunity.

I personally would implement the whole shebang in the form of an Opportunity Apex trigger with a handler class, but I think it's doable declaratively if that's your or your org's preference.