I’ll be among the first to say that being a desk jockey can be tough on a person. Long days virtually tied to a desk really aren’t healthy, so after a bit of an extended absence from sports, I’ve recently returned to cycling to get more exercise. Being somewhat apprehensive, I was careful getting on my bike. But it’s true that you really don’t forget how to ride. Your confidence grows quickly and you develop a “can-do” attitude.
For day 5 of the 30 Days to Become a Better Monarch Modeler series we’ll get comfortable with the methods of defining and controlling field content for extracted fields, so as to be able to build our Monarch models to mine data from reports and other sources as quickly and as accurately as we can. Extraction fields differ significantly from the calculated fields with which we add new data to the data from the original data source.
Fields of Dreams
Extraction fields are defined while building templates, and the mechanism for controlling the action of the field are handled within a two tabbed dialog box. The first tab is for the General options, which includes:
- The name of the field, which can up to 62 characters in length. The supplied field name can control most characters including spaces and underscores, and both upper and lower case letters can be used. See the online help for the list of the few characters which are not permitted for field names.
- The type of content within the field. This can be:
- Character,
- Date/Time,
- Numeric, or
- Memo
- If the field is numeric, the number of decimal places can be specified (from 0 to 9 places are permitted).
- The format with which to display the data. There are several options, each of which is dependant upon the field type. Monarch guesses as to default to the proper data format based on the extracted content of the field, but this can be overridden.
- For Character fields, only the option (which doesn’t make it an option then, does it?) available is General. Basically what you see is what you get with General formatting.
- For Date/Time content, we have the following available formats (for which Monarch displays a short example for reference):
- General,
- Short date,
- Long date,
- Short date & time,
- Long date & time, and
- Time
- For Numeric content, we have:
- General, once again,
- Thousands,
- Currency,
- Percentage, and
- Time span
- The number of characters to display for this field, or its width.
- A checkbox to use if you want to hide the field in the Table window.
- The alignment style: left, center, or right alignment.
- The template width. Unlike the display width, this positive integer value indicates the number of characters to extract from the report. This option can be a little confusing, as the Monarch technical specifications indicate that a Memo field can contain over 65,000 characters. So you’d think that you can set number quite high for wide reports, such as when the report has over 1,000 columns for example. But you can’t. The upper limit for template width is only 254 characters.
- Then there are options on how to control the verification process, which helps to determine if the fields have been painted properly. You can elect to check the left and right sides of the field, as well as the field type.
- Finally, you can elect to leave empty cells blank, or to use the value from the field in the previous record.
I do have one additional tip related to the simple act of naming your fields that can save a lot of time and effort later on if you plan on using your extracted data in a database that can be programmed with SQL (such as Microsoft Access), and it’s this: don’t use reserved SQL keywords as field names.
That’s a bit easier said than done as there are a good number of SQL keywords.
It might not be your time and effort, but some programmer one day will thank you.
That brings up yet another tip: don’t use built-in Monarch function names as field names.
The confusion that you’ll experience when building and reviewing calculated fields and filters is completely avoidable. Instead of naming the dates from your Accounts Receivable report “Date”, name them “InvoiceDate” or “InvDate”. Anything but “Date”.
Keep in mind though that Monarch doesn’t care what you name your fields, so long as you’ve used legal characters to do so.
Advanced Monarch
On the Advanced tab of the dialog, there are two sections that affect the data extraction. The first section affects the control of where the field begins and contains the following options:
- A specific line number. This is based on the line on which the field appears within the template sample.
- A string, or a series of characters, that appears at any position within the line immediately prior to the line in which the field exists.
- A string that appears within the same line as the field and before the field.
- After the last defined field in the template, or more clearly, use this option for a field that appears two lines after the last line of multi-line field which is also extracted in this template.
The second section controls how to terminate the field, including:
- After a specific number of lines.
- Following a non-blank series of n characters which end immediately prior to the beginning of the field, in a row below the start of the field.
- Following n blank field values. This refers to the position on the line that has been painted for the field. For instance, if you specify 2 blank field values, then a section of text that has a single blank line in the middle will be captured in its entirety, until such time as there are 2 blank portions for that position in the line.
- Until the end of left justification is reached. This is often the method to choose when defining text to use in conjunction with the address block feature. Just be careful that the last line doesn’t have a leading space, as Monarch will miss that line in that case.
- The classic “none of the above”, which really means that Monarch should capture this field until it runs into data which meets the trapping criteria for another template. It’s a bit of an odd choice, as Monarch will always enforce this rule regardless of the “end field on” option that was selected.
Your Task for Today
I’m almost 100% sure that when you first began to learn and use Monarch you used the sample reports that are provided in conjunction with the supplied Learning Guide. But after going through it once (maybe some time ago?) you checked that task off on your to-do list and moved on.
Today I want you to open each of the sample reports and build a model for each report to do basic extractions. Do this without using the Learning Guide. Base your activities only on what we’ve covered to date in the 30 Days to Become a Better Monarch Modeler series.
If you run into content that you can’t model without venturing into Monarch’s other abilities, skip it for now.
Define Your Success
Life is like riding a bicycle – in order to keep your balance, you must keep moving. ~Albert Einstein
Building Monarch models gets a bit tricky at times because there are so many variations and possibilities in the reports that each of us use regularly. You simply might not run into the need to use all of the Advance field tools in your frequent model building tasks.
But by practicing a little, and being aware of the results that each option can provide you, you’ll be able to build models accurately and quickly. Keep moving forward and you’ll soon excel with Monarch.
—
Continue your commitment to Become a Better Monarch Modeler with Part 6 of the series, or review Part 4.
