The Power of Two

WIP

Balance of Power

If you have been around Salesforce for any amount of time, there is a good chance you have heard of The Power of One, a brilliant way to perform different calculations on the fly that until very recently simply weren't an option. By just adding a field that always equals 1, you can find totals, averages, and all sorts of other useful statistics in reports dynamically.

But sometimes you need to know something that just doesn't fit a mathmetical method. Sometimes you need more than 1 - you need 2.

The Problem

Actually, there are a lot of them. Sometimes you need to get information on multiple elements of a record. Sometimes you need to account for many true/false values, and you end up with a truly ugly IF() tree. The truth is, there are a lot of situations where you may want to look at many values all together, but you can run into some really unruly formulas very quickly.

I was thinking through a very ugly series of IFs and I just couldn't get it right. There were several different checkboxes, and different combinations that could all mean the same thing. I actually ran into a formula character limit just trying to accomodate all the different states! Somewhere in the back of my mind I kept thinking, "There has to be a better way!"

Finally it clicked - binary flags! Binary values can work like a list of true/false flags if you can read them right. Fortunately we can work with binary numbers by using...drum roll...the Powers of Two! By adding and subtracting powers of two (2² = 4, 2³ = 8, etc), and then using the CASE() function, our formulas can become enormously flexible and expressive, all while staying simple and clearly written.

The Solution

So how can we implement a Power of Two formula in Salesforce? Well first, we need to understand some basic math. Binary is a base 2 number system, and it reads from right to left. Each number position is either a 0 or 1, which is what makes it a great match for tracking true/false values (also known as Boolean values). Binary math looks like:

 01 =   1
+ 1 = + 1
 10 =   2
+ 1 = + 1
 11 =   3

Now that means if we take a decimal (base 10) number, we can convert that into a binary number that can represent true/false values - even if we never see a string of 0s and 1s. If we assign a Power of Two value to our areas of concern we can find all kinds of conditions! For instance, we have an organizational history check process that needs to satisfy multiple steps, but those steps may have different requirements for different people, serving in different US states. The record type has been created with a set of checkbox fields that will be TRUE if a part of the process is missing:

Now, depening on which state the person is in, different rules may apply. Also, depending on where they are in the process, they may be able to participate in some parts of the program, but not others. So normally we would set up a series of IF statements to try to capture the History Check Status:

IF(OR(History_Check_NSOPW_Missing__c, History_Check_Service_State_Missing__c, History_Check_Residence_State_Missing__c, History_Check_FBI_Missing__c, History_Check_Acknowledgement_Missing__c),
    "Missing Steps",
    "Training and Service"
)

That would tell us if they can serve, but not where in the process they are. So let's make it a little more specific:

IF(OR(History_Check_NSOPW_Missing__c, History_Check_Service_State_Missing__c, History_Check_Residence_State_Missing__c, History_Check_FBI_Missing__c, History_Check_Acknowledgement_Missing__c),
    IF(AND(NOT(History_Check_NSOPW_Missing__c), OR(History_Check_Service_State_Missing__c, History_Check_Residence_State_Missing__c, History_Check_FBI_Missing__c, History_Check_Acknowledgement_Missing__c)),
        "NSOPW Complete",
        "Missing Steps"
    ),
    "Training and Service"
)

But that only gets us one more layer. So:

IF(OR(History_Check_NSOPW_Missing__c, History_Check_Service_State_Missing__c, History_Check_Residence_State_Missing__c, History_Check_FBI_Missing__c, History_Check_Acknowledgement_Missing__c),
    IF(AND(NOT(History_Check_NSOPW_Missing__c), OR(History_Check_Service_State_Missing__c, History_Check_Residence_State_Missing__c, History_Check_FBI_Missing__c, History_Check_Acknowledgement_Missing__c)),
        "NSOPW Complete",
        IF(AND(NOT(OR(History_Check_NSOPW_Missing__c, History_Check_FBI_Missing__c)), OR(History_Check_Service_State_Missing__c, History_Check_Residence_State_Missing__c, History_Check_Acknowledgement_Missing__c)),
            "FBI Check Complete",
            "Missing Steps"
        )
    ),
    "Training and Service"
)

