11 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Automating External Lookup with VB

    Dusty _

      All,

       

      I am using VBscript to automate most of my Monarch work.

       

      I have one job that does an external lookup to an excel file.

       

      The name of the excel file changes everyday with the date (eg. Download20071018.xls).

       

      Is there anyway to automate external lookup file names through VBscript???

        • Automating External Lookup with VB
          Data Kruncher

          Hi Dusty, and welcome to the forum.

           

          Would an approach similar to  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000568;p=1#000001"]this solution[/url] work in your case?

          • Automating External Lookup with VB
            Nick Osdale-Popa

            Another solution is to have the script just rename your download file (Download20071018.xls) to a fixed name and have your external lookup use that instead.

            • Automating External Lookup with VB
              Dusty _

              Yes this does work for me....   I thought about this method but I was hoping there would be an easier way.

               

              Thanks for letting me know!!!   Much appreciate!!!

               

              Dusty

                • Automating External Lookup with VB
                  rlandeo _

                  Does anyone know if there is a command line parameter to do an external lookup?  I have a set of records in MS Access that I would like as the lookup from a table in Monarch Pro.

                    • Automating External Lookup with VB
                      RalphB _

                      Hi rlandeo and welcome to the forum.

                       

                      Sorry, the only way to do external lookups is to define them in a model.  External lookup settings are saved in each model. There is no command line parameter or OLE parameter to do it at this time.  Maybe in a future release.

                       

                      Depending on the version of Monarch Pro you are using, you can have up to 9 external lookup tables starting with Monarch Pro V8.  I can't remember how many you could have with version 7, but I think it was more than version 6 which was 1.

                       

                      HTH.

                        • Automating External Lookup with VB
                          rlandeo _

                          Hi rlandeo and welcome to the forum.

                           

                          Sorry, the only way to do external lookups is to define them in a model.  External lookup settings are saved in each model. There is no command line parameter or OLE parameter to do it at this time.  Maybe in a future release.

                           

                          Depending on the version of Monarch Pro you are using, you can have up to 9 external lookup tables starting with Monarch Pro V8.  I can't remember how many you could have with version 7, but I think it was more than version 6 which was 1.

                           

                          HTH.[/QUOTE]

                          Hi RalphB,

                           

                          I was working on this during the weekend and came up with this command-line.  It worked for me.  It opened up a report, applied a template to it, ran a look-up against an Access database, and exported from the table to an Excel spreadsheet.

                           

                          monarch /rpt:"c:\FOLDER\REPORT_NAME.dat" /mod:"c:\FOLDER\MODEL_NAME.xmod" /t /DATABASE_NAME.mdb:"KEY=KEY" /exp:"c:\FOLDER\FILENAME.FILETYPE"

                            • Automating External Lookup with VB
                              Grant Perkins

                              Raul,

                               

                              That's interesting.

                               

                              The Help documentation indicates that Monarch allows an existing lookup from the model to be varied from how it is set up. However the command syntax for that seems to be a little variant from what you have used.

                               

                              Did you have an internal lookup defined or are you just creating it directly in the command line?

                               

                               

                              Grant

                                • Automating External Lookup with VB
                                  rlandeo _

                                  Raul,

                                   

                                  That's interesting.

                                   

                                  The Help documentation indicates that Monarch allows an existing lookup from the model to be varied from how it is set up. However the command syntax for that seems to be a little variant from what you have used.

                                   

                                  Did you have an internal lookup defined or are you just creating it directly in the command line?

                                   

                                   

                                  Grant[/QUOTE]

                                  Hi Grant,

                                   

                                  Sorry, but I'm still a bit new to Monarch (I started using it about 3 weeks ago).  I'm not sure what you mean by internal lookup, unless you're talking about a calculated field.  In that case, no, I am not using a calculated field.

                                   

                                  I have a file of account details and another file of account name information.  I wanted the account name information to be tied in to the proper account details.  I created an Access DB from Monarch with the account name info and then ran an external lookup from the account details table in Monarch to give me those fields I needed.

                                    • Automating External Lookup with VB
                                      Grant Perkins

                                      Raul,

                                       

                                      My apologies, I see that what I wrote coould be confusing.

                                       

                                      I was thinking about the model containing (internal to the model rather than just part of the command script) an external lookup definition. As I read it you had not mentioned that so I wondered if you had found a unique trick to activate an external lookup where none was defined in the model.

                                       

                                      Using 'internal' was, as you pointed out, unclear and potentially confusing.

                                       

                                      3 weeks only, huh?  Seems like you are getting into it pretty well!

                                       

                                      If the external file (Access table) is temporary and a throw away after the process you have the option to make only a text based file (csv for example) and use that as your external lookup 'database'. It may be quicker perhaps and easier to process. If you have a prior process that alwasy creates the external lookup table/file you can control the name used as part of the model, especially if you roll the whole thing inot a couple of projects and then script using the project names and parameter controls in the batch file.

                                       

                                      Just a few thoughts that are no great distance from where you have already travelled to with Monarch.

                                       

                                      HTH.

                                       

                                       

                                      Grant

                                        • Automating External Lookup with VB
                                          rlandeo _

                                          Raul,

                                           

                                          My apologies, I see that what I wrote coould be confusing.

                                           

                                          I was thinking about the model containing (internal to the model rather than just part of the command script) an external lookup definition. As I read it you had not mentioned that so I wondered if you had found a unique trick to activate an external lookup where none was defined in the model.

                                           

                                          Using 'internal' was, as you pointed out, unclear and potentially confusing.

                                           

                                          3 weeks only, huh?  Seems like you are getting into it pretty well!

                                           

                                          If the external file (Access table) is temporary and a throw away after the process you have the option to make only a text based file (csv for example) and use that as your external lookup 'database'. It may be quicker perhaps and easier to process. If you have a prior process that alwasy creates the external lookup table/file you can control the name used as part of the model, especially if you roll the whole thing inot a couple of projects and then script using the project names and parameter controls in the batch file.

                                           

                                          Just a few thoughts that are no great distance from where you have already travelled to with Monarch.

                                           

                                          HTH.

                                           

                                           

                                          Grant[/QUOTE]

                                          Ok, so if I understand you correctly, and I'm almost afraid to say this, but are you saying that models store external lookup definitions?  I thought they only stored the template.  In that case, then yes, my model contains that lookup.  I thought that because I was specifying the lookup keys in the batch file it wasn't being defined by the model.

                                           

                                          I'll work on using a CSV file and see how that works.  Thanks for the advice.

                                            • Automating External Lookup with VB
                                              Grant Perkins

                                              Hi Raul,

                                               

                                              Just for clarification for anyone finding this thread at some future point there are 2 types of lookup in Monarch.

                                               

                                              Internal (Standard and Pro versions)

                                               

                                              External (Pro version only)

                                               

                                              Internal lookups[/B] are tables defined within the model and held there alone. Any changes to the content must be actioned through Monarch by editing the tables in the model. Internal lookups are best used where the data rarely of ever changes.

                                               

                                              External lookups[/B] always point to external data sources. Only the reference to the external source is saved in the model, not the data it contains.

                                               

                                              External lookups or most often used when the external data changes regularly or always, perhaps day to day for example. If the lookup table is large it can often be easier to make it an external lookup even if it rarely changes since there is likely less effort to create it. If the lookup pulls in more than one field an external lookup is also necessary if multiple internal lookup tables are to be avoided.

                                               

                                              Various types of files and databases can be used to store the data to be looked up. Any external lookups defined and named within the model will always be active provided that the file name originally set and the path to it are valid. If they are not valid the model will still run but the lookup will fail and that may create other errors - in calculated fields that rely on the values from the lookup for example.

                                               

                                              Older version of Monarch Pro allowed just one external lookup definition. More recently that was increased to allow up to 9 external lookups per model.

                                               

                                              One interesting aspect of multiple lookups[/B] is that the link to a lookup is from any field (or at least any realistic field that obeys the rules of offering a unique enough key to make the lookup worth doing) in the table. So if creating a second lookup in a Monarch model the connection can be made to a field created as part of the first lookup.

                                               

                                              When using command lines in batch files (as you are doing in the examples) you can override the settings held in the model file for each  lookup defined by reference to its name. The command line may specify a different reference file (/datasource) and/or table within the datasource (/table) from the definition in the model.

                                               

                                              The Help files offer guidance for these commands. As a non-programmer and a very occasional user of batch files I find the help information an invaluable resource. But I imagine coders will have their own expectations of available commands and many approaches to get the reuslt they desire.

                                               

                                               

                                              HTH.

                                               

                                               

                                              Grant