Update a record based on the field update on any grand child record

Question

I have three different objects A (Grand Parent), B (Child) & C (Grand Child). All the three objects are related by a lookup relationship. Now, there is field on A object, IsActive which is a checkbox and another field IsEligible on object C which is also a checkbox.

Now, if any of the Grandchild associated with A is having IsEligible marked as TRUE, then IsActive on the A's record should be marked as TRUE . If none of the Grandchild records are having IsEligible marked as TRUE, then IsActive should be marked as FALSE.

Parent - A Child - B1, B2 GrandChild - C1, C2 associated with B1 & C3, C4 associated with B2.

If either of the records, C1, C2, C3, C4, has IsEligible = TRUE, then IsActive = TRUE on A. If neither of the records, C1, C2, C3, C4, has IsEligible = TRUE, then IsActive = FALSE on A.

How can this functionality be achieved?

Answer 1

You're essentially trying to create a programmatic rollup, with the extra wrinkle being that you have an extra level of object hierarchy. However, that doesn't fundamentally change the solution set that's available.

The best solution, as is typical with these kind of needs, is to use Declarative Lookup Rollup Summaries. With DLRS, you can do a roll-up from the grandchild to the child (a COUNT roll up, specifically, filtered on your eligibility criteria). You can then roll up that first roll up field from the child to the parent using a SUM roll up to get a total count of eligible objects on the top-level parent.

Once those rollups are established, populating the check box field on the grandparent is easy. Make it a formula field Count__c > 0, or use a workflow rule to update it whenever the count field is updated by DLRS.

If you must reinvent the wheel, you can do this in pure custom code. However, it'll involve writing two moderately complex triggers that replicate work already done by DLRS. It's also possible to achieve in a fully declarative way using Process Builder and Flow, but at this complexity level I would say that's very unadvisable.