What the heck are all these Chat settings?

Q: “What the heck are all these Chat settings?”

A: Great question. Salesforce implemented Chat with so many weird objects squirreled away in so many obscure corners.

I assume that’s what happens when they acquire companies left and right and have neither the time nor the inclination to make it tidier!

live-agent-object-model-erd-entity-relationship-diagram-2017-04-27Download

Save Me from “Last Activity”!

Q: “When was this Salesforce record last worked?”

A: If you mean “when was an Activity last completed for it?”, I got a great solution for you.

But first, make sure NOT to use Salesforce’s built-in “Last Activity” field (API Name: LastActivityDate). It behaves really strangely, like allowing “Last Activity” to occur in the future.¹

(I’ve got a full tirade about it. Once you’re done with that, come on back here to implement a Better Mousetrap.)

1) Create a new Activity Custom Field, with Type “Formula (Date/Time)”, named Date/Time MARKED Complete, with the formula

IF(IsTask,
   CompletedDateTime,
   IF(ActivityDateTime < NOW(),
      ActivityDateTime,
      NULL))

2) Create a new Activity Custom Field, with Type “Formula (Checkbox)”, named “Has Date/Time MARKED Complete?“, with the formula²

NOT(ISBLANK(Date_MARKED_Complete__c))

3) Create a new Activity Custom Field, with Type “Formula (Number(0))”, named Days Since MARKED Complete, with the formula²

IF(Has_Date_Time_MARKED_Complete__c,
   NOW() - Date_Time_MARKED_Complete__c,
   999)

4) In a Report that has both Activities and the object you’re rolling up to (like Accounts), create a Custom Summary Formula (I named it Days Since Last Activity MARKED Complete) with this formula

IF(Activity.Has_Date_Time_MARKED_Complete__c:MAX = 1,
   Activity.Days_Since_MARKED_Complete__c:MIN,
   NULL)

You’re done! The Custom Summary Formula field enables reporting when someone last completed an Activity for an Account, Contact, Opportunity, Lead, Case, etc. . .

“Activities with Accounts” Report, with Custom Summary Field “Days Since Last Activity MARKED Complete”

---

¹
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 render, say, an Opportunity’s “Last Activity” in the future. wtf.

²
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.

“Where Is this Used?”

Q: “How do I run ‘Where Is this Used?’ on all custom fields at once?”

A: Jupyter notebook spfy-where-used!

spfy-where-used leverages the “Simple Salesforce” Python library and Tooling API calls to generate a spreadsheet of all custom fields’ “Where Is this Used?” results.

Update the config.py file in the resources subdirectory to reflect your org’s username, password, and security token.

Sadly, “Where Is this Used?” doesn’t cover Workflow or Data Validation. 😐

Preserving Ancient Documents: Showing Images in Fields

I’d found this help document, “Getting Started with Image Fields: Tips, Techniques & Samples”, useful back in the year 2011.

It’d disappeared off its original site, but I had a copy. Enjoy!

sample_image_formula_fields_customization_guideDownload

Bucketing by Hour (Not Day)

For reports that need to bucket by hours—not days, weeks, or months—create a formula like this.

• It returns a date+time string in 'YY: MM/DD HHh format (like '22: 03/23 09h), which is the longest string I could fit in a graph label without Salesforce truncating it.
  
• It uses the American schedule for Daylight Saving. I haven’t yet tweaked it for European Daylight Saving, which diverges from American DS for about six weeks each year
  
• It’s formatted for the the United States’ Central time zone—if you’re in a different US timezone, tweak the 5/24 and 6/24 terms

IF(
    DATEVALUE(CreatedDate) >= DATE(YEAR(DATEVALUE(CreatedDate)), 3, 1) + 
    (14 - CASE(MOD(DATE(YEAR(DATEVALUE(CreatedDate)), 3, 1) - 
    DATE(1900, 1, 7), 7), 0, 7, MOD(DATE(YEAR(DATEVALUE(CreatedDate)), 3, 1) - DATE(1900, 1, 7), 7)))
    &&
    DATEVALUE(CreatedDate) < DATE(YEAR(DATEVALUE(CreatedDate)), 11, 1) + 
    (7 - CASE(MOD(DATE(YEAR(DATEVALUE(CreatedDate)), 11, 1) - 
    DATE(1900, 1, 7), 7), 0, 7, MOD(DATE(YEAR(DATEVALUE(CreatedDate)), 11, 1) - DATE(1900, 1, 7), 7))),
    
    "'" & MID(TEXT(CreatedDate - 5/24), 3, 2) & ": " & 
        MID(TEXT(CreatedDate - 5/24), 6, 2) & "/" & MID(TEXT(CreatedDate - 5/24), 9, 2) & " " & 
            MID(TEXT(CreatedDate - 5/24), 12, 2),
    
    "'" & MID(TEXT(CreatedDate - 6/24), 3, 2) & ": " & 
        MID(TEXT(CreatedDate - 6/24), 6, 2) & "/" & MID(TEXT(CreatedDate - 6/24), 9, 2) & " " & 
            MID(TEXT(CreatedDate - 6/24), 12, 2)
) & "h"

The first clause in the huge IF statement determines whether we’re on Daylight Saving Time

The second clause returns the Daylight-Saving-formatted version of the date/time

The third clause returns the non-Daylight-Saving-formatted version of the date/time

Hamlet Plays Wordle

BEING?
DYING?
TOUGH
QUERY.
NOBLE
BRAIN
AILED
UNDER
COUPS,
LOUSY
FATES?
FIGHT
ANGST-
HEAVY,
UPSET
OCEAN?
DEATH
SEEMS
SLEEP-
ALIKE.

Generate “Random” Numbers Declaratively in Salesforce

Q: How can I use declarative configuration to generate random numbers in Salesforce?

A: The following field formula generates a number from 1 to 100 based on each record’s ID. It isn’t random, but with enough records created gradually over time I hope it’ll produce a reasonable-ish distribution between 1 and 100.

(
  (
    (
      (FIND(MID(Id, 14, 1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz") - 1) * 62 +
      (FIND(MID(Id, 15, 1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz") - 1)
    ) * 99
  ) / 3843
) + 1

What’s going on here?

• The two FIND() functions in the heart of the formula take the 14th and 15th characters of the Salesforce ID and convert them into a number between 0 and 3843 (which is the highest two-digit number in base62).
• The three outer operations—the * 99, / 3843, and + 1—are what’s used to convert
  • from a number that ranges from 0 to 3843
  • into a number that ranges from 1 to 100.
• The conversion logic is cribbed from a Stack Overflow post.

Testing it out, I just inserted 5000 Leads using Data Loader, which I thought would produce a uniform distribution of numbers (again, not random, but uniform).

As you can see, something (in how Salesforce processed the insert job, I guess) led to a doubly-high bulge between 62 and 95, and half the number of 1s and 100s:

My friend Chris Robertson applied it to a table with 3,906 records and got this:

. . . eh, still not great. Let me know what you get!

Better yet, let me know if you have any luck generating random numbers declaratively, say using other “random” number generators like CreatedDate and/or LastModifiedDate. I had no luck with them.