Trigger to update field on master object based on detail object field

Question

I'm working with Opportunity object and OpportunityDetails object that have a master-detail relationship with one another. The master's api name is Opportunity and the detail's api name is Opportunity__Details__c.

I'm trying to write a simple trigger that will query for the field "Status__c" in the detail object and assign the min value to the field OppStatus__c in the master object. The Field Status__c is a picklist value field with values Closed,Pending,New .(Min=New,Max=Closed)

Answer 1

Do you need a trigger to do this?

There is a way to do this without a trigger (and without the Declarative Lookup Rollup Summary tool) using 2 formula fields and a rollup summary field (since your two objects have an M-D relationship).

First formula is on Opportunity_Detail__c, and assigns a number to each of your picklist values.
You'd then be able to do a rollup using the MIN operator to Opportunity.
The second formula field would be on Opportunity to turn the number back into a text value (matching the text of the picklist values on the child object).

That said, that does come with complications like additional maintenance whenever picklist values are added/moved/shuffled around. The code required to do this via a trigger shouldn't be too bad, but you do need to consider cases like:

• What happens when an Opportunity Detail moves to another Opportunity?
• What happens when an Opportunity loses its last Opportunity Detail?

So declarative, or via trigger?

Honestly, I think it's a toss-up between which solution is better. I believe that the DLRS tool should be able to roll up picklists (doesn't appear that standard rollup fields can do that), and would thus be the easiest to recommend.

But I want to learn how to do this in code

In this case, an important bit of knowledge to have is that picklist fields can be used in the ORDER BY clause in SOQL. The ordering is based on the order in which the picklist values appear (in the active picklist values). It's helpful to think of it like the first picklist value = 0, the second value = 1, and so on.

You can base your query on the Opportunity_Detail__c object, but I think that basing it on the Opportunity object and using a parent-child subquery is easier to work with. Something like

[SELECT Id, 
    (SELECT Status__c FROM Opportunity_Details__r ORDER BY Status__c ASC LIMIT 1)
FROM Opportunity
WHERE Id IN :idSet
]

Basing the query on the parent object and limiting the subquery to 1 row means you don't need to worry about trying to track the "minimum" status per parent yourself. Limiting the subquery to a single row means that it's safe to access the subquery results directly (as opposed to using a nested loop).

You'd pretty much just loop over the result set and write oppsToUpdate.add(new Opportunity(Id = opp.Id, Min_Status__c = opp.Opportunity_Details__r[0].Status__c));

If you do choose to go down the trigger route, then at the very least you'll also want to consider the case when an Opportunity loses its last child record. If your trigger is on the child object, then you only really need to guard against opp.Opportunity_Details__r being empty (the embeded list will never be null, just empty). Because the query here is based on the parent object, you'll still get a result from the query even if it has no more children.

The same cannot be said if the query was based on the child object (like you would be doing if you were to use SUM(), MIN(), MAX(), or AVERAGE() and GROUP BY to roll up numerical data yourself). The way I've handled that in the past has been:

• declare a map of parent records that need updating
• iterate over the child records, and put a new parent record instance into the map (with the Id set, and your rollup field set to a default value like null)
• query the parents based on the keyset of the map you made, and iterate over it
• put a new parent record instance into the map (with the Id set, and your rollup field set to the target child value)

The idea is that when a parent loses its final child record, it'll no longer be returned in the List<AggregateResult>, so you need to make sure you have something in your map for those parents. Otherwise, your parent field will be stuck at the value just before it lost its final children.

For things that do end up in the List<AggregateResult>, you'd just simply overwrite the "default" value in the map (by virtue of iterating over the aggregate results).