If you need to maintain a table of codes and values - such as your cost centre database - then Monarch does let you do this within the model by using a type of calculated field: the lookup field.
If this would be useful in multiple models, then Monarch v9 and above let you import entities (such as lookup fields, filters, templates etc etc) between models. A common deployment of this is to have a central master model that contains all the useful things you've built, that you can call on to select the right tool for the model you need. You can select strong, weak or no linking - this affects how the active model checks for updates from the source model.
It sounds like it's time to scale this process up to a VBS script or better, an Excel VBA program.
Then you could have better formatted and detailed input prompts, and could pass the inputs along to the model via the SetRuntimeParameter method.
Using an Excel front end, you could import data from the cost center Access db, and use that as a validation source for user inputs.
But when it comes down to it, since you can't create complex filters dynamically, you must define specific filters in advance in Monarch.
That said, (as Olly said above) you could limit your overall efforts by employing a centralized strategy by creating a master Monarch model that defines the filters, both simple and compound, and then create strong links to those filters in your other models. See the object linking tutorial PDF file for more info.
Your needs sound like it could be an interesting and very useful mini application. My Excel class module for Monarch would help you in developing your solution, regardless of your experience in automating Monarch.
Olly and Kruncher,
Thank you for your replies.
At least I have established that one can’t create filters dynamically in Monarch.
I actually tried to remember where strong, weak and no linking were mentioned in the Monarch Learning Guide. Of course it wasn’t mentioned there at all. When Kruncher mentioned the object linking tutorial PDF, I started looking for it. I didn’t realize it was part of the launch menu in Monarch 10—I usually launch Monarch off the desktop icon. I have started reading it and it looks good. My biggest beef with Monarch documentation is that it should consolidated in one big master document. Materials about programming, functions, objects are scattered all over the place. I use the command lines a lot in batch scripts, but the documentation is buried in 2 pages within Monarch Help—this assumes you read all of Monarch Help. I only stumbled upon it accidentally. I sometimes think that there is a page of documentation out there that could make my life easier if I only read it.
Someone should collate all the best tips and tricks of Monarch off the user forums and publish it (just a thought—I know this is a lot of work).
Kruncher, you mention an Excel class module for Monarch would help me. I have no idea what this is about—can you direct me where I could find this “class module for Monarch” and how I could use it. I am sure others would benefit too.
For me, a very small sample application to illustrate what you are talking about would help immensely. Is there a book that you could recommend?
Thanks a lot,
I supply the [URL="http://********************/tips/monarch-programming-class"]Excel class module for Monarch[/URL] to those who elect to subscribe to my site via the free Feedburner service. The service sends you the new posts (which have been woefully non-existant of late , but that's a story in itself and I'm doing my best to rectify it) on the site direct to you via email.
I'll try to post a sample of coding with the class module here later tonight. The main advantage of this approach is that you don't need to know all of the various flags and such for the Monarch object parameters. Instead, you're prompted with clear, user-friendly (hopefully ) parameters. You don't need to know the difference between 0, 1 values for a parameter.
As I mention in my site post, my take on the Monarch Programmers Guide is that is was written for programmers by programmers. My class module is my attempt to bring the power that automating Monarch to the rest of us by hopefully making the task easier to do and understand.
The tool includes not just the program code, but also full documentation and many, many samples.
Again, I'll try to post a meaningful sample or two here later.
As mentioned above, here are a couple of samples from my custom Monarch Excel class file. Class modules are a mechanism that Excel offers that allows for the creation of objects and can make programming easier to manage.
This snippet would load a new report into Monarch and apply an existing model file.
bSuccess = .LoadReport("D:\Reports\SalesQ1.prn", mclAppendTypeNew)
If bSuccess Then
bSuccess = .ApplyModel("D:\Models\Sales.xmod")
Here's a sample of my ExportSummaryEnhanced method, which offers the named parameters (sDestinationFile, sTableName, iAppend):
bSuccess = .LoadProject("D:\Projects\Inventory.xprj")
If bSuccess Then
.ActiveSummary = "Supplier Purchases"
If .ActiveSummary <> "Supplier Purchases" Then
MsgBox "The Supplier Purchases summary does not exist in the model."
bSuccess = False
'you would normally choose only one of the following three file handling methods
'create a new table (sheet) in the output file
bSuccess = .ExportSummaryEnhanced("D:\Output Files\Supplier Purchases.xls", "Purchases", mclFileAppendTypeNewTable)
'add data to an existing table named Purchases
bSuccess = .ExportSummaryEnhanced("D:\Output Files\Supplier Purchases.xls", "Purchases", mclFileAppendTypeAddData)
'overwrite the existing ouput file if it already exists
bSuccess = .ExportSummaryEnhanced("D:\Output Files\Supplier Purchases.xls", "Purchases", mclFileAppendTypeOverwriteFile)
If bSuccess Then
MsgBox "Export complete!"
MsgBox "Export failed!"
The file includes 51 worksheets used to describe each of the available properties and methods available to control Monarch, and a FAQ to answer task related questions like "How do I open a report?", "How do I export the table?", "How do I run a specific project export?" and many more. Two sheets summarize the available properties and methods and describe the purposes for each - what it means to you.
The items that are for the use of Monarch Pro users only are clearly indicated with red icons.