Category: Documentation

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:

PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)

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:

PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER, 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: ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER) ) / PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)

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 - PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)
) / PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)

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:

(CURRENT_PERIOD_AMOUNT - LAST_PERIOD_AMOUNT) / LAST_PERIOD_AMOUNT

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:

  • % Change in Amount by Quarter
  • % Change in Amount by Quarter (subdivided by 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.

  • Column Group Options: Grant Total Only and Fiscal Period
  • Row Group Options: Grant Total Only and Account Name

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:

Compare Groups with PREVGROUPVAL() and PARENTGROUPVAL()

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.

BSA-Evaluation-Assessment.docx-1Download

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.

Alternatives:

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.

Alternatives:

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.

Alternatives:

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.

Alternatives:

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.

Alternatives:

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.
    2. 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
    3. 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.
    4. 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:

PARENTGROUPVAL and PREVGROUPVAL on Salesforce Help

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:

RowCount / PARENTGROUPVAL(RowCount, GRAND_SUMMARY)

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:

  • Account Name Summary (Percent Opportunities per Account)
  • Fiscal Period Summary (Percent Opportunities Per Quarter Per Account)

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': RowCount / PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, 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):

RowCount / PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, COLUMN_GRAND_SUMMARY)

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:

  • Row Group: Account Name, Column Group: Fiscal Period (% Records Per Account Per Quarter)
  • Row Group: Grand Total, Column Group: Fiscal Period (% Records Per Quarter)
  • Row Group: Account Name, Column Group: Grand Total (% Records Per Account)
  • Row Group: Grand Total, Column Group: Grand Total (% Records Per… Table?)

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!:

Compare Groups with PREVGROUPVAL() and PARENTGROUPVAL()

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

All The Things You Should Do Before Adding A Validation Rule

Why Validation Rules Are a Bad User Experience

Validation Rules are the hammer of your mission to maintain data integrity in Salesforce- You can do a lot with them, but they’re not often the best tool for the job, and they’re certainly not the most user-friendly.

The ability to write code to determine if a value is acceptable is extremely powerful, but because the rule can only be executed on-save in Salesforce, it creates this awkward interaction between users and the system:

  1. User enters data which isn’t valid
  2. User tries to save the record
  3. Validation Rule runs and displays an error
  4. User finds the invalid field and corrects it
  5. User saves the record again

That’s not bad, except the user could have multiple errors they need to correct; errors could be displayed at the top of the page instead of on the field, meaning the user has to find the field that is incorrect, and there could be multiple levels of validation rules that get hit as the user updates data and corrects the record, causing them to continue to have to re-save the record repeatedly.

Use Proper Data Types

This one is pretty obvious, but I’ve seen before where people will ask for the ability to have a currency field, but they also want it to be able to display N/A in the field when it’s not relevant or before a user has entered a value… If this is an absolute necessity, consider whether it makes sense to keep a flag on the object to indicate if the value had been changed by a user so you can keep the data clean.

You should also consider when a picklist is a better solution than a text field. Lots of times we need reasons for why decisions were made, like why an Opportunity was closed, why a Lead was closed, why a Case closed, etc. While these reasons can be complicated, being able to categorize them into more general categories, like “Customer Lost Interest”, “Customer purchased product”, “Insufficient Funding”, etc. allow you to not only produce better reports, but they also allow you to use other features like Field Dependencies to prevent awkward situations like a “Closed Won” Opportunity with a loss reason.

Using the right Decimal configuration on currency and number fields also helps prevent situations like nonsensical values, by putting an upper bound on the numbers that can be entered. You certainly wouldn’t want a user moving too quick and marking a 2020 Honda Accord as being a $2000000.00 vehicle because they missed a decimal point!

Validation Rule on Text Field to Accept Blanks and $ values

All of these problems could be resolved via a validation rule, but if you address them by using the right field configuration, it teaches the user how to populate them before they commit to an answer. This means fewer clicks and a more intuitive system.

Currency Field Type w/ Automatic Format Correction

Field Level Security/Page Layouts

Another common use-case for Validation rules is to ensure that only certain users are able to update values in certain fields. This is typically done in-lieu of maintaining multiple page layouts. While the cause for reducing future work is noble, it creates the impression for the user that they have access to update fields they may not have access to change. This can make user access issues difficult to figure out if your validation rule messages are incomplete.

