9 Replies Latest reply: May 15, 2014 10:06 AM by Olly Bond RSS

    External Lookup Table

    feldwickm _

      Hi

       

      I'm having a bit of trouble using an external lookup table. The table is a spreadsheet that is used in many of the models that we use but it seems that often when users try to use the models linking to the table it comes up with a message asking to Switch To or Retry. Is this caused by a limit on the amount of models that can link to a lookup table at once and is there any way around this problem? Any help would be appreciated. Many Thanks.

        • External Lookup Table
          Olly Bond

          Hello feldwickm,

           

          This sounds as if Monarch is trying to open the spreadsheet but finding that it is already open by another user. Can you open the spreadsheet OK in Excel or do you see the same error?

           

          You might be able to avoid this by using Access instead of Excel as the format to hold the shared data in? Alternatively, if the data consists of straightforward looukp values, you could define a calculated field of type "lookup", put this in a shared model file, and use Monarch's "linked objects" to bring this definition into the other models.

           

          What's most efficient for you will depend on how the data is maintained / generated, audit requirements, automation (are you using DataPump? batch files? calling Monarch from other programs?), as well as what version of Monarch is in use.

           

          HTH,

           

          Olly

          • External Lookup Table
            Grant Perkins

            Hello feldwickm and welcome to the forum.

             

            I donlt think there is a limit to how many models can link to the same lookup table but there may effectively be an Excel or system limit on how many can read the file at the same time, especially if the spreadsheet is open in update mode, in which case it may be locked for other users anyway.

             

            There is most likely an inbuilt timeout on the enquiry so that a slow response might result in a a message like the ones you mention. Likewise a lack of access to the file via the network, perhaps a slow response due to network traffic, could be involved.

             

            If you are using direct access to Excel I assume the lookup data is fairly dynamic and regularly changes. If not there may be some benefit in considering using an alternative file type - say a CSV text file - to hold the lookup data. That may depend on how many models you would need to change and update on people's workstations. And perhaps other considerations about operational matters.

             

            Others here are likely to be better able to provide input from everyday experience of their operations.

             

            HTH.

             

             

            Grant

             

            ETA: I see Olly has already posted excellent information whilst I was typing ....

              • External Lookup Table
                RalphB _

                Hi,

                 

                Both Olly and Grant have offered good solutions especially moving the data to an Access database.  If that isn't an option and you need to keep the file in Excel format, you can set the Excel file to Share the file.  If the file is set to Share, more than one person can access and work in the file at the same time.  We use that on some of our Excel files here and it works great.

                 

                There are different ways of setting a worksheet to Share depending on your version of Excel.  I am attaching a link to the Knowledge base explaining how.[url=http://support.microsoft.com/kb/269860] Sharing Excel Files /url

                  • External Lookup Table
                    feldwickm _

                    Thank you very much for your quick responses.

                    The lookup file is already shared and often it is unable to be opened in excel when we are experiencing difficulties, sometimes it opens in Excel after Monarch has been closed. It looks like I'll develop a solution in Access, probably linking the database to the spreadsheet.

                    Thanks

                      • External Lookup Table
                        feldwickm _

                        Hi again,

                        Is monarch able to use linked tables in databases as lookups? I've made a database that uses my spreadsheet as linked tables but Monarch doesn't seem to recognise the tables. Once again, many thanks for any help.

                          • External Lookup Table
                            Data Kruncher

                            This may well open a bunch up of new opportunities.

                             

                            Like you, as was reading through this thread the concept of linked tables crossed my mind too.

                             

                            But as you say, Monarch doesn't "see" them.

                             

                            But there is a solution, and it's deceptively easy to implement.

                             

                            In the Access database, create and save a query that selects all of the data (*) from the linked table.

                             

                            Now Monarch can not only see the query and use the records in the linked table properly, but it will also do so even if the Excel file is in use[/I]. You also don't have to jump through the workbook sharing hoops. :cool:

                             

                            Kruncher

                              • External Lookup Table
                                feldwickm _

                                Hi

                                The query solution sounds really good but Monarch can't see the query I've made. I'm using Monarch Pro v9.01 and Access 97, could it be to do with having an old version of Access? Or do I need to change a setting on Monarch? Any help would be appreciated again. Many Thanks.

                                  • External Lookup Table
                                    Data Kruncher

                                    Hmm. That could be correct about Access 97 being a problem. I did test it with Access 2003.

                                     

                                    I don't have Access 97 to test with. Can anybody else try it to confirm?

                                      • External Lookup Table
                                        Olly Bond

                                        Hello everyone,

                                         

                                        (Happy Thanksgiving for our American friends...)

                                         

                                        I got it to work in Access 2007 by defining the Query as a "Make Table" type.

                                         

                                        So there's a spreadsheet, then in Access you have a Linked Table, a Query that selects * from this table, and when you run the Query it creates a new Table that is visible to Monarch.

                                         

                                        I'm presuming that there are scripts / macros / options or other voodoo that can tell Access to run this query automatically.

                                         

                                        Best wishes,

                                         

                                        Olly