Over the last couple of posts, we’ve examined some of Monarch functions. Since we’re on a bit of a roll, let’s carry on with the topic.

Today we’ll look at a special category of functions, known as, well, special functions. There aren’t too many, so this might be pretty quick.

If()

Just about every application that allows you to do any kind of analysis has an “If” function, and Monarch is no different. The format is If(condition, result if true, result if false). Incredibly simple, but simultaneously really powerful. If() doesn’t care what kind of data you use for the condition test, as long as it can properly resolve the condition. That means that these conditions are valid, and can be used in an If() function:

MyDateField < {2008-12-31}

ProfitAmount > 999

Left(FullName,3) = "Mr."

but these are not valid in an If() function:

MyDateField < "12/31/2008"

ProfitAmount = "Mr. Bob Smith"

Left(FullName,3) > 999

As well, you can include, or “nest” multiple If() functions together in one expression, such as:

If(ProfitAmount>0,If(ProfitAmount>500,"Great","Good"),"Poor")

In this example, a ProfitAmount of 300 would be “Good”, 800 would be “Great”, and -100, a loss, would be “Poor”.

IsEmpty()

The IsEmpty() function tests to see if a field is empty. I suspect that this is really meant to test character or memo type fields, but Monarch’s online help doesn’t specify that requirement. The function returns a numeric value, either one if the test field is empty, or zero if it isn’t. So if the field MiddleName is blank,

IsEmpty(MiddleName)

would return a one, but if the field held the value “Stephen”, it would return a zero. Close to the IsEmpty() function is…

IsNull()

Where IsEmpty() tests for (I suspect) an empty string, IsNull() tests for absolutely nothing. That is, there’s no value in the field at all. Since IsEmpty works well for character or memo type fields, I would use IsNull() exclusively on numeric or date fields. And just like IsEmpty(), IsNull returns a one when the test is true, and a zero when the test is false.

So if your report has a field where a date is optional for a specific record or transaction type, you can test to see if a date exists for a record before you do a calculation with that date. Let’s suppose that you need to calculate a due date of 30 days after an invoice date, when an invoice date exists, that is. The formula would be:

If(IsNull(InvDate,CtoD("","m/d/y"),InvDate+30)

CtoD(”",”m/d/y”) properly returns a null value to a date type field.

The last two functions in the “special” category are:

Rand() and RandEx()

They both generate a random integer value. Rand() gives you a value between zero and 32,767. RandEx() goes considerably higher; from zero to 4,294,967,295. Random number generators have many practical purposes, but honestly, I rarely use them. In fact, I use Rand() from time to time for creating data from scratch in Excel, but I really don’t recall having ever used either of the random functions in Monarch.

That’s a Wrap

As I said at the top, the list of functions in Monarch’s “special” category of is pretty short. But mastering these functions, especially If() in it’s multiple instance version, can be key in your ability to excel with Monarch.