How do I use a merge field in a formula that is rolled up?

Question

I have a formula on object Engagement__c.

And when I try to use a value from Contact lookup in a formula with this code:

(Contact__r.FirstName & " " & Contact__r.LastName)

it shows me this error message:

Error: Invalid Data. Review all error messages below to correct your data. Unable to use this formula containing derived values because the field is included in the following roll-up summary field(s): Contact.Engagement Score (Last 6 Months),Contact.Engagement Score. To continue, remove references to derived values such as current date, current user, or user-role. (Related field: Formula)

How could I use those values? Should I use before create/update flow or should I use Apex to update a new field on Engagement (Contact_FirstName_LastName__c) with values from Contact lookup?

Answer 1

The trigger for recalculating a rollup summary field is usually the insert/update/delete of a child record.

Since these 2 fields are on a different object, the rollup recalculation does not get triggered whenever the Contact object is updated. That would mean that there can be a data inconsistency, which is why SF blocks the usage of this kind of fields in rollups.

The solution would indeed be to create a new text field on Engagement and fill it by a before insert/before update Flow with the values from the Contact record from the Lookup. Then use that new field in your formula.

This means that if the Contact FirstName/LastName would ever change, that would not be reflected on the Engagement records (and therefore on the score). If this needs to be supported, you'd need to create a record-triggered Flow on Contact as well that updates all the related Engagement records with the new FirstName/LastName values.