Retrieve specific field of a related list as field of its parent

Question

I´m trying to figure out how to achieve the following requirement:

Pick a specific field from a related list of Account and put in in a field in Account object. Like the image below.

This would help us to segment our campaign and give us at a glance the type of products the company has acquired, like summarized data.

Solution path would be: Trigger? Process Builder? Workflow rule? Junction Object? How?

Answer 1

My first thought was that this could be accomplished with a report using a report type of Accounts with <relatedObject>. With the information that you've provided, I can't see how this wouldn't work (With such a report, you would be able to see the individual Accounts, and all product models available on each individual Account.)

That aside, assuming that a report is not a viable solution, my next thought would be to do this with a Trigger.

+edit:

As I was writing this, crop1645 commented about the declarative rollup summaries package. I had completely forgotten about this, and it's probably the best solution (after building a report).

I'll keep going in case someone can't use the delcarative rollup summary package.

/edit

You could go about this in two ways, (spoiler: they end up being very similar) depending on the type of relationship between Account and your unnamed relatedObject. For ease, I'll assume your related object is called Product__c, and that it's relationship name is simply Products

If your two objects are in a Master-Detail Relationship

This is the easy case. Assuming Account is the Master object, you could use a Parent-Child subquery in an Account Trigger Before Update.

Set<String> models; = new Set<String>();
Account temp;
for(Account acct : [SELECT Id, (SELECT Id, Model__c FROM Products) FROM Account WHERE Id IN :Trigger.new]){
    temp = Trigger.newMap.get(acct.Id);
    models = new Set<String>();
    for(Product__c prod : acct.get('Products')){
        models.add(prod.Model__c);
    }
    temp.All_Available_Products__c = String.join(new List<String>(models), '; ');
}

In the above example, the temp variable is needed because to take advantage of the ability of the Before Update trigger event to update the Account record without DML, you need to update the instance of the Account record that resides within Trigger.new or Trigger.newMap.

The Set<String> models is just there to make sure that you don't end up with duplicate models being listed on your Account.

The Master-Detail relationship ensures that Account's trigger(s) will be fired when a Product__c is inserted or updated.

If your Product__c object has a lookup relationship to Account

You can largely reuse the code that was used for the Master-Detail case. The difference being that you have to gather a list of Account Ids instead of being able to rely on a Trigger context variable, and you need to perform a DML update.

// Gather all of the Ids of accounts that your Product__c records are related to
Set<Id> accountIds = new Set<Id>();
for(Product__c prod : Trigger.new){
    accountIds.add(prod.Account__c);
}

// At this point, most of the rest of the work can be done by code very similar to the code used in the previous example
Set<String> models; = new Set<String>();
List<Account> accountsToUpdate = new List<Account>();
for(Account acct : [SELECT Id, (SELECT Id, Model__c FROM Products) FROM Account WHERE Id IN :accountIds]){
    models = new Set<String>();
    for(Product__c prod : acct.get('Products')){
        models.add(prod.Model__c);
    }
    // Note that we're not using the 'Account temp' variable here
    acct.All_Available_Products__c = String.join(new List<String>(models), '; ');
    // Because we're not in an Account Trigger (Before Update), we need to add the modified account to a list so we can call DML update
    accountsToUpdate.add(acct);
}

update accountsToUpdate;