As most of my experience in Salesforce is in the Non-Profit sector, it’s refreshing to have a client that is in for-profit, because it’s making me stronger in some key areas of Salesforce such as

• Products
• Price Books
• Price Book Entries
• Quotes
• Quote Line Items
• Opportunity Products
• Contracts
• Orders
• Order Line Items
• Salesforce CPQ

As well as developing formulas that use custom metadata to do conversions among dimensions and units, as well as store some universal cost per weight values used downstream in commerce calculations. It’s making me feel pretty adult.

Modern Mill

is a subsidiary of a lumber company. One that’s thinking ahead about the scarcity of hardwoods and their ecological importance to fighting climate change. ACRE (product tech sheet here) is the name for their rice-hull substrate wood substitute. ACRE is waterproof, durable, has wood’s emotional warmth & beauty and is versatile — a sensible choice across a wide array of applications.

• 

• 

• 

• 

• 

• 

• 

They don’t sell to end consumers. The model is firmly B2B and targets contractors, home builders, commercial interior/exterior builders, lumber yards, retailers, distributors and furniture OEMs as well as plastics wholesalers. In short, the channel structure is complex – and this complexity is addressed through price books with specific target gross margins. Before migrating to Salesforce, they used “customer type” as the rubric to unite the cost basis & configuration possibilities unified under a single target margin on a single tab of a complex Excel pricing workbook.

What was complex and almost overwhelming in Excel (remember: this is one tab of 12)….

Is elegantly expressed in Salesforce through 12 Price Books and a Product2 table with 33 standard Products.

• 

• 

Most Accounts have a fixed price book restriction/assignment that doesn’t change and is set upon creating the prospect account. That said, there are a few OEM Wholesale accounts where with a subset of products where the margin to which they are entitled is a function of annual volume, with larger volumes being rewarded with lower margins and therefore prices.

The allocation of Price Book Entries between standard and wholesaler price books is graphically represented below.

Product2 Table Customizations

Dimensions determine everything. Initially in porting the excel data into Salesforce there was a lot of back and forth looking at cells, mentally mapping what the value represented, and then creating a field to hold an attribute or a calculated value. With guidance from the client, I iterated towards simply entering the dimensions as values — with all other fields as downstream calculations, converting between imperial and metric units, calculating planar surface areas (in various permutations), volume and by density (a universal attribute of ACRE as a product) to mass, and then using a standard cost per gram material, I was able to develop unit costs — and add milling costs to those standard products where milling isn’t optional.

All the conversion ratios were the picture perfect use case for custom metadata which made the formulas more robust (and especially for non-standard density product families, a possibility going forward).

Custom Metadata Types and Records

• 

• 

• 

• 

• 

Simply put: by inputting only thickness, width and length, I could get a very precise cost basis. I needed to – and did – tare care to create all fields with 5 significant digits of precision to minimize the scale and impact of any rounding errors (which had we simply copied and pasted values from excel, where many digits were reduced to $Y.ZA would have been discomfittingly high.)

Using MS Access and the Salesforce Connector to Populate Price Book Entries for Active-, Non-Standard-Price-Books

The Product2 objects is clearly important. (When did that version 2 get written in? What was wrong with the first instance?) The Price Book and Price Book Entry objects are clearly in something of a special class — if not literally a class by themselves. Aside from the odd ways the user interface functions, and the way it’s virtually impossible to get a price book entry record displayed on its own layout in the user interface, I find this fact revealing:

Neither the Price Book nor Price Book Entry can be a record that triggers Process Builder

See for yourself!

Nor can you use them in one of Spring 2020’s nifty advances in Flow: upon record create triggers.

Office 365 MS Access to the Rescue!

MS Access has a OCDB driver that grants bi-directional access to live Salesforce data, and I use it to do data transformation/enhancements. The write function is a bit slow, so if it’s hundreds or thousands of rows I’m inserting (via an append query) or updating (via an update query) or deleteng (via a delete query), it may be faster to export the dataset as a csv and then use Apex Data Loader to get it into SF. Still, for accuracy and set it up and let it do its thing, ease — the Access Salesforce data connector is hard to beat. (Though arguably CData’s OCDB driver is faster, it’s also an additional subscription in the hundreds annually).

In RDBS like Microsoft Access, if you cross a query or table with N rows with one of M rows without specifying a keyed relationship that precisely defines the relationship between the two entities, you’ll get an output with (N x M rows). That’s useful information because for the 5 non-wholesale, non-Standard Price Books, for each I want 1 Price Book Entry for each of the 37 active prices gets me a tabular data set with 185 rows, containing the bare minimum of data to append to the Price Book Entry Table:

