We all have a couple of phrases we say all of the time which are probably despised by our friends because they have heard them so often. One of mine is:
“Don’t make this hard”
On a recent post by Richard Whaley on the Dynamics GP Newsgroup, he suggested adding up the values in a field to see if the field was populated. If the resulting value was greater than zero, the field was populated.
With that in mind, let’s explore the world of Boolean logic. I like it, no gray areas, no fancy nested conditional statements, it’s either true or false. Pretty easy.
True or False?
Often we find ourselves building IF statements that go through multiple logical tests. We string together a bunch of AND, OR, and NOT functions in order to test our data and arrive at a conclusion. Throughout this complex statement, all we are doing, really, is making up a group of True or False computations. Think about it, each step of our equation is either true or false. If it is False it is 0 and if it resolves to True it is 1. Such is the logic behind the ‘IF, THEN, ELSE’ statement. So before you reinvent the wheel with some monster conditional structure, consider breaking it down into a logic statement and see if you can get to your answer more quickly and more elegantly.
Before I go into the example, write down these cornerstones for Boolean logic problems:
Cornerstones for Logic
FALSE is Zero (0)
Add (+) if you want ‘OR’
Multiply (*) if you want ‘AND’
Anything else is TRUE (1)
An Example in Logic
Scenario: A 5% bonus is paid to salespeople who have sales over $50,000 so long as their gross profit percentage is greater than 30%. Oh, and it is always paid to ERIN J. because she has a special relationship with Aaron Fitz. Your job is to figure the bonus amount for each sale.
OK, let’s take this apart. The bonus is paid IF:
(Sales > $50,000 AND GP > 30%) OR Salesperson ID = ERIN J.
Programmatically that would look something like this:
IF(OR(AND (Sales > 50,000, GP > .3),
Salesperson = “ERIN J.”),
.05 * Sales, 0)
If we could create a calculation to solve this logical test, we could check the value of the resulting number. If the number is 0, the answer is FALSE. If it is anything else, the answer is TRUE. So if the answer is TRUE, then the bonus is paid, otherwise it is not.
Using the cornerstones we set out above, let’s solve this problem with Boolean logic.
Sales > 50,000 AND GP > 30% would turn into:
Sales > 50,000 * GP > .3
The result of this calculation is 0 if either of the components is not true. The result is 1 if both components are true.
If that was all of our criteria, then we could just multiply this formula by the bonus calculation and we would have our answer. The whole formula would look like this:
Bonus = (Sales > 50,000) * (GP > .3) * .05 * Sales
We have another wrinkle that we need to factor in, however. ERIN J. always gets the bonus, just because she is ERIN J. So now we have a big fat OR clause in our logic problem.
Our cornerstones say that an OR clause should be converted to an addition operation. On its face we might rewrite the formula to this:
Bonus = ((Sales > 50,000) * (GP > .3)) + (Salesperson = ERIN J.)
We need to be careful here because if ERIN J. had a sale that qualified for a bonus in its own right, then the result of our formula would be 2. ERIN J. may like this outcome, but the stockholders would not. In order to avoid this, we need to convert the result to TRUE or FALSE, or 0 or 1.
The restated formula would look like this:
Bonus = If((Sales > 50,000) * (GP > .3) + (Salesperson = ERIN J.) 1, 0) * Sales * .05
Breaking it down into its components, you can do this easily even in Report Writer. Boolean logic turns it into 3 conditional fields and 3 straight calculated fields.
- Calc 1, conditional = if Sales > 50,000 then 1
- Calc 2, conditional = if GP > .3 then 1
- Calc 3, calculated = Calc 1 * Calc 2, this will be 1 or 0
- Calc 4, conditional = if Salesperson = ERIN J. then 1
- Calc 5, calculated = Calc 3 * Calc 4 this will be 1 or 0
- Calc 6, calculated = Sales * Calc 5 * .05 =The Bonus Amount
If you try and convert your IF statements into Boolean logic, you may find that they are much easier to understand and faster to write.
Don’t make this hard!
Until next post,
Leslie