For new users, this also becomes a big problem, as a user may need to update a number of field, and overwrite a value that was set by an authorized user, only to find out that they don’t actually have access. The problem can then become bigger if that field affects other fields that they have to change, as they may need to revert those as well!

Validation Rules to Control Security Interrupt User Flow

This issue tends to compound itself, because if you have a number of these rules, then you can either have a validation rule for each field, which means a LOT of rules to maintain, or you can have one big rule and be forced to place your validation error messages on the top of the page, or on a field which is only relevant to one field validation.

Another possibility is that you need different levels of access to a record depending on the user. Setting different page layouts for these different user roles allows you to go ahead and secure those fields that shouldn’t be modified for users who shouldn’t be able to modify them.

Field Dependencies

Field Dependencies are an easy way to ensure data integrity for picklist values, as they can prevent users from entering values that don’t make sense for a given object state. To use the Closed Reason example from before, you can prevent a user from entering a Closed Reason value of “Customer Lost Interest” when the Opportunity is Closed Won. You can also use this for things like Substages or Statuses to give more granularity to your Sales Lifecycle without making it impossible to read on the Opportunity page.

Another cool thing you can do, is a neat interaction between field dependencies and required fields. When a Picklist is marked as Required, a value has to be selected, right? What if there are no values available for this field?

What happens is that since no values are available, Salesforce makes the field no longer required… until there’s a value available to be entered. You can use this on something like an Opportunity Closed Reason to make it so the Closed Reason is required once an Opportunity is marked as Closed, only present the values that are relevant for Closed Lost/Closed Won, AND not have to make an explicit exception for any particular stage.

To show this off, here’s how we can require a Completed “Delivery/Installation Status” in order to close an Opportunity:

Validation Rule – Require Completed Delivery/Installation Status before Opportunity Close

The gif above shows what this behavior looks like when we use a validation rule. The user could save a little time by updating the stage in the record edit screen, but the validation rule still takes the user out of their groove.

Field Dependency – Require Completed Delivery/Installation Status before Opportunity Close

Here, we show that the Delivery Status field just becomes an additional field to enter if it’s not populated. If the user has already entered that value, they only need to confirm the value.

Record Types

This one kind of fits in with page layouts. For some reason there seems to be some opposition to creating record types. Not only does this give you a nice field to use for your field dependencies, but it also gives you a nice breakdown for things like page layouts.

Having multiple record types and likewise, page layouts helps you hide irrelevant fields from users for a given record type, resulting in less bad data, but it also allows you to conditionally automate data in places where it makes sense, and lock fields that users shouldn’t need to modify directly

Automation

Automation is an special because it helps prevent errors, not by validating data, but by using business rules to prevent users from having to enter the data at all. Strong business rules and automation allow you to take the users’ hands off the keyboards and utilize strict data types like numbers, picklists, and checkboxes to drive other field values, which may be subject to unnecessary user interpretation.

Using Validation Rules to Enforce Record Flags

It’s kind of unnecessary to leave this change up to a user, when we could just automate it.

Using Automation to… Well… Automate Data Entry

Help Text

The last tool at your disposal is the Help Text. Help Text serves as your inline documentation. It helps a new user understand what they’re supposed to do with a field before they’ve entered their data. It also allows you to put helpful hits right in front of the user, so they don’t need to review documentation in an external system in order to understand how to commit the record.

Making Users Track Down Their Own Answers

Help Text doesn’t really stop the user from making mistakes, but it gives the user more information to help prevent issues, and to teach them what is expected.

Teaching Users Inline w/ Help Text

The Basics of Using Salesforce Data Loader

Overview

This document walks through the steps of performing updates and exports using the Salesforce Data Loader tool.  While dataloader.io can also be used for this purpose, that tool is subject to additional licensing and has limits to the number of records that can be exported or updated per run or in a given month.  The Salesforce Data Loader tool isn’t subject to these restrictions, and it runs on your local device and connects directly to Salesforce.  It also can utilize the bulk APIs to insert, update, export, upsert, etc.  very large quantities of records at a time.  Salesforce recommends the tool for batches of up to 5 million records at a time.