Not only would going on this way take a long time, updating it would be a nightmare, and there are variations it doesn't work well with. There may be states that allow people to serve with only an FBI check, and their might be some that don't require an acknowledgement letter sent out. We would need to do an entire convoluted IF series for those scenarios as well. And though there may be better ways to structure these statements (I'm sure there is), they would still fundamentally run into issues with flexibility, readability, and maintainability longterm.

By Our Powers Combined

But what if we look at those checkboxes another way? Then something useful starts to happen:

The state of that record turns out to be something we can represent with a single binary (base 2) number - in this case 01001, or 9 in decimal. We can check that base 10 value against our list of outcomes, we just need to make a Power of Two map, like this:

Values:
History_Check_NSOPW_Missing__c                      = 1 // in this case the Missing fields are counted if they equal FALSE
History_Check_Service_State_Missing__c              = 2
History_Check_Residence_State_Missing__c            = 4
History_Check_FBI_Missing__c                        = 8
History_Check_Acknowledgement_Missing__c            = 16
Agreement__r.State_Policy_Accompaniment_Allowed__c  = 32 // we can even add flags for values from related records, and this will be counted if equal to TRUE

Outcomes:
Accompaniment                                       = 33 (1+32)
Non-Accompaniment (State Cleared)                   = 57 (1+8+16+32)
Non-Accompaniment (FBI Cleared)                     = 51 (1+2+16+32)
Non-Accompaniment (FBI Cleared)                     = 53 (1+4+16+32)
Non-Accompaniment (FBI Cleared)                     = 55 (1+2+4+16+32)
Acknowledgement                                     = 15 (1+2+4+8)
Acknowledgement                                     = 47 (1+2+4+8+32)
Training and Service                                = 31 (1+2+4+8+16)
Training and Service                                = 63 (1+2+4+8+16+32)
None                                                = Default output

Once we have a clear understanding of the map of values, then we can make our formula accordingly:

CASE(
IF(History_Check_NSOPW_Missing__c, 0, 1)
+ IF(History_Check_Service_State_Missing__c, 0, 2)
+ IF(History_Check_Residence_State_Missing__c, 0, 4)
+ IF(History_Check_FBI_Missing__c, 0, 8)
+ IF(History_Check_Acknowledgement_Missing__c, 0, 16)
+ IF(Agreement__r.State_Policy_Accompaniment_Allowed__c, 32, 0)
,
    33, "Accompaniment",
    57, "Non-Accompaniment (State Cleared)",
    51, "Non-Accompaniment (FBI Cleared)",
    53, "Non-Accompaniment (FBI Cleared)",
    55, "Non-Accompaniment (FBI Cleared)",
    15, "Acknowledgement",
    47, "Acknowledgement",
    31, "Training and Service",
    63, "Training and Service",
"None")

Note that when we check a value, in this case true/false values, we need to understand the output we want. Most of those fields are "missing" fields - they become true if information is not on a record. The Agreement field that allows us to use the Accompaniment status works the other way - if it is true, then we need to use that information. So when returning the flag value for each, we need to be sure it goes in the right order in the IF function.

Now, not only is that function more readable, but it is also more flexible. If some states made a rule that only people over 18 could volunteer, we could integrate that by simply adding other cases with just one line, rather than re-writing a complex flow of IF statements:

CASE(
IF(History_Check_NSOPW_Missing__c, 0, 1)
+ IF(History_Check_Service_State_Missing__c, 0, 2)
+ IF(History_Check_Residence_State_Missing__c, 0, 4)
+ IF(History_Check_FBI_Missing__c, 0, 8)
+ IF(History_Check_Acknowledgement_Missing__c, 0, 16)
+ IF(Agreement__r.State_Policy_Accompaniment_Allowed__c, 32, 0)
+ IF(AND(History_Check_Is_Minor__c, Agreement__r.State_Policy_Minors_Disallowed__c), 0, 64)
,
    33 + 64, "Accompaniment",
    57 + 64, "Non-Accompaniment (State Cleared)",
    51 + 64, "Non-Accompaniment (FBI Cleared)",
    53 + 64, "Non-Accompaniment (FBI Cleared)",
    55 + 64, "Non-Accompaniment (FBI Cleared)",
    15 + 64, "Acknowledgement",
    47 + 64, "Acknowledgement",
    31 + 64, "Training and Service",
    63 + 64, "Training and Service",
"None")

If the minor is blocked from serving, none of the values will match allowed status values, so they will default to none. We didn't have to rewrite any IF logic and can even implement other statuses just by matching a number to the output we want our formula to have!

Power to the People

By using the Power of Twos, we can have an extremely flexible solution to complicated outcomes. It can take a little rewiring and thinking ahead about how to map the values, but once your flags are set you can easily manage a wide array of situations. Though the Power of Two is a little more involved than the Power of One, it is a handy tool to have in your belt as you build your formulas!

Adden-dumb

The more I thought about this way of doing things, the more I liked it. I like the math-iness and getting to talk about binary. But then it hit me, you can do this with plain old letters too, and with better readability! A word of warning, the reason binary works so well is that it has a clearly defined difference of values - when we go to use letters, we'll have to do the same! Basically, all we really have to do is build our map with single letters (which might be the first letter of the test case or some other more meaningful signifier), and then check our match from there. So to rewrite the formula above:

CASE(
IF(History_Check_NSOPW_Missing__c, '', 'n')
+ IF(History_Check_Service_State_Missing__c, '', 's')
+ IF(History_Check_Residence_State_Missing__c, '', 'r')
+ IF(History_Check_FBI_Missing__c, '', 'f')
+ IF(History_Check_Acknowledgement_Missing__c, '', 'a')
+ IF(Agreement__r.State_Policy_Accompaniment_Allowed__c, 'p', '')
+ IF(AND(History_Check_Is_Minor__c, Agreement__r.State_Policy_Minors_Disallowed__c), '', 'm')
,
    'np', "Accompaniment",
    'nsr', "Non-Accompaniment (State Cleared)",
    'nfb', "Non-Accompaniment (FBI Cleared)",
    'nfbp', "Non-Accompaniment (FBI Cleared)",
    'nfbpm', "Non-Accompaniment (FBI Cleared)",
    'nsrf', "Acknowledgement",
    'nsrfpm', "Acknowledgement",
    'nsrfa', "Training and Service",
    'nsrfapm', "Training and Service",
"None")

As long as you use each letter only once in your map, and you make sure you put them in order in your matching step, you can use letters and tell right away what a given state covers, and what it doesn't. I'll even admit, when I did this I realized there are a few more cases I need to cover, because some letters were missing.

So, if you like math, the Power of Two is here to help - but if you like letters, you can use that Power, Too.