The Power of Too

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 "too".

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 - condition flags! The original inspriation came from how command line programmers use binary values like a list of true/false flags - more on that at the bottom. But after working out a math-y way to tackle this, it dawned on me letter flags would be more readable and easier to understand. So by mapping our conditions to a letter or symbol and then using the CASE() function, our formulas can become enormously flexible and expressive, all while staying simple and clearly written. This is the Power of Too - using CASE to see if a letter is included too (alright, it's a little weak, but I like the sound of it, and it is in honor of the binary that inspired it).

The Solution

So how can we implement a Power of Too formula in Salesforce?

If we assign a Power of Too 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 short string of letters - in this case 'sa'. We can check that value against our list of outcomes, we just need to make a Power of Too map, like this:

Values:
History_Check_NSOPW_Missing__c                      = n
History_Check_Service_State_Missing__c              = s
History_Check_Residence_State_Missing__c            = r
History_Check_FBI_Missing__c                        = f
History_Check_Acknowledgement_Missing__c            = a
Agreement__r.State_Policy_Accompaniment_Allowed__c  = c // we can even add flags for values from related records, and this will be counted if equal to TRUE

Outcomes:
Accompaniment                                       = nc
Non-Accompaniment (State Cleared)                   = nfa
Non-Accompaniment (State Cleared)                   = nfac
Non-Accompaniment (FBI Cleared)                     = nsrac
Non-Accompaniment (FBI Cleared)                     = nsac
Non-Accompaniment (FBI Cleared)                     = nrac
Non-Accompaniment (FBI Cleared)                     = nra
Acknowledgement                                     = a
Acknowledgement                                     = ac
Training and Service                                = BLANK
Training and Service                                = c
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, '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, 'c', '')
,
    'srfac', "Accompaniment",
    'fa', "Non-Accompaniment (State Cleared)",
    'fac', "Non-Accompaniment (State Cleared)",
    'sra', "Non-Accompaniment (FBI Cleared)",
    'srac', "Non-Accompaniment (FBI Cleared)",
    'sa', "Non-Accompaniment (FBI Cleared)",
    'sac', "Non-Accompaniment (FBI Cleared)",
    'ra', "Non-Accompaniment (FBI Cleared)",
    'rac', "Non-Accompaniment (FBI Cleared)",
    'a', "Acknowledgement",
    'ac', "Acknowledgement",
    '', "Training and Service",
    'c', "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, '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, 'c', '')
+ IF(AND(History_Check_Is_Minor__c, Agreement__r.State_Policy_Minors_Disallowed__c), 'm', '')
,
    'srfac', "Accompaniment",
    'fa', "Non-Accompaniment (State Cleared)",
    'fac', "Non-Accompaniment (State Cleared)",
    'sra', "Non-Accompaniment (FBI Cleared)",
    'srac', "Non-Accompaniment (FBI Cleared)",
    'sa', "Non-Accompaniment (FBI Cleared)",
    'sac', "Non-Accompaniment (FBI Cleared)",
    'ra', "Non-Accompaniment (FBI Cleared)",
    'rac', "Non-Accompaniment (FBI Cleared)",
    'a', "Acknowledgement",
    'ac', "Acknowledgement",
    '', "Training and Service",
    'c', "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! A word of warning, when using letters as flags, be absolutely certain that you are using unique values, or you could end up matching against the wrong flags.

Power to the People

By using the Power of Too, 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 Too 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 1s and 0s

The first way I went about this used a concept called binary flags, which uses powers of 2 to translate between numbers we normally use and the strings of 1s and 0s you see in cheesy computer animations. I liked the math-iness of this method and getting to talk about binary. But then it hit me, you can do this with plain old letters too, and with better readability! The original thought is worth noting though, as it can work in situations where you can't use letters.

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

Think about it like those elementary school counting blocks - the base is how high you can count before you have to start a new stack. No blocks = 0, and when the next block you put on would equal the base number, you start a new stack. Each unit value (the counting block) in base 10, the system we normally use, can hold a 0 through a 9 - then it rolls over to the next digit, so 1+9 = 10. Binary works the same way, but it only gets two unit values, 0 and 1. Once you go to put on that second block, you have to make a new stack!

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.

Basically, all we really have to do is build our map with powers of two (2⁰ = 1, 2¹ = 2, 2² = 4, 2³ = 8, etc), and then check our match from there. So to rewrite the solution above:

Values:
History_Check_NSOPW_Missing__c                      = 1
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

Outcomes:
Accompaniment                                       = 2+4+8+16+32
Non-Accompaniment (State Cleared)                   = 8+16
Non-Accompaniment (State Cleared)                   = 8+16+32
Non-Accompaniment (FBI Cleared)                     = 2+16
Non-Accompaniment (FBI Cleared)                     = 4+16
Non-Accompaniment (FBI Cleared)                     = 2+4+16
Non-Accompaniment (FBI Cleared)                     = 2+16+32
Non-Accompaniment (FBI Cleared)                     = 4+16+32
Non-Accompaniment (FBI Cleared)                     = 2+4+16+32
Acknowledgement                                     = 16
Acknowledgement                                     = 16+32
Training and Service                                = 0
Training and Service                                = 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, 1, 0)
+ IF(History_Check_Service_State_Missing__c, 2, 0)
+ IF(History_Check_Residence_State_Missing__c, 4, 0)
+ IF(History_Check_FBI_Missing__c, 8, 0)
+ IF(History_Check_Acknowledgement_Missing__c, 16, 0)
+ IF(Agreement__r.State_Policy_Accompaniment_Allowed__c, 32, 0)
,
    2+4+8+16+32, "Accompaniment",
    8+16, "Non-Accompaniment (State Cleared)",
    8+16+32, "Non-Accompaniment (State Cleared)",
    2+16, "Non-Accompaniment (FBI Cleared)",
    4+16, "Non-Accompaniment (FBI Cleared)",
    2+4+16, "Non-Accompaniment (FBI Cleared)",
    2+16+32, "Non-Accompaniment (FBI Cleared)",
    4+16+32, "Non-Accompaniment (FBI Cleared)",
    2+4+16+32, "Non-Accompaniment (FBI Cleared)",
    16, "Acknowledgement",
    16+32, "Acknowledgement",
    0, "Training and Service",
    32, "Training and Service",
"None")

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