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

[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

[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.

:( ??

[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.

:( ??

[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.

[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.Yep, that's the other way to do it!




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.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.




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. 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

[ September 26, 2003, 01:32 PM: Message edited by: Grant Perkins ]