Q: The text field on a record shows a list of values with the selected value designated by an “[X]” (like “one, two [X], three”). How can I get a formula field to show just the chosen value, like the below?
A: Here’s the formula:
IF( /* is "[X]" in the string? */ CONTAINS(Other_System_Picklist__c, "[X]"), /* if "[X]" is in the string, then. . . */ IF( /* does a comma precede the "[X]" value? */ CONTAINS(LEFT(Other_System_Picklist__c, FIND("[X]", Other_System_Picklist__c) - 2), ","), /* a comma precedes the "[X]" value. do some fancy stuff */ REVERSE( LEFT( REVERSE(LEFT(Other_System_Picklist__c, FIND("[X]", Other_System_Picklist__c) - 2)), FIND(",", REVERSE(LEFT(Other_System_Picklist__c, FIND("[X]", Other_System_Picklist__c) - 2))) - 1 ) ), /* no comma precedes the "[X]" value. do some less-fancy stuff */ LEFT(Other_System_Picklist__c, FIND("[X]", Other_System_Picklist__c) - 2) ), /* no "[X]" in the string */ "#N/A" )
quick observations:
as my friend Martin pointed out, this would be easier if Salesforce supported Regular Expressions in formulas.
Salesforce doesn’t, although you can use a limited REGEX() function for matching in stuff like Validation Rules.
a formula function that enabled searching from the end of a string (like InStrRev or rfind) could help, too. gotta use REVERSE() weirdly instead!
thanks to Daniel Parkhurst for the challenge! I look forward to that tasty beer next time you’re in town 😁
remember that the functions that end in _QUARTER() or _MONTH() don’t include the year, so you may want to pair them with a *_YEAR() function.
For example, the badboy below groups Opportunities by the year-and-quarter they were created over the last three years: SELECT CALENDAR_YEAR(CreatedDate), CALENDAR_QUARTER(CreatedDate), COUNT(Id) FROM Opportunity WHERE CreatedDate = LAST_N_YEARS:3 GROUP BY CALENDAR_YEAR(CreatedDate), CALENDAR_QUARTER(CreatedDate) ORDER BY CALENDAR_YEAR(CreatedDate), CALENDAR_QUARTER(CreatedDate)
the THIS_MONTH and LAST_N_YEARS items in the WHERE clauses above are date literals, and there are a whole gaggle of handy ones.
I haven’t thought of a simple way, in declarative SOQL, to perfectly say “going back to today’s date, X years ago”. Unless the number of years ago is divisible by 4, you’re likely to include or exclude one day too many, because of leap years.
WHERE CreatedDate = LAST_N_DAYS:365 might be a day too few!
WHERE CreatedDate = LAST_N_DAYS:366 might be a day too many!
WHERE CreatedDate = LAST_N_DAYS:1461 (that is, 365 * 4 + 1) should always work!
Yah, I caved and finally bought me a smartwatch. For a guy who works in tech, I have some Luddite tendencies–I generally only pick up a new technology when I see a clear use case for it.
I’d been ruminating buying one for awhile (I was particularly jazzed about the concept of an e-ink smartwatch).
But at the end of the day, my concrete use case was a device that (a) worked like a phone when I took it running (out of Bluetooth range of my iPhone), and (b) wasn’t gigantic on my ladylike wrist. Add those up and it meant I was going for a cellular model of the latest, smallest Apple Watch, the 41mm Series 9.
The watch’s purpose is to protect me from the phone, which is kind of breathtaking when I think about it: I purchased device #2 to protect me from device #1. But don’t take my word for it–it’s related in this entertaining Wired article from 2015:
Along the way, the Apple team landed upon the Watch’s raison d’être. It came down to this: Your phone is ruining your life.↩︎
I created an “Insert Current Date as ISO” shortcut from this post.
(I followed the instructions in the section titled “Alternatively, Use Automator Yourself” because I’m a sucker for learning by doing things the laborious manual way. Errors are the portals of discovery and all that!)
Why? Because that way I can update the date whenever I make a meaningful revision to the file.
The date a file was last modified isn’t always helpful. There are plenty of times I might tweak a file’s format, or fix a minor typo–and those changes don’t rise to the level of a “revision”, imo.
Consciously choosing whether or not to revise the date at the end of a filename helps me truly know its sell-by date.
Here’s an example of a random folder on my drive, filled with assorted files and documentation: ↩︎
. . . notice how
the notesy files capture the date I took the notes (at the beginning of the filename, which 1. alphabetizes them at the top of my folder, 2. in chronological order), and
the Visualforce file “Custom Console Component – Example 2017-04-13.vfp” has a date at its end, which captures the last time I made any meaningful revisions to it.
Q: “How do I make a Salesforce record bypass Data Validation?”
A: The Clever Way:
create a checkbox field on the desired object called “Toggle Me to Bypass Validation” (or something like it); and
change your Validation Rules on the desired object to ignore records where “Toggle Me to Bypass Validation” has changed–add a clause like NOT(ISCHANGED(Toggle_Me_to_Bypass_Validation__c)).
create a checkbox called “Bypass Validation Rules for this Record” (or something like it);
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
create a Flow or Apex Trigger 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 Flow or Apex Trigger 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 Flow or Apex logic.
Kudotek v2.0 