SFDC: How To

How to create filtered Opportunity Related Lists based on the Opportunity Stage?

How to create filtered Opportunity Related Lists based on the Opportunity Stage?

Have your users ever asked for a way to filter related lists or have multiple related lists that separate records based on specific criteria. A similar idea how Open Activities and Activity History related lists separate Task records based on their status.

A request I got this time was to create filtered related lists based on the Opportunity Stages. Business wanted to separate Open Opportunities from Closed Won Opportunities and Closed Lost Opportunities for a quick and easy overview of the records. This is very essential requirement specially for event, ticketing and entertainment companies that create a lot of reservations and bookings for corporate clients and they want to see at a quick glance what is happening with this Account and if they have any Open Opportunities and what could they suggest to the client based on their past bookings.

Unfortunately, at this moment and time, Salesforce currently does not natively support filtering related lists.

There are two big ideas on the Idea Exchange requesting this very feature and are tentatively on the road map (safe harbor):

In my post I’m going to show you how to setup multiple Opportunities related lists you can add to the Account page layout and each related list will filter the Account’s Opportunities differently. This solution involves custom lookup fields and process builder.

Lookup Fields

This solution for multiple related lists exploits the fact that every time you add a new lookup field on an object then the object the field references gets another related list showing those child records.

In our Opportunity scenario, we will be creating 3 Lookup fields on the Opportunity object.

Create Lookup field on Opportunities object

Create Lookup field on Opportunities object

Select “Related To: Account“. This will make sure that new Opportunities Related List will appear for Account object.

Select “Related To: Account“. This will make sure that new Opportunities Related List will appear for Account object.

Create a name for your Lookup field. And add a Description so employees internally know the purpose of this lookup and do not delete it.

Create a name for your Lookup field. And add a Description so employees internally know the purpose of this lookup and do not delete it.

This is an example how you name name your Opportunity Lookup field.

This is an example how you name name your Opportunity Lookup field.

Open Opportunity Lookup

Open Opportunity Lookup

Closed Won Opportunity Lookup

Closed Won Opportunity Lookup

Closed Lost Opportunity Lookup

Closed Lost Opportunity Lookup

Process Builder

Next we will need to create a new automation using Process Builder feature. We will be setting and clearing of these three new lookup fields. Whenever Opportunity Stage will change one of these lookup field values in the background will be set using our Process Builder. And on the Account page layout those Opportunities will appear in the related list associated with that lookup field (Open, Closed or Lost). When the Opportunity Stage will change Opportunity record will re-evaluate the logic and make sure Opportunity appears in correct related list.

This is how your Process Builder would look like.

This is how your Process Builder would look like.

First, we will start by choosing Opportunity Object.

Process_Builder_Opportunity_Object_ParetoPi.png

Second, we will need to create a rule that will include all Open Opportunity Stage picklist values.

Open_Opportunity_Settings_ParetoPi.png

And setup an Action: Update Record.

Open_Opportunity_Update_record_ParetoPi.png

For “Open Opportunity“ Lookup you will reference the same Opportunities Account ID field.

Opportunity_Account_ID.png
Opportunity_Lookup_Account_ID.png

For the other two Lookups you will use type “Global Constant” and select “$GlobalConstant.Null“ option from the picklist.

Screen Shot 2019-01-27 at 14.28.38.png

Now you just need to repeat the same process for Closed and Lost Lookup (or however you have decided to call your fields).

Once you have completed building your Process Builder make sure to create a Test Account and Create multiple Opportunities with different Stages to test the behaviour and make sure they all appear in the correct Related List.

Mass Record Update

The ugly part of this mini-project. Now when we have created and tested the solution to make sure all works as expected, we need to make sure that all current Accounts have their Opportunities displayed in the correct sections on the Account’s Related List.

Account_Page_Layout_Opportunity_Related_Lists.png

Unfortunately, this will not gone happen automatically, as remember, we need to physically populate newly created Account Lookup fields for the Process Builder to place these Opportunities ir corresponding Related Lists.

So what we need to do?
1. We need to go to Reports >
2. Create New Opportunity Report with Opportunity ID, Account ID, Stage, Open Lookup, Closed Lookup, Lost Lookup fields >
3. We then need to export this *csv file and upload it to Google Excel >
4. Wr need to set a filter for each stage and copy based Account IDs into related Opportunity Lookup >
5. Once you have completed the data manipulation process >
6. You need to disable all Opportunity Validation Rules and required fields
7. Use Salesforce Data Loader to mass update all Opportunities
8. Review multiple Accounts to make sure all Opportunities appear in the correct Related List
9. Activate back all Validation Rules and Required fields
10. Remove default All Opportunities Related List (and keep only your new Related Lists)

LAST STEP, to achieve the final solution is to create a“New Opportunity” Custom Button for our Custom Related Lists. The reason we need to do this is because one, the “New Opportunity” button on the custom related lists strips the automatic pre-filling of the the account name value ( the lookup field) on the new custom related list. So to keep the original flow we need to create a new custom button so the Account the user is on while clicking on the “New Opportunity“ field on the relate list is automatically populated.
Please use the below image to replicate button for your org.

 
New Opportunity Button Pareto Pi.png
 

Don't forget - entering the description is a key to long term success for all people that will be coming after you. :)

If you managed to follow the whole setup —> CONGRATS. I hope you are happy with your improved Opportunity Related Lists and your employees are massively happy for the easy overview. :)

Kristina AlexandraComment