5 Replies Latest reply: Dec 9, 2016 4:34 AM by Grant Perkins RSS

    External Lookup question

    Angela Kephart

      How can I do an external lookup on a report fields and another external lookup?

        • Re: External Lookup question
          Olly Bond

          Hello Angela,

           

          In Monarch Classic, you define the external lookups from the Table window. Assuming you've got v12 or v13, you need to go to Table Design, and then go from there.

           

          Let's say you have some fields from the templates in your report. You can make a lookup that is based on these, and connects to external data to bring in some new fields. You can then make a second lookup that is based on the new fields, and connects to other external data, and brings in some more fields.

           

          Monarch should perform the lookups in the correct order each time you open the model.

           

          Best wishes,

           

          Olly

          • Re: External Lookup question
            Angela Kephart

            I am using Monarch 10.5.

             

            I am in the table area.  I've done one external lookup to an excel spreadsheet that adds one field.  I am attempting to do an external lookup to another excel spreadsheet matching 3 fields from the report and the field from the external lookup.

             

            The Error message is:

            The Table Window columns chosen for linking do not all come from the same source, thereby producing an invalid lookup definition.  If you need more than on linking column, choose Table Window columns that all come from the main report or that all come from one specific lookup.

             

            If I can't accomplish what I am trying to do, how else do I do this?

              • Re: External Lookup question
                Olly Bond

                Hello Angela,

                 

                That's an odd one - I never encountered that before. But this should help in 10.5:

                 

                After making the first lookup, export the table to an Access database (mdb).

                 

                Then open the mdb in a new Monarch project, and define the external lookup to the second file.

                 

                You can then easily run the two projects one after another with a batch file.

                 

                Best wishes,

                 

                Olly

                • Re: External Lookup question
                  Grant Perkins

                  Hi Angela,

                   

                  As a further input for Olly's suggestion ....

                   

                  I recall creating a "One Model" solution for a similar dependency of 2 lookups where the input from one lookup was required for the second look up to work.

                   

                  In that case the lack of the dependent lookup did not stop the first lookup populating the table successfully. That meant the a refresh of the table completed the look up from the second source.

                   

                  Does that make sense as described?

                   

                  I'll try to remember some more about it to see if I can find the development model. It was prepared as a suggestion for someone else some years ago (probably back in V9 or V10 times) so it's not entirely fresh in my recall "database"! It may have involved the first pass creating an output file that filled in the missing lookup connection.

                   

                   

                  Grant