Generating Random Numbers in Flows and Formulas

Generating Random Numbers Using an Invocable Method

I’ve come across a handful of use-cases where I needed to generate random numbers in flows for things like random cohorting. I’m taking this as an opportunity to continue working on some “Admin Tools” scripts which would have saved me some time over the years.

The Class and Test Class above create an invocable method which can be used inside a flow to generate a random number within a range, and a specified number of decimal places.

Within the flow builder, the invocable looks like this:

You can enter a Lower Bound, which is the smallest number that the invocable will generate.

The Upper Bound is the largest number that the invocable will generate.

Scale is the number of decimal places.

For some examples of how this works:

Example 1:

  • Lower Bound = 1
  • Upper Bound = 10
  • Scale = 0
  • Result = 1, 2, 3, …, 8, 9, 10

Example 2:

  • Lower Bound = 1
  • Upper Bound = 10
  • Scale = 1
  • Result = 1.0, 1.1, 1.2, 1.3, … , 9.8, 9.9, 10.0

Example 3:

  • Lower Bound = 1
  • Upper Bound = 100
  • Scale = -1
  • Result = 10, 20, 30, …, 80, 90, 100

Generating Random Numbers using Formulas

Sometimes using an invocable method is overkill and you just need something that appears random and doesn’t need to be particularly performant. For that, you can use a formula to generate a random number.

Taking inspiration from old random number generators which used nth digits of pi, I’ve written this formula which uses the same principle, and uses the record id and created dates as seeds to ensure uniqueness. Because these fields will exist on ALL records this solution is more portable than other random number generator formulas you might find on google.

If you need to change the number of returned values, you can simply change the number within the outer-most RIGHT() formula.

