The Basics of Building Monarch’s Expressions

Avoiding Function Malfunctions

Apart from being aware of the functions at your disposal, and how they’re used, it’s really important to understand how the software is going to interpret your functions, and the order in which you want calculations to take place.

Central to this process is the topic of Boolean logic. Don’t get scared off if it sounds like computer science stuff. It’s really not that complicated.

With such a fundamental topic that could apply to a vast audience, you’d think that I’d be able to easily find a nice web site that I could direct you to for clear, simple instruction. No dice. I tried, but came up empty. I’m sure there’s one out there somewhere, but I gave up. So here goes.

Simple math has four basic operators: add, subtract, multiply and divide. For our purposes, we need to focus on three Boolean operators: AND, OR, and NOT. Additionally, I need to tell you that Boolean data has only two values: TRUE and FALSE.

The Truth is Out There

To run through the possibilities, typically “Truth Tables” are used to demonstrate these combinations and their results. Usually two variable values are used to come up with examples. A variable value is typically a field or part of a field.

The result of an AND operation is only TRUE when both variables are TRUE, as follows:

The result of an OR operation is only FALSE when both variables are FALSE, as follows:

The NOT operation simply produces the opposite value, as follows:

“What on Earth does any of that have to do with Monarch?”, you ask. With this background as our foundation, we can look at how Monarch will resolve our expressions for our filters and calculated fields and such.

Let’s suppose you want to filter your monthly divisional sales report to give you the total number of cases of products sold by Bob Smith. Your report lists multiple salespeople and units appear as EA, CASE, and PLT.

Your expression would be:
Salesperson = "Smith, Bob" .And. Units ="CASE"
What if Bob and Jane Peters are your only Western region reps, and you need cases sold in the Western Region? One solution would be:
(Salesperson = "Smith, Bob" .Or. Salesperson = "Peters, Jane") .And. Units ="CASE"
This introduces the topic of order of operations; the order in which Monarch will resolve the function.

Order in the Court!

Let’s use Excel for a moment. When you key the formula =8/4+2*3-1, Excel first calculates multiplication and division, then addition and subtraction. Further, it will calculate from left to right. So the answer to the formula is 7.

When you introduce parentheses, you tell Excel to calculate whatever is inside the parentheses first, and then get on with the rest of it.

Let’s change our formula slightly to include parentheses. What’s the answer to (8/4+2)*3-1? Right, it’s 11.

Let’s get back to our Monarch filter. When Monarch finds a record for the salesperson “Dawson, Mike” and the units are “CASE”, what happens?

Monarch will resolve both
Salesperson = "Smith, Bob" and Salesperson = "Peters, Jane"
as FALSE, and Units = “CASE” as TRUE, so what it really sees is
(FALSE .Or. FALSE) .And. TRUE
Resolving the order operations, we first get
(FALSE) .And. TRUE
And finally
FALSE
This means that Monarch won’t include this record in the displayed records.

Achieve Balance

You may find that in order isolate multiple conditions; you need to use many sets of parentheses in your expression. This can get somewhat confusing, as you must balance the parentheses; that is, for every ( you must have a ).

A common trick used to make sure the expression is balanced is to count the ( characters. Start with 1, then add 1 for every (. Then deduct 1 for every ). You must end up with a zero value at the end, or you’re out of balance somewhere.

If you end up with a positive value, you’re missing one or more ) characters or you have too many ( characters. The reverse is true if the value is negative.

Here’s an example often used in older versions of Monarch Standard to derive abbreviated month names from the month number of a date (Pro versions provide a better way to do this – we’ll talk about that on another day):
If(Month(MyDate)=1,"Jan",
If(Month(MyDate)=2,"Feb",
If(Month(MyDate)=3,"Mar",
If(Month(MyDate)=4,"Apr",
If(Month(MyDate)=5,"May",
If(Month(MyDate)=6,"Jun",
If(Month(MyDate)=7,"Jul",
If(Month(MyDate)=8,"Aug",
If(Month(MyDate)=9,"Sep",
If(Month(MyDate)=10,"Oct",
If(Month(MyDate)=11,"Nov",
"Dec")))))))))))
Keep these principles in mind when building expressions and you’ll soon excel with Monarch.