Referencing junction object records on master object

Question

On the Opportunity object I need to reference the industry sector of the Opportunity Contact Role junction object.

One Opportunity has multiple Opportunity Contact Role relationships. If at least one contact has the Maritime industry, I need a field on the Opportunity saying 'Maritime'. If the sector is not included in the contacts, the field should just display 'Other'.

I tried to achieve it with a formula field but it is impossible to reference Opportunity Contact Role from the Opportunity since it's a master object in this case.

Roll-up Summary field is no option either.

I would be grateful for any hints.

Answer 1

For this scenario you'll need to use something other than a formula field due to the many-to-one nature of a parent child relationship.

I'd recommend using a Record Triggered Flow on the Opportunity Contact Role object to update the parent Opportunity record based on the logic you've included in your question. You can either have the Flow check all associated child records, or have the Flow update a 'Has Maritime Contact Role' (or similar) checkbox field on the Opportunity whenever an Opportunity Contact Role with the 'Maritime' industry is added and use that field in your formula field that you have tried.

Hope this helps but happy to clarify anything that doesn't make sense.