Data Loader Setup

  1. Follow the instructions in the link below to install Zulu Java OpenJDK/JRE and the Salesforce Data Loader program
    https://developer.salesforce.com/docs/atlas.en-us.dataLoader.meta/dataLoader/loader_install_general.htm
  2. Run the Salesforce Data Loader program.

Performing an Export

  1. Click Export
  1. You’ll be prompted to log in.  Select either Sandbox or Production, depending on what system you intend to log into.
  2. Enter your credentials to log in.
    • Note that in some orgs you may need to click Login with Custom Domain in order to get OAuth to work!
  3. You’ll be redirected to a page to choose the object you want export.  If you don’t see it in the list, check the box next to “Show All Salesforce Objects”, then select the object you want to export.
  1. At the bottom, click Browse… and choose a file name and location for the exported file.  Click Save
  1. You’ll see that the file name at the bottom has updated with the new file and path.  Click Next >
  2. On the next page, you will define the export.  You can either enter a SOQL query that you’ve tested in the dev console, or you can choose fields to display on the left, and then enter filtering conditions on the right (you may need to expand the window to see all fields).  When done, click Finish.
  1. You’ll be prompted to confirm that you want to export the records.  Click Yes.
  1. You’ll receive a progress indicator as it exports the data.
  1. When complete, you’ll be asked if you want to view the data.  You can just click OK or Close.
  2. You can now open the file to review in Excel or Numbers, or upload and open the file in Google Sheets.

Performing an Update

  1. From the Welcome Page, click the Update button.
  1. You may be prompted to log in. If so, see steps 2-6 in the export section.  For step 7, you’ll be redirected back to the page shown here in step 3
  2. Choose a Salesforce Object.  Note that you may need to click the box next to “Show all Salesforce objects”
  1. Click Browse…
  2. Choose the files that contains the records you want to update.  You’ll need at least an id field to identify the records and a column for the field you want to update.  Click Open
  1. The program will process the file to see how many records it is able to process, as well as scanning for issues like the wrong product selected and let you know how many records can be read properly.  If the number of records looks correct, click OK.
  1. You’ll be presented with a screen that looks like this.  If the File Column Headers do not look familiar, click < Back and upload the correct file.  If not, then click Create or Edit a Map.
  1. From here, you’ll be selecting which field in Salesforce corresponds with the column header in your csv file.  You can try Auto-Match Fields to Columns.  It works reasonably well.  Review its selections in the bottom-half of the window.  If any are incorrect or blank, you may have to update the map by dragging components from the top section.
  1. When happy with the mapping, you can click Save Mapping if this is a manual import you expect to perform frequently, or just click OK.
  2. The field mapping should be update now on the Mapping page.  If correct, click Next >.
  1. On this final page, you’ll be asked where you put the log files.  Click Browse… then create and/or select a directory where you want log files to be added.  There will be a success log and an error log for each run, indicating whether the run was successful for that record or not, and why.  After selecting a folder, click Finish.
  1. The program will increment through with whatever batch size you have configured, and provide an update every second or so of its progress on the update.  When complete, it will prompt you to review the log files.  You can do that for the errors, or just click OK.  You’re done!

Thesis: GreekDB

For my Bachelor’s in Computer Science, my university required the completion of a thesis project and write-up. Traditionally students would complete a project requested by their co-op employer, but due to changing my co-op employer at the end of my Junior year, I had to opt for an Entrepreneurial Thesis.

GreekDB is a CRM software geared toward Non-Profits and Membership-based organizations. The solution was developed using PHP, and driven from the reporting requirements of my college fraternity and their parent organization. It features tracking of active and past membership, recruiting information, billing and financial information, budgeting requests, approvals, and transaction reporting, tracking of community service and donations, and more. The system also integrated with providers to allow text-based notifications about payment plans being due and payments received, which would be sent automatically.

In order to keep formatting, I’ve included the original, approved thesis pdf for download below. It’s a fairly long document so if you’re just looking for some technical graphics, some components of note can be found below:

  • Environment Model: p. 39
  • Use Case Models: p. 44-46
  • Data Model: p. 130
  • Navigation and UI Components: p. 134-170
  • Detailed Scenarios and Swim Lane Diagrams: p. 172-186
GreekDB – Bachelors ThesisDownload

Tools Used:

  • Document: Microsoft Word
  • Graphics: Draw.io (similar to Lucidchart, but free)