Having successfully extracted data from our report sources by defining custom templates to match the requirements of our reports, using what we’ve studied so far in the 30 Days to Become a Better Monarch Modeler series, we’re now likely gathering a quite a collection of data in the Table window.

At this point it might be becoming a little difficult to see our data, or organize it in the manner in which we prefer. To that end, today on day 9 we’ll discover how to tame the beast that can be the Table window.

A Quick Tour

Across the top of the window are the usual menu system and a toolbar with one-click buttons. We’ll skip the details of those icons – it’s a “quick tour”.

Along the bottom we have some interesting information. At the left, Monarch tells us about the currently selected field: its name, the field type, and the name and type of template from which the field was derived. If a filter has been activated then that information is displayed close to the right side of the window. Finally Monarch tells us how many records are currently being displayed. Again, this is the number currently displayed, not the count of all of the possible records.

Assuming that the Table contains some data, or records (one or more fields displayed in the same row), the first thing that we should know is how to navigate in order to review what’s been extracted.

The Table looks just like an Excel spreadsheet, and in many ways it can be navigated in the same manner. Of course there are the horizontal and vertical scroll bars that we can click on and drag around. If you have a wheel mouse, you can scroll up and down, but not side to side (at least not with my mouse). But those approaches are too slow for us, budding power users that we are.

Pressing Ctrl-left arrow and Ctrl-right arrow will scoot us to the far left and far right fields in the Table. Ctrl-down arrow will always take us to the first field in the last row, but oddly pressing Ctrl-up arrow will move to the first row, highlighting the current field.

Ctrl-Home moves us to the first field in the first row, and Ctrl-End once again moves to the first field in the last row.

Of course, there’s also the simple navigation using the arrow keys and the Page Up and Page Down keys.

Tasty Menus

Right-clicking on the row numbers presents a pop-up menu with several options. Right-clicking on a field presents the same menu items with a couple of additional ones as well.

You’ll notice that the choices under the regular Edit menu are different than when the Report window is active, and that we now have a Data menu which mirrors the grouping of some of the toolbar buttons.

I find it quick and easy to see if I’ve accidentally truncated some fields by defining them with too small a setting for their display width. Just right-click the blank button immediately row number one, and select Autosize Column Widths.

If you’re questioning whether your templates are working as you thought that they would, it’s often useful to find the same record in the report as is currently selected in the Table. To have Monarch point it out for you, press Ctrl-F5, or select Display Source of Record in the pop-up menu.

When you need to locate a particular value in the Table, press Ctrl-f (Find is also available in the pop-up menu) to find the value. Monarch Find dialog is really quite powerful, as you can build formulas to find records that match criteria for multiple fields at once, effectively like “show me the sales made on Wednesdays wherein we sold 15 units”. Of course we can find simple text and numbers easily as well.

The last item of note exclusively relating to menus is on the Window menu, and it’s the Show Field Contents item. This opens an area in the toolbar space which is most useful for long field values, like memo fields, or multi-line fields.

Make It Right

If the sequence in which the fields appear doesn’t fit what you have in mind, it’s easy to change the layout, and we have a couple of ways to do that.

First, we can click on the field name at the top to select the column, and then click and drag it to the left or right to move it. The mouse pointer changes slightly to tell you that you’re about to move your field, and as you move the mouse, Monarch displays a thin red line to indicate where the field will go when you release the mouse button.

Using the pop-up menu, we can also choose to not display, or hide, a field. The field will still exist, and will calculate values if necessary, but it won’t be displayed on the screen, nor will it be included in exports.

When a field has been hidden, the pop-up menu will list a Display item that you can use to unhide a field.

But if we have a lot of field moving, and perhaps some hiding and/or unhiding, to do then the most efficient manner Monarch offers for that work is the Field List which is also available on the pop-up menu.

Re-sequencing the fields is done just like it is in the Table, only the controls have switched orientation. Click and release on the blank buttons on the left, then click and drag up or down to move the field.

If you do move the fields around with the Field List, and you’re happy with the new presentation and want to keep it, it’s imperative that you click the Set Display Order button on the bottom left of the dialog box. If you don’t and you just OK the dialog to close it, your changes will not be implemented. The more changes that you’ve done, the more frustrated that you’ll be. Don’t say that I didn’t tell you.

The Field List dialog box contains many columns and you can use the horizontal scroll bar to see all of them. I believe that they’re pretty self explanatory, so I won’t delve into them all. Notice that you can click, pause and click, or double-click on many of the values if you wish to change them.

Using the Show Template Properties and Show Table Properties we can examine much about our fields, and if we need to produce printed documentation about our fields, a Print button is available.

Here’s a little challenge if you’re up to it: print the field documentation to a PDF file using free PDF printer software (I like CutePDF Writer, and so does Monarch), and then Monarch the PDF, producing an Access database that you could use to document all of your models. How could you further enhance this proposed database?

Introduced in v10 is a Table Verify feature that is accessible via the Field List dialog. Please see the online help for a somewhat lengthy description of this very robust feature. I do find it a bit odd that such a useful tool is only accessible within the Field List dialog – tucked away somewhat – and not proudly on display in the toolbar.

Save Your Eyes

Remember to take your health into consideration when working with screens full of data, and use the font name and size controls in the toolbar to avoid straining your eyes.

Your Task Today

For today’s activity, re-read this post while you have a Table full of data available, and try it all out. Slow down a little and really take note of the different menus available, and get comfortable with navigating around the Table quickly.

Spend 10 to 15 minutes having a good look at the nooks and crannies in the various menus and dialog boxes. What happens when you click here or there, or when you right-click or double-click?

This is not a particularly challenging activity; it’s more about making the actions automatic, so that you won’t even have to think about what you need to do, you just do it.

The Table is Set

We can define up to 255 fields within the Table, so in order to make sense of it all, it’s important to be in control of it instead of the Table being in control of you. Stop working for your data, and make your data work for you. Master the Table interface and you’ll be set to excel with Monarch.

—

Continue your commitment to Become a Better Monarch Modeler with Part 10 of the series, or review Part 8.