How To: Use Flow to Report on the Most Recent Record

This month is quickly becoming Flow February here at Salesforce Weekly. Following Mike’s examples of how you can build (and improve) a flow to create your own rollup summary, here we are going to tackle another relatively common requirement – the ability to include only the most recent child record in a report – and see how flow can help.

Note: A second iteration of this post, showing how the below flow can be simplified and also highlighting a more complex extended use case has now been posted here. If you’re looking for a quick-start, check out the new post; if you’re here to dive deeper and learn more about the different elements in flows and how they are built, read on.

The use case is this: you want to find out some information about the most recent child record, perhaps to report on the last activity on an open opportunity, or maybe to show details of an account’s latest case.

Usually you have two options here.

You can use a rollup summary (either a regular one, one built with an app, or one like Mike’s example above) to roll one value – usually a date – from the most recent child up to the parent. For example, this means you can run a regular Accounts report and see for each account what the Close Date of the most recent won opportunity was. But you can’t get any other info. What if you wanted to know the Amount of that opportunity?

The alternative is to use a Parent with Children report type which allows you to use any fields from the child records. But there is no way of only returning one child record for each parent – the best you can do really is try to find a date range that is likely to only return one, or more commonly pull all children into the report and ignore the lines you don’t need. Messy.

So how can flow help us overcome this? Well, we create a new custom checkbox field on the child object – Is Most Recent Record – and we build a flow that detects new child records being created and maintains this field to ensure only one record has it marked as true at any one time.

To help us step through the building of our solution, we’re going to use this real-world example from the Success Community:

Here is a preview of what our finished flow will look like and what it will do:

Now we will examine each of those flow elements in turn and explain what it is doing. Sit tight; building flows can be a wild ride!

Preparing to Build Your Flow

Visual Workflow, to give the feature its full title, is only available in orgs running Enterprise Edition or higher. If you haven’t built a flow before, this guide will be essential reading. When you’re ready to get started piecing this flow together, navigate to Setup > Create > Workflow & Approvals > Flows, then click New Flow.

Building Your Flow

Here we will walk through the configuration of each of our flow’s nine elements.

1) Fast Lookup: LookupCase – This element takes in a variable for the Case ID, which we’ll pass in using the Process Builder later. It then simply looks up that case and stores it (and some of its fields, including your new checkbox field) into an sObject variable, which we’re calling LatestCase, for use later in the flow. When creating your varCase variable, ensure it is set to Input Only.

2) Assignment: UpdateCase – This element simply changes the value of the custom checkbox field we created to true for the case record being passed into the flow.

3) Fast Lookup: LookupAccount – This retrieves the parent Account of the Case and stores it into an sObject variable which we’ve called TheAccount. I believe this part is actually an optional step (and probably unnecessary) but it helped me when troubleshooting this flow and it perhaps makes the logic of the flow slightly easier to follow.

4) Fast Lookup: LookupOtherCases – This retrieves all other cases related to TheAccount (being sure to filter out the case which triggered the flow, the one we want to be ‘true’) and stores them into an sObject collection, OtherCases, ready to loop through in a moment. Be sure to store the Latest Record checkbox field into the collection variable as shown.

 

5) Loop: LoopThroughOtherCases – This arranges all the other cases we just found and begins looping through each of them in turn as a new sObject variable, which we’ve called OneOtherCase.

 

6) Assignment: UpdateOneOtherCase – This takes the current iteration of our loop and updates the checkbox on that case to false.

 

7) Assignment: AddOneOtherCaseToCollection – Having updated the checkbox to false, this element moves the case into a new collection, OtherCasesToUpdate, ready for mass-updating at the end of the flow. We use the ‘add’ operator to do this.

 

8) Fast Update: UpdateOtherCases – Once all the cases are looped through, we exit the loop and perform a mass update on the OtherCasesToUpdate collection we created. This updates all other cases on our account to ‘false’.

9) Fast Update: UpdateLatestCase – Very similar to the last step, except this one updates only our LatestCase Object, committing its ‘true’ value to the database.

 

Completing and Launching Your Flow

With all your elements now on the canvas, connect them together as shown in the overview image and select your first element – LookupCase – as the start element. Save, close and activate your flow.

Now we need to make use of our newest ally, the Lightning Process Builder, to build a simple process which detects a new case being created and passes that case’s ID into our flow, causing it to execute the rest of the actions we need.

To begin creating your process, navigate to Setup > Create > Workflow & Approvals > Processes (or Process Builder), and click New Process.This is what the finished process is going to look like – and it won’t take us long to get there so let’s walk through the steps:

1) Click on Add Object. In the right hand pane, type in Case, select it, leave the other option as ‘created’ and click Save. (If your use case was slightly different here you may want to choose ‘created and edited’ instead – for example if you wanted to store the most recently closed opportunity.)

 

2) In the diamond-shaped Add Criteria node, select No Criteria and save.  (If your use case was different you may want to set up some specific criteria.)

3) Under Immediate Actions, click Add An Action, select Launch A Flow, select the flow you just created and then pass in the Case ID into your starting variable like this:

Now simply save and activate the process, and you are done!

You now have a new field on your child object that can be used in report filters, and a process and flow to keep the field up to date.

Reminder: If you want to see how this flow can be simplified, or even how it can be built out to show the Most Recent 5 Items or the Top 5 Items ordered by your chosen metric, check out the second iteration of this article.