Buggy Booleans

I've been cleaning up a lot of address/phone/email data that got mis-synced (or not synced at all) between our ERP and SalesForce this week.

(I'm supposed to be making error reports of SalesForce/ERP data that is no longer in sync due to humans changing it in only one system or the other, but to make such reports meaningful, I've had to eliminate all the bulk mistakes.)

 

Typically, for such single-object (Contact) errors, I make a formula field to detect the error ("WHERE").
I make a List View on Contact to show the fields that help a human decide how to react to the error ("SELECT...FROM...").
Why List Views? Reports don't let you filter a list based on the value of a formula field. List Views do.

 

You'd think that "checkbox" - a Boolean - would be the perfect data type for an error-condition-checking formula field. Who doesn't love a Boolean? It's the ultimate in concreteness and unambiguity - everything a programmer loves.

SalesForce. SalesForce doesn't love a Boolean, that's who. :-(

 

Most filters in SalesForce can only do string and numeric comparisons (and "is null" & "is not null" are often missing). Don't count on anything but other formula fields being able to have a sense of "is checked/selected" or "is not checked/selected."

List View filters can't handle "is checked."
Surprisingly, neither can the filters in "power-tool" software MassImpact by DemandTools.

 

So I've made a lot of text-typed formula fields this week that return "true" and null.
(Although since I'm returning a string anyway, I have decided to return a substitute for "true" like "BannerPhoneNotInSF" so I don't get similar-looking formulas mixed up with each other when I have them pasted into two NotePad++ windows at the same time. That habit saved my bacon today.)

 

Which reminds me of another thing I've been doing this week: A lot of relying on NotePad++ to edit formula fields. I find it extremely helpful to edit nested code there and paste it back into the Formula Field editor window in my browser at the last minute. I don't know what I'd do without my indentation management and little red close-parentheses.