Change Picklist Value on Opportunity + Update fields

Question

I have a custom object - Object 1 and the Opportunity object. There was no relationship between the two objects, so I've created a lookup field from Object 1 to the Opportunity , that I am populating through a trigger, in order to update some fields from the custom object on the Opportunity, but also to change a picklist value on the Opportunity.

The records on the Object 1 are the Opportunity Line Items triggered from another system in Salesforce, so every time a modification happens (e.g price) in the other system, a new record is being created in Salesforce for the same OLI, but with the new value (price). The records share the same "lineitemid" to identity that records relate to the same opportunity line item.

What I am trying to achieve:

• to populate custom field 1 from Opportunity with custom field 1a from Object 1.

• to populate custom field 2 from Opportunity with custom field 2a from Object 1.

• to change picklist value on Opportunity based on below:

  "A" - if the text field contains "a", but it might also contain "b", "c" or "d", but it should definitely contain "a".

For each picklist is a combination of the above.

For one picklist value though:

"E" - if the text field contain 2 or more of the "a", "b", "c" "d" combination.

What I have done so far:

1. Create the lookup field between the objects and populate it through trigger
2. Created a process on Object 1 - when a record is created or edited, with immediate actions: custom_field1__c FIELD REFERENCE customfield1a__c

The process builder is populating the custom field1 on Opportunity with the custom field 1a from Object 1 value, but if I get a new record in Salesforce where custom field 2a is populated on Object 1, it's deleting the value in the custom field1 on Opportunity and it's populating the custom field2 on Opportunity.

Essentially, I want the process to keep the value in custom field 1 on Opportunity and also add the value of custom field2, when it gets populated on Object 1.

I don't seem to be able to find a solution. Any help with be much appreciated.

Answer 1

I continue, as I've mentioned before, to be concerned that the data model here doesn't quite make sense. But diving in...

Let's start with the basics. You have two objects, Opportunity and this other object, which I'm going to call Line Item since that is the role that it plays. Your Line Items are associated with an Opportunity, which means that the Line Item object contains a lookup field to the Opportunity record. This is a one-to-many relationship: an Opportunity has many Line Items, each Line Item has exactly one Opportunity.

to populate custom field 1 from Opportunity with custom field 1a from Object 1.

to populate custom field 2 from Opportunity with custom field 2a from Object 1.

These expectations do not fit a one-to-many relationship. Remember, Opportunity is the one, Line Item is the many. How are you proposing to get the field values from multiple Line Item records into a single field on one Opportunity?

Classically, this is the use case for a Roll-Up Summary Field, or for one of the products (like Declarative Lookup Rollup Summaries) that extends roll-up functionality to situations not supported by the native Roll-Up Summary Field.

The way that you articulate it below

Essentially, I want the process to keep the value in custom field 1 on Opportunity and also add the value of custom field2, when it gets populated on Object 1.

sounds like what you have is multiple fields on the Opportunity, and you want to populate them with fields from the related Line Items in sequence as the Line Items are created.

This does not really make sense to me and it certainly won't scale well, but it is possible. (What happens when you get to 4 Line Items? Do you need 4 fields? What about 12?)

You'd need to build out your Process to condition its update actions on whether or not the related Opportunity's fields are blank. You'd update Field 1, for example, if it's blank. You'd update Field 2 if field 1 is not blank and Field 2 is blank. You'd update Field 3 (if needed) if Field 1 and 2 are not blank and Field 3 is blank.

This will get messy, fast. I strongly suspect this is either (a) a data model that is mismatched with the problem it is intended to solve or (b) an x-y problem.

Edit

Per your comments, what you want is to concatenate the values of a specific field from your Line Items on the parent Opportunity.

You can either build your Process Builder automation to take into account existing values in the field and concatenate new values, rather than overwriting the existing value, or use Declarative Lookup Rollup Summaries to build a Concatenate Distinct rollup to populate the field.

Your formula field is really a completely different problem. You will need to finish a successful solution for summarizing this line-level detail at the Opportunity level before you are able to begin writing a formula field against it.