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

    Problem with external lookup

    MonUserCJ _

      Hey all,

       

      I am having a problem creating an external lookup that I have encountered a number of times in the past, and it's frustrating me.

       

      The file I want to use has patient ID in one column and a doctor ID in the second column. They are both character fields, and I exported them from Monarch delimited by a tilde (~) and with double-quotes around the fields.

       

      This is to match with the patient IDs in another project. I go through and select the field to match on, the field to import, etc.

       

      Then I get the error:

       

      "An external lookup data source 'C:\path\mapfile.txt' is either missing or invalid. From the Table Menu choose Data/External Lookups... to select a new data source for the offending lookup."

       

      Does anyone know what could be causing this? The path I put in the message is generic, not the actual one. I view the map file and it looks fine. I would appreciate any insights as to what could cause this error.

       

      Thanks.

        • Problem with external lookup
          Olly Bond

          Hello

           

          I've a sneaky feeling that Monarch isn't seeing the "abc"~"def" format as a database file. If you're exporting from Monarch, then you have control of the data. If you try exporting in Access mdb format, do you still get the same error?

           

          Best wishes,

           

          Olly

            • Problem with external lookup
              MonUserCJ _

              Hey,

               

              Thanks for the advice. In the end, I was able to get it working using the method that you described. However, the software still behaved strangely in that it didn't work initially. I thought that the length of the path to the file (which was on a network drive) might be causing the issue, so I tried copying it to the root directory of my hard drive. Then it worked. I tried it in a deeper directory on my local hard drive, and it didn't work.

               

              I also had tried putting a delimited text file in the root directory of my hard drive and it didn't work, so exporting as the MDB definitely helped.

               

              In conclusion, I wonder if Monarch has an issue supporting external lookup files stored on network drives, or in directories with spaces. Anyway, thanks again for your help.

                • Problem with external lookup
                  Olly Bond

                  Hello

                   

                  Glad you got it working. Handling paths and filenames is something that Monarch would inherit from Windows, so I doubt that's the issue. But moving it to a local drive would avoid any possible timing or sharing issues that you had on the network drive.

                   

                  A side effect of long network paths is that the File() function returns a string, and this can exceed the 254 character limit of a normal text field in Monarch, so do check and use RSplit() or memo fields as appropriate if you need this data.

                   

                  Best wishes

                   

                  Olly