5 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    External Lookup 10 Chars ?

    Gene _

      I've got an external lookup setup, however when the looked up value appears in Monarch, it cuts it at 10 characters.

       

      For example:

      Lookup Value 239 returns Gene Stair

      Whereas in the excel table I'm listed as Gene Stairs

       

      On the same token, I set the value to lookup to 2

      In the excel table, the lookup portion is abcdefghijklmno

      Monarch only returns abcdefghij

       

      Anyone know anything about this?

       

      Thanks,

      Gene

        • External Lookup 10 Chars ?
          Grant Perkins

          Gene,

           

          I think this is as simple as the default size for a new CHARACTER field created from a calculation (of the lookup type in this case) is 10 chars. (Numerics tend to be 18 chars.)

           

          Change the field parameters to a suitable value for both data width and display width and the problem should resolve itself. I have used lookups (internal and external) up to 100 chars - maybe more if I look back.

           

          Grant

           

          Originally posted by Gene:

          I've got an external lookup setup, however when the looked up value appears in Monarch, it cuts it at 10 characters.

           

          For example:

          Lookup Value 239 returns Gene Stair

          Whereas in the excel table I'm listed as Gene Stairs

           

          On the same token, I set the value to lookup to 2

          In the excel table, the lookup portion is abcdefghijklmno

          Monarch only returns abcdefghij

           

          Anyone know anything about this?

           

          Thanks,

          Gene /b[/quote]

          • External Lookup 10 Chars ?
            Gene _

            I'm lost.

            When I check the field properties, the only thing I can change is the display width.

            It's type is character, but that is greyed out.

            Format is general (the only choice)

             

            I can't seem to find where to change the default data length.

             

               ??

            • External Lookup 10 Chars ?
              Grant Perkins

              Gene,

               

              I see your problem I think. My original response was not incorrect BUT I had not allowed for the functionality of creating lookups working with Excel.

               

              If you created the original lookup with a limited data set the model generated will likely have been set to the max field width of data in that set. Probably 10 chars. It will not resize dynamically. I think that would probably be an undesirable feature.

               

              You need to get to the Field List function

               

              Edit>> Field List is one way

               

              and from there you can change the field size in the Table Properties.

               

              BTW I could only get a link to work by setting the linking fields to TEXT format. Numeric/Number gave me messages of incompatibility no matter what I tried. Some other very odd things occurred from time to time as well at the excel end it seems. Data entered in excel on row 1 but re-formatted a few times and then demoated to row 2 could not be read. Very odd I thought.

               

              ("Source and Table columns must be of the same type")

               

              Did you have this problem?

               

              I have 7.01 installed and Excel 2000 though I am not certain that Excel up to date on patches.

               

              Hope this helps.

               

              Grant

               

              Originally posted by Gene:

              I'm lost.

              When I check the field properties, the only thing I can change is the display width.

              It's type is character, but that is greyed out.

              Format is general (the only choice)

               

              I can't seem to find where to change the default data length.

               

                    ?? /b[/quote]

              • External Lookup 10 Chars ?
                Gene _

                I deleted the external lookup field, recreated it and now the data appears fine.  It may be possible when we first set the lookup that the longest returned value was only 10 characters.

                 

                I did run into the error "Source and Table columns must be of the same type", when my Monarch value was character and the lookup list in excel was set to general.  The current setup is numeric to number which worked fine, and using a numeric from monarch and haveing the excel list formatted as general worked fine as well.

                Trying character to general did not work, neither did character to text.

                 

                I have moved the data around in Excel with no problems on the lookup responses.  However, I am using a named range in excel as compared to selecting the whole worksheet.

                 

                I as well am using 7.01 with Excel 2000.  I do have all current patches installed for MS Office, I don't know if one of these may have solved the issues you are encountering.

                • External Lookup 10 Chars ?
                  Grant Perkins

                  Gene,

                   

                    Originally posted by Gene:

                  I deleted the external lookup field, recreated it and now the data appears fine.  It may be possible when we first set the lookup that the longest returned value was only 10 characters.[/b][/quote]Yep, that's the other way to do it!

                   

                    [b]

                   

                  I did run into the error "Source and Table columns must be of the same type", when my Monarch value was character and the lookup list in excel was set to general.  The current setup is numeric to number which worked fine, and using a numeric from monarch and haveing the excel list formatted as general worked fine as well.

                  Trying character to general did not work, neither did character to text.[/b][/quote]That's interesting as it seems to be exactly the opposite to what I am getting!

                   

                  Something at the back of my memory is nudging me but I can't recall what exactly.

                   

                   

                    [b]

                  I have moved the data around in Excel with no problems on the lookup responses.  However, I am using a named range in excel as compared to selecting the whole worksheet.

                   

                  I as well am using 7.01 with Excel 2000.  I do have all current patches installed for MS Office, I don't know if one of these may have solved the issues you are encountering. /b[/quote]I think I need to investigate that or maybe see what happens if I use excel '97 instead as a first (quicker) investigation.

                   

                  Thanks for the input.

                   

                  Grant

                   

                  [size="1"][ September 26, 2003, 01:32 PM: Message edited by: Grant Perkins ][/size]