Blog: Salesforce #DataTipOfTheDay, Tips & Tricks, and Whatever Else

Save Me from Last Activity Date

posted Dec 30, 2019, 7:30 AM by Ezra Kenigsberg   [ updated Jan 2, 2020, 9:18 AM ]

Q:    How do I determine the last time a user completed an Activity tied to a record?


A:    DO NOT USE Salesforce’s built-in Last Activity Date. Last Activity Date can occur in the future.¹

INSTEAD: Define better Custom Fields on Activity, and create a Report from a Custom Report Type to roll that field up to the desired object. Perform these five steps:


  1. Create the Custom Date Formula Field “Date MARKED Complete” on Activity:

DATEVALUE(IF(IsTask,

             CompletedDateTime,

             IF(ActivityDateTime + DurationInMinutes / 1440 < NOW(),

                ActivityDateTime + DurationInMinutes / 1440,

                Null)))


  1. Create the Custom Checkbox Formula Field “Has Date MARKED Complete?” on Activity²:
    NOT(ISBLANK(Date_MARKED_Complete__c))

  1. Create the Custom Number Formula Field “Days Since MARKED Complete” on Activity²:

IF(Has_Date_MARKED_Complete__c,

   TODAY() - Date_MARKED_Complete__c,
  999)

  1. Create a Custom Report Type that summarizes your object (I'm using Opportunities in this example) with and without Activities:


  1. In a Report built from that Custom Report Type, create a Custom Summary Formula called “Days Since Last Activity MARKED Complete”.

    If you want the Report to return a Null if no Activity has been marked Complete for this Opportunity, use this formula:

IF(Activity.Has_Date_MARKED_Complete__c:MAX = 1,
  Activity.Days_Since_MARKED_Complete__c:MIN,
  Null)


If you want the Report to return a 999 if no Activity has been marked Complete for this Opportunity, use this formula:

Activity.Days_Since_MARKED_Complete__c:MIN




¹ Q:    How can Last Activity Date occur in the future?


A:   Salesforce Help explains that Last Activity Date is either
  • The latest Due Date of the Closed Tasks on a record, or

  • The latest Date of Events on a record.


Out-of-box, Salesforce allows both

  • completed Tasks to have Due Dates in the future or

  • Events to have Dates in the future

. . . so either Tasks or Events can make an Opportunity’s Last Activity Date return a future date.


² Q:   Why are these fields needed?


A:   Two reasons:
  1. Reports can’t roll dates up to parent records, so we need to convert “Date MARKED Complete” into the numeric “Days Since MARKED Complete”.

  2. Reports’ Custom Summary Formulas treat nulls as zeroes, so we need an arbitrary, large error value--I’m using 999 here--to disqualify null “Days Since MARKED Complete” from MIN calculations.

A Better Way to Bypass Data Validation

posted Oct 30, 2019, 9:53 AM by Ezra Kenigsberg   [ updated Oct 30, 2019, 9:58 AM ]

Q: "How do I make a record bypass Data Validation?"

A: The Clever Way:
  1. create a checkbox called "Toggle Me to Bypass Validation" (or something like it); and
  2. change your Validation Rules to ignore records where "Toggle Me to Bypass Validation" has changed--add a clause like NOT(ISCHANGED(Toggle_Me_to_Bypass_Validation__c)).
You're done!

In the past, folks implemented this the Adequate Way:
  1. create a checkbox called "Bypass Validation Rules for this Record" (or something like it);
  2. change your Validation Rules to ignore records where "Bypass Data Validation for this Record" is TRUE--add a clause like NOT(Bypass_Validation_Rules_for_this_Record__c); and
  3. create a Workflow Rule to change "Bypass Data Validation for this Record" back to FALSE whenever it's set to TRUE.
The problem with this approach: step 3.
  • The Workflow Rule changes the record AGAIN, which--depending on your config--could cause logic to re-run. suboptimal.
  • The Clever Way only touches the record once.
  • If you need to programmatically bypass Validation Rules for an existing record, set the record's "Toggle Me to Bypass Validation" to NOT(Toggle_Me_to_Bypass_Validation__c) in your Workflow, Process Builder, Flow, or Apex logic.
Props to Hanna Martynenko for the idea!

Get outta my Taskbar, Slack!

posted Aug 3, 2017, 10:14 AM by Ezra Kenigsberg   [ updated Aug 3, 2017, 11:07 AM ]

Q: "Why can't I minimize Slack to SysTray? I activated Slack's 'Leave app running in notification area when the window is closed' option. Why does the Slack app stay in my Taskbar?"

A: There's a separate setting, "Flash window when receiving a notification", that has to be turned off.

Lemme save 1E3 words:

Props to mak4k on Reddit for solving this thing that's been driving me nuts for months.

Less intrusive Google Calendar alerts

posted May 3, 2017, 9:55 PM by Ezra Kenigsberg

Q: "How do I prevent Google Calendar from annoyingly interrupting whatever I'm doing in Chrome?"

A: Turn on "Use browser notifications instead of interruptive alerts" in Calendar's Settings:

Here's a comparison of the two dialogs. The former is a modal dialog, the latter is a toaster pop-up.

Keeping all the Live Agent settings straight

posted Apr 26, 2017, 3:20 PM by Ezra Kenigsberg   [ updated Apr 27, 2017, 11:30 AM ]

Q: "Deployments, Settings, Configurations. . . how can I keep all the Live Agent stuff straight?"

A: Try the Live Agent "Object Model" and let me know what you think!

Put a single Google Drive file in multiple folders

posted Apr 19, 2017, 11:30 AM by Ezra Kenigsberg   [ updated Apr 19, 2017, 11:31 AM ]

Q: "How do I put a Google Drive file (a Doc/Sheet/Presentation, or any old file) in multiple folders?"

A: Shift+Z!

I don't know why the Google Drive people hid this very necessary function in such an obscure corner, but there it is. 

Once you press Shift+Z and select another folder for the file to live in, that single file will be accessible from multiple folders.


How can you know where a file is shared? Details pane!

Props to Digital Inspiration for the tip!

Set defaults for a new Google Sheet

posted Apr 10, 2017, 10:04 AM by Ezra Kenigsberg

Q: "How do I set my own defaults (eg, font, size, header rows) for a new Google Sheet?"

A: Two answers, depending on the version of Google Sheets you're using. 
If you're using a paid version of Google Apps, you can use a template:
  1. create a Sheet that has all the default settings you want, 
  2. navigate to your organization's Template Gallery, and
  3. click the "Submit Template" button to establish your Sheet as a template.
From then on, select "From a template" from the submenu of either
  • Drive's "New | Google Sheets" button or
  • Sheets' "File | New" command.
If you're using a free version of Google Apps:

Create a Sheet that has all the default settings you want.

From then on, navigate to that Sheet, then click "File | Make a copy".


When does Salesforce automatically create a Campaign Influence record?

posted Mar 23, 2017, 10:31 AM by Ezra Kenigsberg   [ updated Mar 23, 2017, 10:33 AM ]

Q: "When does Salesforce auto-create a Campaign Influence record?"

A: Salesforce automatically creates a Campaign Influence record--an association between an Opportunity with a Campaign--under these conditions.*
  1. Auto-Association must be active;
  2. a Contact is assigned an Opportunity Contact Role prior to the Opportunity's Close Date; and
  3. that same Contact is associated to a Campaign (as a Campaign Member).

* I'm not gonna get into Custom Campaign Influence Models here.

Accessing Campaign Influence records via API

posted Mar 22, 2017, 1:38 PM by Ezra Kenigsberg   [ updated Mar 23, 2017, 10:03 AM ]

Q: "How do I access Campaign Influence records via the API?"

A: Gotta carry out four steps:
  1. Turn on Customizable Campaign Influence.
  2. Assign user(s) the "CRM User" or "Sales Cloud" Permission Set Licence.
  3. Create a Permission Set that includes the "Campaign Influence" App Permission.
  4. Assign user(s) to that Permission Set.
Followup Q: "What does this do for me?"

Followup A: Buncha things.

Advantages:
  • Enables loading "associate this Campaign to this Opty" via the API
  • Enables credit for Campaigns to be broken out in percentages--instead of Primary Campaigns getting 100% credit 
  • On Campaign Page Layout: replaces old "Opportunities" Related List with "Influenced Opportunities" Related List, which shows
    • All influenced Optys, not just Primary Optys
    • Influence (%)
    • Revenue Share ($)
  • On Opty Page Layout: upgrades "Campaign Influence" Related List to show
    • Influence (%)
    • Revenue Share ($)
Disadvantages:
  • This REMOVES the "Add to Campaign" button from Opportunity's "Campaign Influence" Related List:*
  • This BREAKS the old Related Lists on your Campaign and Opportunity Page Layouts. Follow the steps here to set up the NEW AND IMPROVED Related Lists.
* This can be addressed by creating an unlocked Custom Campaign Influence Model, but that brings its own challenges (most notably, you gotta create homebrew Auto-Assign Rules using Workflow, Flow, Triggers, or--ye gods--Process Builder).

New Google Sheet, fast

posted Mar 14, 2017, 12:03 PM by Ezra Kenigsberg   [ updated Jan 17, 2020, 7:40 AM ]

Q: "How do I make a new Google Sheet, quickly?"


I just created an AutoHotKey shortcut for myself so that I can get spreadsheeting with Google Sheets almost as fast as I do with Excel. 

Gasp. After 25 years of Excel (and Lotus 1-2-3; and WordPerfect and Word; and Harvard Graphics and Freelance Graphics and PowerPoint)--will my default "I've gotta make a document" impulse be toward a cloud-based app?

Which reminds me: The History of VisiCalc is one of my favorite pages on the web. Nerdriffic!

* They do the same as the Google URLs:

1-10 of 171

Comments