Is There a Secret Ingredient in This Formula?
Yesterday we looked at the field types available when building calculated fields. Today we’ll examine the rest of the process of defining a calculated field.
The mechanism Monarch provides for building calculated fields allows us to select from the list of all existing fields, logical operators and a slew of functions.
Operator, could you help me place this call?
Monarch allows you to use a number of operators, including:
- Math (+, -, *, /, and ^),
- Comparison (=, <>, <, <=, >, >=),
- Grouping (enclosed in parentheses),
- Dates (enclosed in curly braces),
- Square brackets (used for field names with embedded spaces),
- Double quotes (used to enclose character strings),
- Comments (/* followed by */), and
- Boolean logic (.And., .Or., .Not., .In(), and .NotIn()
Review our discussion of the basics of building expressions if necessary.
What’s my Function?
In order to more easily find the functions we may need at any time, they’ve been nicely organized into groups. You can opt to show:
- All functions,
- Date functions,
- String functions,
- Numeric functions,
- Conversion functions,
- Special functions,
- Report functions, and
- User-defined functions.
Showing “All” functions lets you pick from an alphabetically sorted list.
Yesterday we discussed making sure that we use the correct data format for the field type you intend to use. Of special use to help us make sure that we get the data types right are the conversion functions.
Some of the things conversion functions do for us are:
- change numbers into characters (they look the same to you and I, but they’re entirely different to Monarch),
- change character strings to dates,
- change dates to strings, or
- change characters into numbers.
Another useful group of functions are those that generate information for us that is derived from the report itself, not the data within the report. For instance, you can read the file name of the report, or the page number on which the data you’ve extracted appears.
There’s a group of functions in the Special group. These are really cool. Functions here let you use multiple calculations based on your data values, or even generate test data from scratch.
Express Yourself!
It’s in the expression box that you define what you want your calculated field to do. As of Monarch v9, you can use over 32,000 characters in your expression. But as with a number of other things in this life: just because you can doesn’t mean you should. But if you really, really, really can’t find another way…
The final tool available to us is on the Test tab of the dialog box. Every field that you used in your expression will be listed in a grid, along with its value.
Clicking the Test button shows you the value that is generated by your expression.
Coming Up Next: When Good Functions Go Bad!
What sort of things can go wrong when you build expressions? Monarch will give you errors if you:
- don’t supply the required number of parameters for the functions you used,
- send the wrong type of data to a function,
- don’t use the correct number of left and right parentheses (you must have a right parenthesis for every left parenthesis),
- don’t use square brackets when using field names with embedded spaces, like “Customer Name”, or
- simply typos, leading to functions that don’t exist like Sr, instead of Str.
That’s it for the basics of building formula-based calculated fields. Monarch gives you the basic building blocks. The rest is up to the secret ingredient: your creativity.
Monarch offers you plenty of tools and choices insofar as building calculations that can add to the functionality of your models. Get to know the basics, and what’s possible with a little research when necessary, and soon you’ll excel with Monarch.
