Reciprocal Status Updates on Related Records (Part 2: Update Master Record when all Detail Records are Updated)

In Part 1 of this series, we used Process Builder to update the related records of a particular object when the status field (it could be any field) of our parent object was updated.

The Scenario

What about a scenario where some Requisition Line Items were “pending” for a time or “back-ordered” while other line items were approved already, wouldn’t it be great if once all the line items were marked as approved, the status of the parent Requisition was changed to “delivered”?

Other example scenarios: This same technique could be used for any master-detail relationship. Other program management use cases could include the following ideas:

  • A Service Plan object has a child/detail object called Action Tasks and we’d like Salesforce to automatically change the status of the Service Plan to “Completed” as soon as all the Action Tasks associated with that Service Plan are completed.
  • An object storing applications for medical assistive equipment has a child/detail object to track the individual pieces of equipment being requested. We could use this technique to automatically mark the application as “Closed-Fulfilled” as soon as all the records representing the pieces of equipment are updated to “Distributed”.

Our Task

Update the status of the parent record to a certain value (e.g. Delivered or Fulfilled) when all records in a related list are updated to a certain value (e.g. Delivered).

The methods can differ a little if the relationship is a master-detail relationship or a lookup relationship. For this post we’ll be looking at a master-detail relationship and therefore we’ll be able to use a Workflow Rule aided by Roll-up Summary Fields.

The Steps

Since our objects, Requisition and Requisition Line Items are in a master-detail relationship we can use two roll-up summary fields as helper fields. We’ll create a field that counts the total Requisition Line Items and a field that counts the “delivered” Requisition Line Items. Then our Workflow Rule will trigger when those two fields are equal and it will update the status of the related Requisition to “delivered” as well.

Step 1 – Create the total count roll-up field

In Setup, go to Object Manager and find the parent object you’re working on (the master in the master-detail relationship). For this example, that’s the Requisition object.

Go to Fields & Relationships, and create a new field. On the “Step 1” screen of the new field wizard, select “Roll-Up Summary” for the data type. I like to be as descriptive as possible in my field labels, so I called the first roll-up field “Count of Requisition Line Items”. Create your own title on the “Step 2” screen and then select the detail object as the “Object to Summarize” on the “Step 3” screen.

Your roll-up summary field should look like this:

Step 2 – Create the “delivered” count roll-up field

Create another Roll-Up Summary Field that uses a filter to count only those Requisition Line Items whose status equals “Delivered”. This is done on “Step 3” of the new field wizard in Salesforce under the subheading “Filter Criteria”; select the radio button for “Only records meeting certain criteria should be included in the calculation”.

Feel free to uncheck the boxes on the “Add to Page Layouts” screen (Step 5 of the new field wizard) so that these “helper fields” aren’t visible to your users.

Your second roll-up summary field should look like this:

Step 3 – Create your workflow rule

Now that we have our two “helper fields”, we can head back to Setup and search for Workflow Rules.

Create a new rule on the Parent object (for our example that is the Requisition object).

Label Carefully

Give your workflow rule a helpful label. I like to begin the label with the object to which it pertains so that it’s easier to find in the list of workflows in Setup (until the blessed day when we’ll be able to search in the list of workflows).

Evaluation Criteria

Be sure to select “created, and any time it’s edited to subsequently meet criteria” for the Evaluation Criteria. Here is a helpful article on the differences between these options.

Rule Criteria

Here is where you’ll tell the rule to fire when the two helper fields (our roll-up summary fields) equal each other. i.e. When the count of our delivered requisition line items equals the count of the total number of requisition line items on the Requisition.

Choose “formula evaluates to true” in the drop down in order to switch from criteria evaluation to formula evaluation, and then create a formula similar to the one below in the formula editor.

Now simply create and add an “Immediate Workflow Action” of the “Field Update” type.

Specify the parent object (the object that has the helper fields, Requisition for our example) as the target of the Field Update action and choose the field you want to update to a specific value. Here, we choose the “Status” field and update it to “Delivered”.

Now as soon as all the related records of a certain object (Requisition Line Items) are marked as “Delivered”, our workflow rule will automatically update the status of the parent object to “Delivered” as well!

Follow along in your inbox!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.