You’ll notice some ASCII(RIGHT(Id, #)) formulas being added together. This is because we expect these IDs to increment when the record is created, resulting in uniqueness across records, even those that are created in bulk. The ASCII formula converts the first text value in the string to a number. We don’t so much care what the result is, so much as that it translates letter values to numbers (which we can do math on!). The fact that it is case-sensitive is a nice bonus that enhances the “randomness”.

Note: The ASCII formula only processes the first character in the string, we’re using 3 of these in a row, grabbing 1 additional character each time. Since the formula only operates on the first character, it simply acts as though we’re grabbing the last 3 characters and performing some math on their ASCII value.

Deleting Work Items From Salesforce DevOps Center

Deleting Work Items From Salesforce DevOps Center

This past week we needed to delete a stuck work item from Salesforce DevOps Center. I found an answer online in some Salesforce comments, but the solution was either incomplete or dated. I’ve updated the script and added it here to help anyone else in the same situation!

Thank you to Shreyas Pawar for his comment on the Trailblazer Community Forums for his response here, which got me the information I needed to get started:

The Basics of Using PREVGROUPVAL

This post covers the basics of using the PREVGROUPVAL summary formulas in reports, and how the requirements change in matrix reports.

The PREVGROUPVAL function in Salesforce is fortunately a little easier to understand than its sibling, PARENTGROUPVAL. If you haven’t read that article yet and don’t yet understand Summary Formulas yet, I recommend starting there for a primer and then coming back!

Basic PREVGROUPVAL Summary Formula (Previous Quarter’s Total Opportunity Amount)

We’ll start here with a report that simply demonstrates how the PREVGROUPVAL formula works, and then we’ll build off that to show something more interesting in useful. What we’re building here simply displays the total Opportunity amount from the previous Opportunity group.

Let’s start first with a very basic Opportunity Report, with a configuration something like this:

Report Builder Sidebar (Outline tab) from a Salesforce Opportunity report, with 'Fiscal Period' field in the Group Rows section and 'Account Name', 'Opportunity Name', 'Stage', 'Close Date', and 'Amount' in the Columns section
Group Rows: Fiscal Period
Columns: Account Name, Opportunity Name, Stage, Close Date, Amount

Then we’ll create a Summary Formula.

Click the Caret next to Columns, then click Add Summary Formula

Selecting the 'Add Summary Formula' option from the caret menu in the Columns section of the Report Builder sidebar on the Outline tab

Let’s add a new Summary Formula which looks like this:

Let’s name our column Previous Quarter Total, set the output type to Currency, and Decimal Points to 2.

For the Formula, just put the following code, then click Validate:


This sums the Amount field within the previous group, and then displays it.

When you try to hit Save or Validate, you’ll see a warning like this:

You must select a grouping context to use any report summary function.

To fix this, go into the Display tab, set the formula to apply to Specific Groups, and then in this case, Row Group should automatically be set to Fiscal Period

On the Display tab
Where should this formula be applied?: 'Specific Groups'
Row Group: 'Fiscal Period'

Now we can click Apply.

Now let’s do one more thing before reviewing our work:

Clicking the Caret next to Amount reveals additional field options in the report.  Choosing Summarize, and then Sum adds data to the subtotal rows in the report that adds the value of that column together at each grouping level.

Go up to the Amount field, click the caret, hover over Summarize, and click Sum.

Now let’s take a look:

Report showing the total $ Amount for each Fiscal Quarter, and our Summary Formula displaying the previous Quarter's total $ Amount.

Notice how the the Previous Quarter Total field for Q1-2023 contains the same value as the Amount Subtotal for Q4-2022. Likewise the Previous Quarter Total field for Q4-2022 contains the same value as the Amount Subtotal for Q3-2022. This is how PREVGROUPVAL works: It allows us to compare our summary in the current grouping to a previous grouping in our report.

1 more thing to check out: You see that the Previous Quarter Total field for Q3-2022 is blank? That’s because there’s no previous group to compare against. Our report only contains data back to Q3-2022, so there’s no Q2-2022 to compare with.

How to Skip Groups

It’s not uncommon to need to skip groups when you’re summarizing data. Maybe you want to compare this fiscal quarter against the same one last year? Or the number of text messages sent each day versus the same day 1 week ago. Fortunately both of these use cases are supported, and pretty simply at that!

Let’s re-open the Previous Quarter Total summary formula again by double-clicking it, and we’ll make 1 small change:

Updating the Previous Quarter Total summary formula to add a 3rd parameter, which specifies how far back PREVGROUPVAL should look.

See the change? We added a new parameter, and set the value 2:


Go ahead and click Apply.

Report view showing that by setting a value of 2 in the 3rd parameter of PREVGROUPVAL, we we're now showing the total $ Amount from 2 quarters prior instead of 1

You can see now that Previous Quarter Total field for Q1-2023 now matches the Amount Subtotal for Q3-2022. It skipped Q4-2022 entirely! If we could fit Q2-2023 on-screen, we’d see it now reflecting the value in the Amount Subtotal for Q4-2022.

Additionally, notice that the values for Previous Quarter Total field for both Q3-2022 and Q4-2022 are blank. Once again there’s no data available to compare against, so the values are blank.

Basic PREVGROUPVAL Summary Formula (% Change in Amount by Quarter)

Let’s move on to something a little more useful. Let’s take compare opportunity amounts by quarter to see how they change:

We're updating the field name to '% Change From Last Quarter' and updating the formula to:

We’ll first change the Column Name to something like % Change From Last Quarter, then change the Formula Output Type to Percent. Then we’ll enter this as our formula:


AMOUNT:SUM represents the current period’s total Amount

PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER) represents the previous period’s total Amount

So written more simply, we’re calculating the following:


This should give us a result where from quarter to quarter, if the total mount increases, we see a positive value representing how much of an increase we see. Likewise if the total amount decreases from quarter to quarter, we should see a negative value representing how much the total amount went down.

Click Apply.

Report view showing that the '% Change From Last Quarter' field now describes the percentage increase from the previous quarter, such as an increase from $865,000.00 to $1,495,000 being represented as 72.83%

You should now see that the % Change From Last Quarter field reflects the percentage change from period to period.

We also see decreases from quarter to quarter handled intuitively:

Another example from the report which demonstrates how the report handles decreases Quarter-over-Quarter as negative numbers, such as $1,495,000 to $1,430,000 being represented as -4.35%

Handling Multiple Group Rows

PREVGROUPVAL fortunately handles multiple row groupings fairly intuitively. If you have a summary formula for Fiscal Period and add another row grouping beneath it for Account Name, it handles this without issue.

The situation you do need to be mindful of is if you add a grouping level above a grouping level for an existing summary formula. This splits up your records and means that instead of your formula representing the change from 1 quarter to the next for all accounts, it would now represent the change from 1 quarter for a given account, to the next quarter for the same account:

This is probably not what we want. So just be mindful that Salesforce won’t throw an error if you were to make a change like this, even though it changes the meaning of the data.

Basic PREVGROUPVAL Summary Formula (…, Matrix Report)

Let’s up the ante one more time and look at using PREVGROUPVAL in a Matrix report. Let’s go ahead and move Account Name down into the Group Columns:

In the Report Outline sidebar, we're moving 'Account Name' down from Group Rows into Group Columns

By converting this to a matrix report, we pack a LOT of data into a small space and once again our formula works! Just like in the double-group-rows example, the % Change From Previous Quarter field is demonstrating the % change for a given account instead of the quarter as a whole.:

Matrix Report demonstrating how the '% Change From Previous Quarter' field now represents the % change in Amount for a given Account.  Accounts with consecutive $0 quarters may display a '#Error!'

Do note however that if you move Fiscal Period down to the Group Columns section instead, that % Change From Previous Quarter disappears:

When we try to move our existing 'Fiscal Period' Row Group down to a Column Group, our summary formula is removed since technically its configuration is no longer valid.

If you try to add it back, you’ll notice something different:

The 'Display' tab of the 'Summary Formula Editor' now gives an extra selector for 'Column Group'.  Both of the 'Row Group' and 'Column Group' selectors default to 'Grand Total Only'

When you try to set formula groups on the Display tab, you now have options for both Row Group and Column Group. Respectively, Row Group and Column Group each only provide options for Grand Total Only and whichever fields you have in the Group Rows and Group Columns.

If we Select Grand Total Only for Row Group and Fiscal Period for Column Group

Updating the 'Display' tab of the 'Summary Formula Editor' such that 'Row Group' is set to 'Grand Total Only' and 'Column Group' is set to 'Fiscal Period'.

… and hit apply, we get… an interesting result:

Report sample demonstrating that while '% Change From Previous Quarter' appears in each row, it is only populated with data in the 'Total' row.  It now represents the change across all accounts.

% Change From Previous Quarter is back, but only in the Total row. Notice that these results match what we were seeing in the second example, where we only had 1 grouping level, and it was telling us the % change for all accounts by quarter.

What happens if we change Row Group to Account Name?:

Report sample demonstrating how '% Change From Previous Quarter' now populates in each row and represents the change from quarter to quarter for a given Account.

We’ve now lost the total-level % change, but now have % Change From Previous Quarter grouping by Account Name again.

We can actually still have both versions in the the report, we just have to create another Summary Formula Column with the same formula, but with different Row Groups (just make sure to name them intuitively)

Report Example demonstrating that by having 2 '% Change From Previous Quarter' fields that utilize different Row Group selections, we can display both the change from quarter to quarter by account, as well as across all accounts.

That’s it! You now understand the core concepts around working with the PREVGROUPVAL formula in reporting. If you’d like more practice, I recommend working through this exercise on Trailhead:


or for an extra challenge, take on the Lightning Experience Reports & Dashboards Specialist Superbadge (what a mouthful!).

Lastly, if you haven’t already, check out my other article on The Basics of Using PARENTGROUPVAL.

Help Me! I’m at a Startup and I’m Overwhelmed!

Questions popup all the time about what to do when you’re overwhelmed at a startup. There’s no project management, everything is an emergency, there’s no detail on any requests, and there’s no time to ask questions. It’s awful to be a part of, and it feels like things are out of control. Often times though, things are out of control, but they don’t have to be. I’ve had a few adventures with startups, and it turns out that most of the time things are out of control because people don’t understand how to push back and how to take command. This article describes my process, and was a response to another Salesforce Admin who felt overwhelmed and posted about it online. I hope it helps you if you’re in the same place as he.

1. Find a Friend

Find someone you can talk to, who understands you, even if they don’t understand the problem. This person is your opportunity to not only get your frustrations off your chest, but also to practice communicating issues effectively and in a way that lay persons can understand.

Focus on 3 points:

  1. Without placing blame, what is the problem?
  2. What is the impact on the business? Think dollars, risk, and scalability.
  3. How do we fix it, and what is the cost to do so?

You can do this.

2. Document Requests

Start using a project management tool. Jira and Asana are free for up to 10/15 users on a team, I believe. Creating tickets for every request sucks, but what’s worse is doing 50 hours of work and only being able to account for 30 hours of it. Asana is easy so I’d probably start there. It also integrates with Slack so you can convert a message directly to a ticket and notifies them automatically when the ticket is completed.

You can do this.

3. Don’t Determine Priority by Yourself

Startups are rife with manufactured urgency. I was once told by a C-level that I would be replaced by someone from Fivver if I didn’t produce a chatbot within 3 business days. It took 8 months and half-a-million dollars for the contractors to build the bot, which I later built for a hack-a-thon in 12 hours. I outlasted her.

Learn how to tell people “no”. When most people tell you the deadline is tonight or tomorrow, what they really mean is that they have other priorities which would prevent them from looking at it until then. That doesn’t mean it actually has to be completed at that point.

If for some strange reason it does need to be, then you should ask them to tell you what of your active projects should now be deprioritized to make room for it.

If they have no active projects to deprioritize, point them toward the owner of your current working projects and ask them to negotiate between themselves. If they won’t do that, then it’s clearly not a priority.

In my experience, 95% of these scenarios take care of themselves by this point. Remember, you’re not the one responsible for assessing priorities between requests from other teams.

You can do this.

4. Plan With Stakeholders Around Weekly Capacity

By this point you should be well-versed on your own capacity. If not, it’ll come to you quickly as you take on projects which are more or less involved than you thought. Now you start hosting stand-ups with your primary request-givers. Once per week for 15-30 minutes is a good place to start. Go through the backlog of tickets you haven’t completed, then ask about any new requests for the week, and ask them to prioritize them with you live. Once you hit 80% of your capacity for the week, stop. You need the extra 20% for ticket writing, breakfixes, misjudged capacity, and mental health.

Congratulations on your first capacity planning meeting.

You can do this.

5. Periodically Review Old, Open Requests

At 3 months, host a slightly longer session where you go through the backlog and review requests which are at least 2 months old and determine whether these project should be kept in the backlog or canceled.

Make sure to include a note on why canceled items were canceled. A number of them will be due to changes in business priorities. Those weren’t a high priority and now the business doesn’t care about them. Aren’t you glad you didn’t work through lunch on something no one cared about?

You can do this.

6. Build the Case for More Resources

Armed with this information about what you accomplished, what you couldn’t, and how quickly the backlog is growing, now is your first opportunity to plead your case for additional resourcing.

Use the skills you acquired speaking with your friend to outline the opportunity cost of those backlog items not being completed. Talk about the business risk of not completing compliance-oriented items. And talk about the optics of running a platform which the business may perceive as slow to iterate on because of a lack of resourcing. I have met only one manager who didn’t care about the latter, and I am now happily eating his lunch.

Congratulations on your first resourcing negotiation. You may not get more resources, but if you have gotten this far, you should be far less overwhelmed.

You can do this.

7. Plan Ahead With Your Business Partners

Now that you have a whole quarter of work documented, host a meeting with your big ticket givers to talk about their goals for the quarter. Now is your opportunity to level-set on what they want to do in the next 13 weeks, and to recommend specific approaches to business priorities. It also allows you to visualize the whole pipeline and work out dependencies in advance and prioritize that work ahead of the work that depends on it.

By this point you should hopefully be under-provisioned in part because you have a properly groomed pipeline and because you’re 3 months better at your job.

8. Don’t Be a Hero. Budget Your Time for the Future

Take on others’ additional work sparingly and instead focus tech debt. Compliance issues and workstream bottlenecks are good places to start. The former brings tangible value to the company, and the latter opens up additional capacity.

Keep pushing for additional resources and remember that managing someone still consumes your time. We’re trying to avoid a capacity trap again.

9. Have a Drink and Re-Evaluate

You did it. Things are under control and you learned about workplace boundaries and how to enforce them. Not only that, these are skills that companies value immensely and will pay for.

If you’re here at 4 months and you’ve done all of these things and nothing has changed, leave. They don’t deserve you, but rest assured they’ll notice you when you’re gone.

Project Plan: Licensing Company

For a job application, I was asked to put together a project plan for a Licensing company looking to use technology to revamp one or more business processes. I’ll attach the request document here, and then include my response. I’ve built the entire solution using Salesforce, with a combination of core and add-on functionalities, intending to cover all aspects of the business process.

Technical Solution Proposal

I’ve taken the approach of a cost-no-object solution.  There are other options available to handle a handful of these requirements, and I’ll try to call those out as other possible options.  For diagrams, swim lanes, etc. I’ll be drafting them just for the main, cost-no-object solution.  I can speak to those other options if needed.  The intention is to use the right Salesforce tool for the job, rather than proposing a bunch of abstract configuration.

Receiving New Customer Applications (Web-to-Lead)

Recommended Components:

  • Standard Object: Lead
  • Configured Feature: Web-to-Lead Form

The built-in web-to-lead form can be used to handle new potential customer onboarding, allowing you to leverage the built-in processes for converting a Lead into a Contact and Opportunity.  This abstraction can be hidden if the customer is not interested in following a lead process with some automation to automatically convert that lead into an Opportunity and Contact record.

Leads can be automatically or manually assigned to a Sales Manager based on region or availability.  If there is a team that handles pre-processing of leads, Omni-channel can be used for routing Leads to the pre-processing team who can evaluate and contact the prospective customer about missing or invalid information.


Given that the Web-to-Lead form has some data type restrictions, it may be desirable to move toward an Experience Site which offers more built-in functionality.  I propose further down the implementation of an Experience Site, but it is not a hard requirement to accomplish the customer’s goals, and may not be required to fulfill the needs of the Lead form.

Inspection Checklist (Custom Object)

Recommended Components:

  • Custom Object: Inspection Checklist
  • Configured Feature: Delegated Administration
  • Configured Feature: Record Types

The custom Object, Inspection Checklist is intended to include the fields which may be required for a given inspection.  This allows configuration of custom field types and the use of tooltips to give the technician and customers information about the information to be collected. 

Utilizing different record types allows the inspection checklist to be configured in such a way that different types of inspections will require collection of different kinds of information, and allows re-ordering fields, and hiding fields which are not relevant.  New lightning features also allow hiding of fields which aren’t applicable given previous answers, helping to reduce scrolling and clean up the UI for the inspector.  One example would be to not expose a “Corrective Actions” field when the corresponding regulation has been marked as “Compliant”.

Because users in the org need the ability to configure these inspection checklists based on new regulations or updated standards, we can use the Delegated Administration feature to allow non-admin users to update fields on the different Checklist types, as well as the layouts used by those different checklists.


I’ll be proposing the use of Field Service Lightning in a later section.  A solution could exist for leveraging Work Order Line Items to cover the variable inspection requirements, but that is somewhat abstract and wouldn’t make for a clean experience.

There are some other tools available in the AppExchange, such as this one: Field Service Inspector, which can be used if the inspections are infinitely variable by location and can’t be condensed down to record types with standard checklist options shared by type.  The business description leads me to the impression that there is some variability, but it isn’t prohibitive to the proposed solution.

A Cloud-Based, Mobile Solution

Recommended Components:

  • Licensed Feature: Field Service Lightning (FSL)
  • Configured Feature: Field Service Mobile App
  • Configured Feature: FSL Scheduling and Territories

Leveraging Field Service Lightning (FSL) allows appointment scheduling and dispatching of inspectors to the client site.  Attaching the proposed Inspection Checklist to a Work Order allows quick access to one or more checklists which may be needed on-site.  This automated scheduling in FSL can be implemented through the use of Maintenance plans.  Utilizing Service Territories allows you to ensure the assigned inspector is local to the area and that they can be assigned to work inspections in the same relative are to reduce drive time and cost.

FSL acts as the portal through which the inspector can enter their notes associated with the inspection, preventing them from having to write their notes and transpose them later. This should speed up the publishing time for an inspection report to near-instant.  It also supports working offline in case internet access is not available during an inspection. This prevents the need to fall back to paper and pencil in such situations.


Field Service Lightning isn’t a hard requirement for accomplishing this work.  The Inspection Checklist is fully-functional inside the standard Salesforce App, but significant code changes, configuration, and automation may be required to handle all of the scheduling, routing, and geographic considerations.

Regulation Article Links

Recommended Components:

  • Licensed Feature: Salesforce Knowledge

Salesforce Knowledge can be leveraged to house the regulation articles.  The applet can be attached to the proposed Inspection Checklist object to allow the inspector to look up information about certain items in the inspection checklist.

Additionally, with the proposal of an Experience Site further on, we can leverage the same knowledge base to help customers by providing the same requirements information online so they can self-audit to ensure compliance and get further information on regulations without needing to tie up other support avenues.


If Salesforce Knowledge is not viable or the inspectors need line-item-level guidance, Tooltips on fields can be leveraged to provide links to specific articles or brief information about what that particular step entails.  Tooltips only support a limited number of characters, and as such cannot provide detailed guidance on their own.

Customer Portal

Recommended Components:

  • Licensed Feature: Salesforce Experience Cloud Site
  • Configured Feature: Flows

In order to quickly publish inspection results and allow online collaboration between inspectors and companies, we can leverage an Experience Cloud site.  This would allow us companies to log in and view prior inspections, view corrective steps, and enter corrective action plans to be attached to the inspection.  These can be done in-line as separate fields for each failed inspection step, or as a file upload which is attached to the object.  When satisfied, the inspector can lock the record, and the company can retain access to prior reports.

Publishing online via an Experience Cloud site also allows near-instant publishing of the report once the inspector is satisfied with the information they have entered into Salesforce.  Flow automation can also be configured within Salesforce to send reminders about pending inspection results if no follow-up has been received from the customer to ensure that licensing doesn’t lapse without requiring the inspector to follow up.

Use of automation components such as Visualforce pages, Screen Flows, and Apex Classes can be utilized to generate a PDF of the report as well in case the company prefers to keep such a copy for their records. This can also be automatically emailed to the customer or provided on the Experience Cloud Site for them to download.


You could easily and successfully utilize Salesforce Email functions on the Inspection Checklist object to handle interactions with the customer for discussing corrective action plans.  You could also email the inspection results directly from the Checklist, but it would provide reduction in usability for licensed companies, and could create additional friction.  Without another solution available, your support agents may also have to spend time providing information on old audits.  I’ve outlined other benefits to the Experience Cloud solution in other sections.

Combined Workflow Model

Sample User Stories

New Customer Onboarding

  • As a Sales Agent, I want to minimize call time when onboarding new customers, so that I can handle more customers in my work day.
  • As the Head of National Sales, I would like new leads to be assigned automatically to available Sales Agents, so that potential customers are served quickly and agents cannot “game the system” when picking up leads.
  • As a Head of National Sales, I would like to automate the selection of Inspection Checklists so that we can eliminate erroneous checklist selections, unnecessary inspection steps, and reduce the time to onboard new Sales Agents and Inspectors

Scheduling and Logistics

  • As the Logistics Manager, I would like to present Sales Agents with recommended inspection times based on inspector availability in the customer’s location so that they can choose from dates and times which reduce travel time for the inspector.
  • As the Chief Operations Officer, I would like my agents to input their inspections digitally so that they don’t have to spend time transcribing their notes onto the computer after an inspection.
  • As the Logistics Manager, I would like my inspectors to only be scheduled near their homes to reduce drive time where possible, prevent employee fatigue, and reduce fuel costs.

Inspection, Follow-up, and Award

  • As the Chief Inspector, I would like my agents to be able to select which inspection checklists they use on-site so that they always have the right evaluation criteria on-hand.
  • As an Inspector, I want to send the inspection report to the customer automatically so that I do not need to follow up with the customer just to let them know the inspection results are available
  • As the Chief Inspector, I would like for my inspectors to have access to information about their inspections and guidelines for what to look out for while on-site in order to reduce training overhead, reduce mid-inspection call-backs to home office for help, and improve inspection accuracy.
  • As the Chief Inspector, I would like the customers to be able to review feedback and provide Corrective Action Plans (CAP) without needing to call inspectors so that Inspectors can spend more time on completing Inspections and reviewing CAPs instead of talking to customers.
  • As the Chief Inspector, I would like to automate the issuing of the certifications when a CAP is accepted or an inspection is passed so that my inspectors can service more customers, faster.
  • As the Chief Inspector, I would like to inspection scheduling so that random inspections are not missed, and so that Sales Agents and Inspectors can focus on helping more customers.

Follow-up Questions

  • Is the desired state to have the logistics team handle scheduling of inspections?  Or to shift responsibility for scheduling onto the Sales Agents
  • What are you currently using to handle the task of scheduling of your inspectors and their routes?
  • Do you have prepared, or can you work with us to map out which inspection checklists should correspond with a given industry?
  • At what point should a lead be considered ‘dead’ or expired?
  • How frequent are the unannounced inspections?
  • Roughly how many different types of inspection checklists do we anticipate needing for the business, and is that number expected to grow or shrink in the future?
  • Is part of the 5-day period between inspection and report composed entirely of time the inspector spend transcribing their notes into the word template?  Or is this also time where the inspector is compiling additional notes and recommendations?
  • In an ideal state, how would you like the system to handle unexpected absences, schedule conflicts, etc.?
  • Which teams are responsible for the following tasks:
    • Qualifying the Lead
    • Recording Deal Information
    • Scheduling Inspections
    • Distributing Certifications
  • Is it the same inspector who is responsible for the on-site inspection, and the one reviewing the Corrective Action Plans?
  • Is there an approval or sign-off process for Corrective Action Plans?  Or is it the sole discretion of the inspector?

Sample Data Model

9 Example Admin Interview Questions

I mentioned in my last post (How to Interview A Salesforce Admin) the approach I like to take for interviewing Salesforce Administrators. That approach really helps to find out both whether the admin knows about the different tools available, how to use them, and the flexibility of their solutioning.

This post describes a handful of questions that fit in with that simple question design, and then I’ve gone through as well and given my explanation for the kinds of responses I look for in the interview process.

1. The business would like to stop users from selecting an Opportunity Closed Date which is in the future.

  1. How would you accomplish this?
    • The answer you should expect here is the use of a Validation Rule. That’s the In-the-box answer, and would be the most reasonable solution to this request.
  2. Are there any other ways to accomplish this?
    • Technically this can also be done via Apex, or in some cases, businesses will have a status field which explains any data integrity issues.
    • Don’t put a lot of weight on this one, as there’s really only the one common solution for this problem. If you get one of these answers instead of Validation Rules, this might be of concern, as this isn’t a typical Administrator answer, nor is it the accepted way to do this check. Apex validation is typically used for much more complex validation that this problem requires.
  3. Are there any other changes that might need to be made along with this change?
    • Odds are if a sales team was allowed to put their Closed Date in the future, at some point one or more of them did. It might have even been a requirement for them to provide a date in the future so the C-suite could understand which Opportunities might be closing, and when.
    • The problem this creates is that now a number of Opportunities may fail that validation rule and have to be updated after it is implemented. A couple of solutions to that may look like:
      1. Use ISCHANGED and ISNEW to ensure that the validation rule only fires when an Opportunity is first created and when the Close Date field is subsequently updated. This prevents an invalid Close Date from blocking a user who may not know what Close Date to use.
      2. Use Data Loader to mark Closed Dates which are in the future as blank. Make sure to check with stakeholders as this will impact reporting.
  4. How would this change impact the user experience?
    • This kind of fits in with part 3, as blindly implementing the validation rule without ISNEW or ISCHANGED checks or not going back to update invalid data can create a situation where an Opportunity cannot be updated until the validation error is resolved. The user encountering the error may also not be close enough to the sales process to know to change the Close Date field, or may not have access to do so.
    • Planning around this to ensure a lack of interruption is important for a good user experience.

2. I want to notify myself via email notification when an Opportunity [Amount] is changed to be greater than $250,000. What should I do?

  1. How would you accomplish this?
    • The easiest way to accomplish this is through a flow, setting it to send an email when an Opportunity’s Amount field becomes greater than 100k.
  2. Are there any other ways to accomplish this?
    • This can also be done through a workflow rule or a process builder, but the recommended method and the way Salesforce would recommend doing-so is through a flow.
    • A question could also be asked about whether this request might be better-suited for something like an approval process, where when an Opportunity amount exceeds $250k, the Opportunity is escalated to management for review. It would depend on the business’s actual goals, but notifying an individual of a change like this implies that the Opportunity is worthy of additional review.
  3. How would this change impact the user experience?
    • One thing to note here is that if the only criteria is whether an Opportunity’s Amount goes above $250k, then there’s a possibility that the recipient may receive multiple notifications for the same Opportunity, especially if synchronized quotes tend to change frequently. Additional criteria like only notifying when the amount goes from less than $250k to greater than $250k, or only notifying once the Opportunity is in a stage where less fluctuation occurs may be warranted to ensure the notification isn’t as noisy.

3. When an Opportunity is closed won, I want to submit this record for manager review, what should I do?

  1. How would you accomplish this?
    • Given that we asked for a way to submit the record “for manager review”, the answer we should be expecting is an Approval Process.
  2. Are there any other ways to accomplish this?
    • There are a couple of other ways to accomplish this, but they aren’t exactly the recommended solutions. On the business side, you could simply review any newly closed Opportunities during a regularly scheduled meeting. You could also utilize a combination of record types and flows to restrict access to an approval stage post-Opportunity-closure. It’s not ideal, but it is an option. You should expect the first answer however.
  3. Are there any other changes that might need to be made along with this change?
    • As part of the approval process, you’ll need to outline notification behaviors and/or whether the record should be locked. Outlining criterial requiring approval is also an important step. The business may want all Opportunities treated equally. They may also want to ignore Opportunities with an Amount below a certain threshold.
  4. How would this change impact the user experience?
    • Users should be notified of the new approval process, especially if your approval process implements record-locking so they understand why Opportunities may be locked. Additionally, there may be Opportunities which should have been reviewed, but which have passed over the approval process trigger. These may need to be reviewed outside that process, or if they fit within the approval criteria, may cause a large number of records to be impacted on go-live.

4. I would like a flow that is launched from Account on change, which checks if the NumberOfEmployees field is greater than 500, and if so, changes the Description field on the Account to “Big Company”

  1. How would you accomplish this?
    • The expected answer would be a Flow for this question
  2. Are there any other ways to accomplish this?
    • There are a couple of ways to accomplish this, including Process Builder and Apex Triggers
  3. Are there any other changes that might need to be made along with this change?
    • The request here is kind of unusual in-and-of itself, and so while this change is itself pretty simple, the user impact is significant and the approach should probably be revised. If you’re really going to do this, at least do yourself a favor and take a manual backup.
  4. How would this change impact the user experience?
    • This change would cause any accounts with >500 employees to have their Description be overwritten. That is… Probably not a great idea. To fit the requirements, you could try prefixing the Description with “Big Company” to avoid overwriting your teams’ hard work, but it would probably be better to do this work on a separate field, since the goals and the proposed solution doesn’t seem quite so well-aligned. Additionally, if this is just something needed for reporting, then Bucket Columns or Row-level formulas will do the job just fine.

5. A user wants to add a new field [Support Tier] to Account object, and should have a dropdown list of “Bronze”, “Silver”, and “Gold”.

  1. How would you accomplish this?
    • This is a pretty simple example for a picklist, on its face.
  2. Are there any other ways to accomplish this?
    • What’s being described here sounds much more like an Entitlement, which along with Milestones can help ensure that tickets are resolved within a desired timeline or escalated to ensure that a customer’s service-level is met.
  3. Are there any other changes that might need to be made along with this change?
    • As a picklist, it should probably also be pulled onto cases to help ensure that agents know how to prioritize cases. If done as an Entitlement, then the business needs to decide what kinds of Service Levels should be offered and what kinds of lead times should be offered to customers, keeping in mind agent throughput
  4. How would this change impact the user experience?
    • The picklist solution doesn’t change much about the user experience, except that Accounts will need to be populated with the new picklist. For entitlements, the users will need to be made aware of what the milestone graphics mean on cases (if you use them), as well as how cases should be escalated on higher support tiers. How cases should be prioritized and how to ensure that cases are resolved within the promised timelines are pieces agents should know how to complete as well.

6. I only want the Opportunity field [Primary Campaign Source] visible to “Marketing User” Profile. What should I do?

  1. How would you accomplish this?
    • The standard answer for this is to update the Field Level Security for this field to only give read access to the Marketing User profile.
  2. Are there any other ways to accomplish this?
    • Salesforce recommends simply copying the standard profiles, so before making changes, it’s recommended to duplicate the profile first and update access there and re-assign users to your new profile.
    • Another option is to grant access via Permission Set, but this is only really advantageous if users who should have access to this field may or may not be using the Marketing User profile
  3. Are there any other changes that might need to be made along with this change?
    • No other changes are necessary unless you’re cloning the Marketing User profile for the first time. Then you would need to clone the profile and re-assign users who are currently using the Marketing User profile.
  4. How would this change impact the user experience?
    • The field here would disappear for most users, and with it, a link to a related Campaign from the Opportunity. This could confuse non-marketing users who are accustomed to reviewing campaigns related to the Opportunity.

7. We only want to show a list of related Opportunity on an Account page to users whose profile is “Standard User”

  1. How would you accomplish this?
    • At its simplest, you can add the related list to the page layout, and then add the Opportunity related list to the lightning page layout, and then add conditional visibility to hide the component when the users’ profile isn’t Standard User
  2. Are there any other ways to accomplish this?
    • You could accomplish the opposite way as well by including the related list on the page layout for everyone, and just not including it for the lightning page layout for apps not used by the Standard User profile
    • You can also accomplish this by having separate page layouts by profile, so the layout for the Standard User profile would simply add the Opportunity related list to the page layout and the lightning page layout (or flexipage)
  3. Are there any other changes that might need to be made along with this change?
    • The first answer is pretty straight-forward. The latter 2 answers will require some planning, as additional page layouts and assignments would be required, as well as a understanding of who has access to the different lightning apps that are in use by your org.
  4. How would this change impact the user experience?
    • Not much changes about the user experience when it comes to the user experience (apart from the addition of this new field). It is worth noting though that it does take up real-estate on the page layout and that in a collaborative environment, a question may occasionally come up about why one user has this component, but another user does not.

8. I want to prompt a user to enter Opportunity [Closed Lost Reason] before marking the [Stage] as “Closed Lost”.

  1. How would you accomplish this?
    • The most typical way to accomplish this is to use a validation rule to check if the Opportunity is marked as Closed Lost, and display an error if the Closed Lost Reason is blank.
  2. Are there any other ways to accomplish this?
    • In the event that your Closed Lost Reason field is a picklist, you can actually use field dependencies and the Required flag to enforce this. Salesforce ignores the Required flag on a picklist field if there are no options available for the user to select. And since we would only provide Closed Lost Reason values when the Opportunity Stage is Closed Lost, that field would only become required once the user tried to move the stage to Closed Lost
    • Another option is to use separate page layouts and record types, but that’s an edge-case, and is not an optimal solution
  3. Are there any other changes that might need to be made along with this change?
    • Inevitably after making this sort of change, you will find out that you have some Closed Lost opportunities which have no Closed Lost Reason. Those will need to be corrected in order to stop this change from preventing additional changes to Opportunities which don’t currently comply with the new validation rule.
  4. How would this change impact the user experience?
    • The first example will simply alert users if they try to save an opportunity as Closed Lost without also providing a Closed Lost reason.
    • The second example will provide a visual cue to the user that Closed Lost Reason is now selectable once they move the stage to Closed Lost.
    • If you fail to update old Opportunity records, this change may block other teams from updating data on the Opportunity once it’s in Closed Lost, as they may not know which reason to enter, or that this new requirement is in-place. Sometimes it’s helpful to allow other users to update closed opportunities as you fill in your post-mortem on the lost deal.

9. 6 months after Opportunity Last Modified Date, I want to auto mark Opportunity [Stage] as “Closed Lost”.

  1. How would you accomplish this?
      • Using a process builder, you can set a trigger that when the Last Modified Date is updated on an Open Opportunity, after a 6 month delay, mark the Opportunity as Closed Lost. Conveniently enough, if Process Builder sees another one of these events is queued up, it will replace it with the new scheduled action.
    1. Are there any other ways to accomplish this?
      • This could also be done via a scheduled apex job, or via a schedule-triggered flow, both set to retrieve all Open Opportunities where the Last Modified Date is > 180 (ish) days ago, and mark them as Closed Lost
    2. Are there any other changes that might need to be made along with this change?
      • Users should obviously be made aware of this change, but in addition you may want to record somewhere like a Closed Lost Reason, that the Opportunity was Aged Out so it’s clear why it was closed. Obviously you want to also inform the Sales Managers.
      • In addition, as mentioned above, if you’re making a decision on behalf of users, you should inform them of why. This could be done via a notification to the Owner of the Opportunity that it was aged out.
      • It may also be worthwhile to add a flag to the Opportunity that Sales Managers can use to exclude this particular Opportunity record from automation if the Sales Manager is expecting a particularly long Sales Cycle.
    3. How would this change impact the user experience?
      • In addition to cleaning up the Opportunities automatically, you could accidentally pull the rug out of some users, as things like emails, chatter, and related tasks and events may not update the Last Modification date on an Opportunity. For a particularly long sales lifecycle, this is of significant importance as you don’t want to block users.

How to Interview A Salesforce Admin

Interviewing in general can be pretty awkward, and when someone isn’t trained or practiced interviewing, it can leave a lot of questions. How do I test this person’s skills? How do I test their decision-making abilities? Will they be able to work with our design philosophy? Do they know what they’re doing?

The approach that I like to take is very simple, and straightforward questions. Things like “How do you make a field required, but only once an Opportunity reaches the Contracting stage?”

When I ask a question like this, I’m looking for 4 things:

  1. Can the interviewee answer the question?
  2. Can the interviewee describe other solutions? And can they describe the benefits and caveats to the solutions they have proposed.
  3. Can the interviewee describe the impact of those changes to the data in Salesforce?
  4. How do those changes impact a user’s workflow?

Can the interviewee answer the question?

The first criteria is pretty typical. It’s a simple question, so an admin with a rudimentary understanding of the system should be able to answer it. Using the example question, we can say with confidence that you would use a validation rule to require the field be entered when the Opportunity stage reaches contracting.

This takes us to part two…

Can the interviewee describe other solutions?

Can the interviewee describe other solutions and the benefits or caveats to these solutions? There’s almost always a few different ways to solve problems in Salesforce, and an interviewee’s ability to describe these different options goes a long way toward understanding whether they know the “by-the-book” answer, or whether they understand the platform well enough to work around business rules or an environment’s own obscurities.

By extension of this, we want the interviewee to be able to understand the pros and cons of the proposed solutions. A validation rule handles this job perfectly, but has its own usability quirks. In my other post, I likened Validation Rules to a hammer, and that they’re often the first mechanism people run to for controlling input in lieu of other solutions. There are other options available, including:

  • Using record types and page layouts to require input at different stages
  • If the field is a pick list, we can use field dependencies and the required field checkbox to require selection after the contracting stage
  • Apex can also be used for restricting/requiring input
  • Utilizing proper field types that match the expected input and then using formula fields when we need to convert those values

Each of these has its own caveats:

  • Record types is messy on the admin side and requires automation to convert between types
  • Field dependencies really only works on picklist types
  • Apex is much more complex and suffers from the same after-submission error messages that validation rules do
  • And proper field typing requires you to be aware of the requirements before the field is created, or else a data migration is required. It also requires extra fields for the formulas if needed.

They also have their own benefits:

  • With varying record types and page layouts you can change how information is presented and hide anything which is no longer relevant
  • Field dependencies communicate expectations to the user up-front, rather than waiting for them to submit and requiring them to correct the mistake
  • Apex can be infinitely more complex than the other solutions
  • Proper field typing also communicates expectations to users up-front

It’s less important to know all of the solutions than to be able to recognize that other solutions exist, and be able to justify why you chose the solution you did.

Can the interviewee describe the impact of those changes to the data in Salesforce?

When a new field or automation is added to Salesforce, a pivotal question is whether the change will be a running change or if it also needs to be implemented retroactively. Understanding that a new validation rule which requires that a Closed Date cannot be set 1 week in the future has down-stream consequences is important.

Perhaps this business rule is new. Maybe there are a number of Opportunities out there which would not pass this validation rule. What happens then? What happens when a marketing resource changes an unrelated field on the Opportunity? Do we add an IsChanged expression to our formula so they don’t have to make the correction for the Sales Manager? Do we do a data load to clear out any Close Dates which are too far in the future? Or do we notify Sales in advance to update their close dates? And what happens if it’s not a close date, but another field instead where closed Opportunities might not pass the criteria going forward? If we have to data load or batch process them in the future, we may get blocked by this failing validation rule.

It’s once again far less important that the interviewee can come up with all these examples off-the-cuff, but important that they can talk through these and understands that every change and decision has consequences going forward.

How do those changes impact a user’s workflow?

The last piece is secretly one of the most important. In order to maintain a good relationship with the business and continue to grow your team, you have to be able to provide a tool that teams can use. Admins aren’t creating something they use day-to-day. They’re building something that the business users have to use day-to-day. That means considering how non-technical users will interact with the system, and ensuring that their experience is as easy as possible. Ensuring that the interviewee understands how their changes impact the user experience and how to mitigate and speed bumps that the change may create is critical to ensuring continued buy-in from the business and a happy, working relationship with the business going forward!

Example Interview Questions

I’ve gone ahead and written out some example questions, including the types of responses I look for in my other post, found here:
9 Example Admin Interview Questions

The Basics of Using PARENTGROUPVAL

This post covers the basics of using the PARENTGROUPVAL summary formulas in reports, and how the requirements change in matrix reports.

In my current admin role, it’s not in my job description to do reporting, but somehow I always find myself embroiled in emergency requests to draft reports for executives at my company. Maybe it’s just me, but I’ve always found the PREVGROUPVAL and PARENTGROUPVAL functions in Salesforce to be far more confusing than almost anything else. I think this comes from the weirdly sparse documentation around these two functions as the fact that their behavior is so wildly different from anything else we’re used to.

When we look at Salesforce’s documentation for these functions, it’s… rough… They give you a high-level explanation of how the functions work, but don’t give you much in the way of how to use them beyond a simple example:


In addition to this, they allude that there is a 3rd grouping level that has to be applied on matrix reports, but then don’t tell you much about what’s actually supposed to go there. So let’s try to answer that question and break down some examples. For this to make sense, we need to try to understand how summary formulas work, since that’s how we get our column groupings. Let’s do that first!

Basic Summary Formula (Count Records in a Group)

First, let’s start with a very basic Opportunity Report, with a configuration like this:

Report Builder Sidebar (Outline tab) from a Salesforce Opportunity report, with 'Account Name' field in the Group Rows section and 'Opportunity Owner', 'Opportunity Name', 'Stage', 'Amount', and 'Fiscal Period' in the Columns section
Group Rows: Account Name
Columns: Opportunity Owner, Opportunity Name, Stage, Amount, Fiscal Period

Then we’ll create a Summary Formula.

Click the Caret next to Columns, then click Add Summary Formula

Selecting the 'Add Summary Formula' option from the caret menu in the Columns section of the Report Builder sidebar on the Outline tab

Next, let’s name our column Row Count, set the output type to Number, and Decimal Points to 0.

For the Formula, just put RowCount, then Click Apply.

Column Name: 'Opportunity Count'
Formula Output Type: 'Number'
Decimal Points: '0'
Formula (General tab): 'RowCount'

That will give you a page that looks something like this:

Report preview showing the total number of rows at each grouping level (Account Name), as well as at the grand total level

You’ll now have your new Opportunity Count column, and on the Subtotal Rows for Account Name (our group column), you’ll have the number of records within each grouping, as well as the total number of records in the report in the Total Row (or Grand Total)

This takes us to an important facet of summary formulas. You can choose which groups they apply to, as well as whether it should be calculated for the Grand Total. This becomes important once we try to do something like calculate the percentage of Opportunities held by each Account. In fact… let’s do that!

Basic PARENTGROUPVAL Summary Formula (% Records in Group vs Total, Summary Report)

We’ll add a new summary formula that looks like this:

Column Name: '% Records'
Formula Output Type: 'Percent'
Decimal Points: '0'
Formula (General tab): 'RowCount / PARENTGROUPVAL(RowCount, GRAND_SUMMARY)'

We’re setting the Column Name to % Records, a Formula Output Type of Percent, and 0 Decimal Points.

For the Formula, we’re going to use:


This is taking the total count of the rows in each group, and dividing it by the row count at the Grand Total level.

When you try to hit save, you’ll probably see this warning now:

You must select a grouping context to use any report summary function.

To fix this, go into the Display tab, set the formula to apply to Specific Groups, and then in this case, Row Group should automatically be set to Account Name

On the Display tab
Where should this formula be applied?: 'Specific Groups'
Row Group: 'Account Name'

Now we can click apply.

Report preview showing the percent of all opportunities that each group (Account Name) represents

Looking back, that requirement kind of makes sense. If we tried to do this calculation at the Grand total level, we’d get 100%, so it wouldn’t tell us much. Let’s make this more difficult though and turn this into a matrix report to really show off how these summary formulas work, as well as PARENTGROUPVAL.

Basic PARENTGROUPVAL Summary Formula (% Records in Group vs Total, Double-Summary Rows Report)

So what happens when we use two different grouping levels? Now we have to chose which groupings our column will apply to. To test this out, let’s move Fiscal Period into the Group Rows under Account Name.

Move the 'Fiscal Period' field into the Group Rows section of the Report Builder Sidebar, under 'Account Name'

That gives us a report that looks something like this:

Report preview showing records now grouped first by 'Account Name', then by 'Fiscal Quarter'

Notice that the summary formulas still apply! Let’s re-open % Records and go back to the Display tab.

We now have two options for our Row Group— one for each of our group rows.

On the Display tab, we can configure which grouping we want to apply the formula to ('Account Name' or 'Fiscal Period')

Let’s take a look at both to compare them:

You can see above how our percentage calculation changes depending on which group we select. You’ll also notice that Opportunity Count doesn’t seem to care which is selected because we previously selected All Summary Levels for that summary field.

Now that we can see how those summary levels work, let’s go bigger and apply this to a matrix report.

Basic PARENTGROUPVAL Summary Formula (% Records in Group vs Total, Matrix Report)

If you’re still following along from above, let’s turn this into a matrix report by moving Fiscal Period down into the Group Columns.

Move 'Fiscal Period' field down from Group Rows into Group Columns

When you try to make this change, you’ll get an error like this:

Salesforce Error Message, saying:
Incorrect number of parameters for function 'PARENTGROUPVAL()'. Expected 3, received 2
Incorrect number of parameters for function ‘PARENTGROUPVAL()’. Expected 3, received 2

This is because your PARENTGROUPVAL function now requires you to also choose which column grouping it should use. This is unfortunately not well documented. The documentation linked above shows that it’s needed, but doesn’t tell you what goes there. Let’s fix that:

Back in the summary formula editor, we must add a 'Column Grand Summary':

First, we need to update the PARENTGROUPVAL formula to use ROW_GRAND_SUMMARY and COLUMN_GRAND_SUMMARY (These are not in the Salesforce documentation at time of writing):


Next, we need to go into the Display tab and set our Row Group and Column Group. We’ll set Row Group to Account Name and Column Group to Fiscal Period

On the Display tab, we can now apply 'Account Name' as the Row Group and 'Fiscal Period' as the Column Group

Now let’s hit Apply.

Report preview now showing that '% Records' now sums across all grouping combinations to 100%, and splitting the Row Group (Account Name) by each Column Group (Fiscal Period), and displaying the '% Records' for each subgrouping
Row Group: Account Name, Column Group: Fiscal Period (% Records Per Account Per Quarter)

This gave us a breakdown of the percentage of opportunities within each account, per each quarter.

Let’s look at the other options:

As you can see, the double-Grand Total option isn’t particularly useful, but the other options, which summarize by Account, by Fiscal Quarter, and by both can provide a valuable breakdown of our Opportunity data.

Those are the basics on how summary formulas and PARENTGROUPVAL work. In another post, we’ll break open some more of the possibilities with the function, complete with more examples and use-cases!

In the meantime, try it out yourself with this exercise on Trailhead!:


or for an extra challenge, take on the Lightning Experience Reports & Dashboards Specialist Superbadge.

Tales from the Socials: Mass custom reports


The poster reports that they have heard that having large numbers of reports will result in report results which are incorrect. The assertion is a little confusing, as reports should be read-only in their relationship to the data in your org, so my immediate assumption was that he/she means that the data appears incorrect and thusly can’t be relied up for making business decisions.

What’s the Deal?

This problem happens, and apparently it happens frequently enough to warrant its own support article:

There are a number of ways that this can happen (as Salesforce points out), but the one I want to focus on is: Proper Report Type Selection. This is kind of a multi-pronged issue. In some organizations (like mine), you may have a separate team in charge of generating reports, or the business may ask you to give access to build their own reports, and even create their own report types… This can be helpful in removing that responsibility from you admins, as well as putting someone in charge of drafting your reports, who is more familiar with the business need… but you know what they say about having too many cooks in the kitchen…

Problem 1: Repetitively Redundant Report Types

Sometimes team members will need a report and they need it fast, so they crank open Salesforce, and they know enough about the development lifecycle to know that a new report type is required, but they’re in such a rush that they forget to make sure it’s not already there:

Oops… Unfortunately, now that I know there are 3 of these, I’m questioning whether or not I know which one I should actually be using. Now fortunately, the authors have described the types of relationships present in those reports (though, I can tell you the first one doesn’t actually describe the delivery object’s relationships properly). Unfortunately as well, the descriptions don’t actually describe the intent or functions of the reports. With names and descriptions like these, you don’t know what kinds of lookup relationships might be present in those reports! Perhaps that’s why we have 3 reports that are all pretty similar?

Problem 2: Those Redundant Report Types Weren’t Redundant…

When we start looking at these 3 reports, we find out that two of them are similar, but one is very different (but notice they’re all in different report type categories):

Ok, not too bad. We might be able to consolidate two of these. The 1st one looks a little suspicious though… Let’s take a closer look at the layout for that one:

548 fields in this Layout with 1 object type… Concerning, but let’s keep going…

Ok, we’re not even half-way down the list and there are some very real problems here… Part of this is a Salesforce problem, where we’re showing only a fraction of the characters that are available in the space given here. So while we see 3 Credit Team… entries, we don’t know for sure that there’s a big problem yet.

But you should also notice that there are no page layout sections… So your drop-down selection in the report is just one long list, and in the event that there is a field on your delivery object that has the same name as the one on your opportunity object (think Created Date), then there’s going to be a problem…

Now fortunately, our friends at Salesforce thought of this problem and they help us out a little by telling us what field drives the relationship for the field we’re looking at when selecting a field:

Since all of these fields run together, while technically we’re providing enough context for a user to select the right fields, we’re not allowing Salesforce to help our users, nor using the grouping functionalities in the report editor to really help us out.

Problem 3: Admins Have Bad Hygiene

But there was something else. There are two status fields on the Delivery object… When I create a report with these, I soon discover that these fields are not equivalent…

So if someone is relying on these fields do find out the delivery status, there’s a very real possibility that they select the wrong one. To make matters worse, there is a Delivered status under both.

So What Do We Do?

There are 4 things we can do to work through these problems:

Audit and Reconcile Existing Report Types

You probably don’t need a report type for Deliveries with Opportunities and Opportunities with Deliveries if you’ve set up your reports properly. Nor do you need Opportunities with Deliveries when you already have Opportunities with Deliveries with Activities, as adding Activities is just additional content layered onto the Deliveries with Opportunities report type.

(Note: Deliveries with or without Opportunities is a different type; though you can always use filters in your report to get an equivalent result)

Name and Describe Reports Properly

I recommend naming your report types in terms of the objects that are accessible, and the relationships. Something important to note: Don’t just go with Deliveries with Opportunities with User when you’re looking up to a (for example) Delivery Driver. Something like Deliveries with Opportunities with Delivery Driver is much more helpful to someone choosing a report type.

Continuing down, be sure to describe the goals or intents for the report type, and if you have a ticketing system or a data catalog, that information could be immensely helpful for you, your team, and your users.

Re-Train Reporting Team

If you are seeing a number of repetitive report types, or your reporting users aren’t following quality standards, host a re-training session and put together documentation with quality standards. You can always revisit this in the future, and it will help them deliver on user-requests and ensure there are no doubts about the integrity of the data in your reports.

Audit Objects for Fields With Identical Names and Rename Them

Things happen, and sometimes team members are exhausted or are trying desperately to hit a deadline and forget to evaluate whether the work they’re doing might be redundant, or that they might inadvertently convey information to users in a way that might give them the wrong ideas. In the case of this status field, one status field represents the generalized vehicle delivery status (for example, Delivered; Not Delivered). The other status field provides specific delivery details based on the type of delivery, hence the mismatch in values. At hub for picked up vehicles actually qualifies as a delivered vehicle, but someone who isn’t on the Logistics team might not know that. If they select the wrong status field, they might get the wrong idea. There are two ways to combat that:

  • Simplify the field – Consider in this case a boolean field titled something like “Delivered”. It’s a very obvious search for a user, and answers their question as simply as possible.
  • Consider names like “Short Status” and “Detailed Status” which helps convey to the user that they may want to look at other fields to get their data, and helps separate out the two fields so they don’t assume they’re the same.