• Price Book ID
• Product2 ID
• Price Book Entry Price
• Unit Cost
• Is Milled
• PBE Is Active

But sometimes is just simpler and faster to populate Price Books with Price Book Entries via Data Loader

Don’t even think about using the User Interface, what with the save time and the Lightning movie. All that time consumed — times 185.

Our Sales Quote Template – Branding, Custom Fields & Respecting Authority

Challenge: preventing the Sales Rep from accidentally emailing to a customer (or going rogue with respect to policy and deliberately emailing) a Sales Quote that hasn’t been approved or looked over?

Solution: record types and an approval process that changes the Quote record type to one whose user interface supports the Create PDF button.

Here’s what our quote template with branding looks like.

Automations

By far the niftiest of the flow automation’s I’ve created for this project is the one that occurs when the Sales Quote is returned signed from the customer.

When the rep changes the stage and saves the record, process builder fires and calls this flow, which does the following things

• Looks up the Quote’s parent Opportunity.
• Creates a custom notification recipient collection of the SysAdmin & Opportunity Owner
• Gets the Primary Contact on the Opportunity
• Creates a Collection of the Opportunity Products on the Opportunity
• Creates a Draft Contract
• Creates a Draft Order on the Contract
• Notifies the admin of the collection sizes and AOK status so far
• Updates thisOpportunity with the contractID
• Loops through the Opportunity Product collection and copies each line item to the new Draft Order as Order Line Items
• Inserts the Order Line Item Collection.

Here’s the screenshot

After that, this Process Builder automation seems almost prosaic. All the Sales Rep need fill out on the new quote modal is the Quote Name and the Quote To. We set a quote expiry term of 45 days out. And look up the recipient’s email and phone and populate those fields accordingly.

Back to Flow’s Power of Iteration

This next automation is also rather nifty. As I’ve observed before, Price Book Entries are an odd object that it’s hard to get your hands on directly. Adding an (active) product from a price book (i.e. a Price Book Entry) to an opportunity, a Sales Quote, or an Order brings up a modal window that isn’t too easy to customize. Rumor had it on Trailblazer Success Community that field lists would do the trick, but on the one time I followed that recipe, it did not yield the hoped for results. So, the staff at Modern Mill are used to seeing the products table grouped by product family in a familiar but arbitrary sequence of families. To speed and ease the rep when adding products, we wanted to reproduce that same order. Initially, we created a custom field Sort Order but through trial and error decided that both the Product Code and Product SKU fields would store their own copy of that order.

Because there’s clearly automation going on in the background, this seemed advisable. Still, it was easy enough to see a time when we might need to tweak the display order — if you do that at the product level, it’s all very well and nice. But the Price Book Entries would only automatically be updated on the Product Code instance of the order. Not the target margin custom field I had created, nor the unit cost basis field which together define precisely the pricebook entry for the product.;

So, I created this flow that triggers off a Product SKU value change in process builder to take all of that products’ Price Book Entries (active or inactive) and write the new Product SKU sort order key to those values.

Flow Automation in Anticipation of User Laziness

Some users are fastidious. Others are well, just lazy. To be fair to users, who are not a monolithic mass but clusters of different attributes, Tthe Add Product functionality in Salesforce is at best ungainly. At least in non-CPQ orgs.

When adding product line items to an opportunity, quote or order, the over-determined modal window pops up, insisting upon quantity and less strict about date or line item description. See below.

And quick – even advanced admins: controlling which columns display on the modal is far from straightforward. The one solution I read about I could not get to work as advertised. Go figure.

Fortunately Flow and the new (!$Record.} functionality in #Spring2020 let’s us plan for those whose approach is shall we say minimalist — and we can remedy those deficiencies to keep data hygiene levels high!

Use the upon record create (for the line item) trigger, in many cases we can infer or set defaults so that calculated values work properly. We can create a bunch of non-correlated nodes that test for a null value – and adds one where a user omitted/declined/forgot to do so.

Next Steps with this Client

Include

• Integrating QuickBooks (on premises) via Konnex or DB Sync for invoicing.
• Adding quote custom fields that enable the rep to adjust the quote-level or quote-line-item level discount and see the potential impact on the commission.
• Bundling Acre products with finishes and coatings measured in gallons but sold in square feet covered.
• Exploring the feasibility of Salesforce CPQ licenses.
• Developing Trailhead training standards by user role and curating curricula

Of course part of me hopes I can persuade them that CPQ is the answer. Time will tell.