Filter Dynamically Based on User’s Sales Region

Control Where Products Are Sold

AW Computing started as a local business, but it has grown over time and now sells to customers in many regions. However, some products are only sold in certain regions, and AW Computing must make sure sales reps only sell products from their assigned region. One solution is to create a price book for each region that includes only those products sold in the region. Sales reps would then be trained how to choose the right price book for each sale. 

But this solution has a few issues. First, it means maintaining several similar price books, which is a lot of work. Second, a sales rep might make a mistake and choose the wrong price book. Salesforce CPQ is all about preventing these kinds of mistakes. A better way to display products by region is to use Search Filters, of course!

There are a few moving parts when using search filters in this solution. The first is to identify which products are sold only in specific regions. To save time, Team Trailhead has created a multi-select picklist on the Product object named Region and given it North, South, East, and West values. For products that can be sold anywhere, the admin leaves the field blank. But if a product is only meant to be sold in the East and West regions, the admin will add those values on the product record.

The next important part of this solution is to determine which region a sales rep is assigned to. To capture that information, create a text field on the user object named Region.

1. Click and click Setup. The Setup page opens in a new tab.
2. Click Object Manager.
3. Click User.
4. Click Fields & Relationships.
5. Click New.
6. Choose Text.
7. Click Next.
8. For Field Label, enter Region.
9. For Length, enter 100.
10. Click Next.
11. Click Next to keep field-level security as is.
12. Click Save.

Now each user can have regions. But in order for CPQ to filter products, the user’s region information must exist on the quote record. To do this, create a formula field on the quote object, which pulls their region down from the user record.

1. While still on the Setup tab, click Object Manager.
2. Click Quote.
3. Click Fields & Relationships.
4. Click New.
5. Choose Formula.
6. Click Next.
7. For Field Label, enter User Region.
8. Choose Text.
9. Click Next.
10. In the formula area, enter IF(ISBLANK($User.Region__c),";Narnia",";"+$User.Region__c).
    Trust us, we’ll explain the formula after we save the field!
11. Click Next.
12. Click Next to keep field-level security as is.
13. Click Save.

Now, about that formula. To understand why it includes both semicolons and Narnia, you first must understand how CPQ uses a multi-select picklist in the filter pane. Imagine you’re on the Product Selection page and you choose South for the Region filter.

You now see only the products that can be sold in the South at this time. You don’t see any of the products that can be sold everywhere. However, the sales rep should be able to see both region exclusive and non-exclusive products. That said, there is a way to show both: select the --None-- value in addition to the other values.

As a human, you can do that with a command- or control-click. But to tell CPQ how to choose multiple values, you must use semicolons.

So, back to the formula—if the sales rep is in the South region, the resulting formula is ;South. This text represents two values. The left (blank) side of the semicolon represents the --None-- value, the right side is the exclusive region.

So why is Narnia part of the formula? Narnia, C.S. Lewis’s mythical land, stands in here for when the user doesn’t have an assigned region. If we don’t give CPQ anything for the Region filter, no filter is applied, so it shows every product, even the region-exclusive ones. Instead, we give it ;Narnia, which makes it show all the products exclusive to Narnia (none, hopefully) and every nonexclusive product. It’s safe to assume AW Computing won’t be selling in Narnia any time soon, so we can use it as a bogus value.

At this point, the user’s region exists on the quote record. Next, we need to update the Filter Source field so that CPQ can use the new User_Region__c quote field for filtering.

1. While still on the Setup tab, click Object Manager.
2. Click Search Filter.
3. Click Fields & Relationships.
4. Click Filter Source Field.
   The Filter Source Field tells CPQ where it can find the data it should use for filtering. In this case, you’ll point it to your new formula field.
5. In the Values list, click New.
6. Enter User_Region__c.
7. Click Save.
8. In the Picklist Options section, for Controlling Field, click Change.
   
9. Double-click User_Region__c.
10. Click Save.

Close the Setup tab to return to the Salesforce CPQ tab. You’re ready for the final step: Create the actual search filter record.

1. In the navigation bar, click Search Filters.
2. Click New.
3. For Filter Name, enter Region.
4. For Target Object, choose Product.
5. For Target Field, choose Region__c.
   Team Trailhead pre-added this value to your org to save time.
6. For Operator, choose equals.
7. For Filter Source Object, choose Quote.
8. For Filter Source Field, choose User_Region__c.
   Now CPQ knows where to find dynamic filter values.
9. For Display Order, enter 1.
10. Check Hidden.
11. Click Save.

Let’s take a moment to review all the moving parts of the solution.

• The Region multi-select picklist on the product record identifies region-exclusive products.
• You created a Region field on the user object to capture the sales rep’s region.
• You created a Region formula on the quote object to pull and format the user’s region information.
• You created a search filter that compares the product region with the formatted region, and made it hidden.

Let’s test the solution. Start by making a few products region-specific.

1. In the navigation bar, click Products, then choose the All Products list view.
2. For the 5G Antenna product, click Show Actions then click Edit.
3. For Region, select East and West.
4. Click Save.
5. For the Satellite Receiver product, click Show Actions then click Edit.
6. For Region, select South.
7. Click Save.

That should be enough to test. Let’s first make sure the region-exclusive products don’t appear when a user has a blank Region.

1. In the navigation bar, click Quotes.
2. Click Q-00054.
   Notice that the user region shows ;Narnia. If you don’t see the User Region field, refresh the page with your browser’s refresh button.
3. Click Edit Lines.
4. Click Add Products.

At this point, you should see neither the 5G Antenna nor the Satellite Receiver. The nonexclusive products are all still there to choose from. Also, if you open the filter pane you shouldn’t see the Region field among the other filters. But can we get your user to see exclusive products? Let’s try!

1. Click and click Setup. The Setup page opens in a new tab.
2. In the Quick Find box, enter Users.
3. Click Users.
4. Click Edit for your user, the one with the System Administrator profile.
5. For Region, enter West.
6. Click Save.

You can close the Setup tab to return to the Salesforce CPQ tab. You might still be on the Product Selection page, but it’s out of date, so you need to reload the quote to test properly.

1. In the navigation bar, click Quotes.
2. Click Q-00054.
   Notice that the Region field now shows ;West.
3. Click Edit Lines.
4. Click Add Products.

The 5G Antenna product, which is exclusive to the West and East regions now appears in the list of products, along with every nonexclusive product. Well done! What you won’t find is the Satellite Receiver product, since that’s exclusive to the South region.

This solution to the exclusivity problem took some work to set up. But in the future you will only need to maintain regional information on special products and user records. If you leave a product’s region blank, it’s treated as nonexclusive. If you leave a user’s region blank, they will see only nonexclusive products. 

As a bonus, allow a single user access to multiple regions by using a semicolon to separate values. For example West;South in a user’s Region field would work.

In the next unit, you learn how to create a button on the Quote Line Editor so that users can get a filtered list of products with just one click.