Trouble with External Look Ups

[JLain]

I am attempting to bring in collection notes populated on excel spreadsheets into an ATB that I am working with. I am importing four similar spreadsheets in which collectors have put collection notes at the invoice level on the spreadsheet. The notes are in two columns on each spread entitled "short notes" and "long notes". Each of the 4 collector spreadsheets have these columns populated with their notes. The first external look up properly brought in the notes and opened two columns on the table view of "short notes001" and long notes001". Each of the remaining lookups opened new columns "short notes002" and "long notes002" and so on. However, no data was imported...only (null). I checked that the format types in the excel spreads are general as are they in Monarch.

Why isnt the subsequent external lookup's importing their respective data instead of the null value?

[Grant Perkins]

Hi JLain,

If you are seeing (null) in the fields the chances are that Monarch has the fields as numeric but the incoming data does not parse as numeric. Should they be numeric fields?

Are you predefining the fields or is Monarch creating them on the fly somehow? In other words are the field naming conventions (short notes001, short notes002, etc.) your naming convention or are they derived elsewhere?


Grant

[JLain]

Grant,
Thanks for responding. I thought of that too. On the spreadsheet they are formatted as general. In Monarch the fields are grayed out as "character". This should work in that the imported fields from excel are text.

[OllyInMunich]

Hello JLain,

It sounds like there might be an issue with the key fields being used to join the four tables. Is there a unique reference number common across the four tables?

Best wishes,

Olly

[JLain]

Yes, invoice number

[Grant Perkins]

Is it always the same lookup that fails?

If they are not numeric fields than the NULL issue is an odd one UNLESS it is the result of something else owing to the failed lookup.

Is there any possibility that the field lengths or alignment don't match? Might you have something like leading spaces or trailing spaces that are being included/excluded in the link key? Of maybe some other hidden character, perhaps formatting related, that is in the way?

Long shots once again but the hope is that they prompt some ideas rather than deliver a solution - though that would be good too!

HTH.


Grant

[elginreigner]

I would agree with Grant Perkins. I have run into this many times, it usually ends up being leading/trailing spaces.

I don't see it being data type issues as you would not be able to do the lookup.

[JLain]

Thanks for the input. I deleted the external look up files and put them in another folder. Now the lookups work bring in both the short notes and long notes. Not sure what changed if anything but it works now...we'll see on Monday when I run the project again.

Now I have discovered yet a new problem. I have summaries for each collector that are part of the project export. I bring in the short and long notes as items into the summaries. However, the short notes (001,002...) are available for each collector, however, the long notes field aside from 001 (002,003....)are not fields populating in the fields window to bring in as items like the short notes above? any ideas on this one?

[OllyInMunich]

Hello JLain,

Character and date fields can't be used in summaries as measures, but could be used as items.

I've not checked in a while whether you can use Memo fields in summaries - it would sound as if they can't (assuming your long notes are Memo fields).

You could try using Left(Textline(Longnote001;1);254) to create a calculated field that shows the first 254 characters of the first line of a long note.

Best wishes,

Olly

[JLain]

Thanks for the response. It continues to give me an invalid item error message. Thinking outloud though...this is going to be an after the fact fix once I get your formula to work as a calculated field...not that many characters are being imported by the external lookup?

[JLain]

I found a fields list on the tool bar. When I changed the "type"which I assume is from the source document to a memo it imported the entire note. Granted all of the source documents are excel 2003-07 yet one of the long notes I am unable to change to memo...it defaults to character.

any diagnosis to this probem?

[Grant Perkins]

The field lost is a gouping of all fields for mass change/review/re-ordering the table columns, etc. You can make the same changes via individual field edits and other activities but it is often very useful to see everything in a table and be able to manipulate it.

How many Memo fields have you got? Memo field default to 254 chars wide. A record has a limit of 4000 characters for the sum of field 'data width'. (Less - 2000 - for earlier versions of Monarch - I can't recall where the change point was off the top of my head. V7 or V8 as I recall.) Memo fields as defined will obey this constraint but allow more 'rows' (conceptually) to allow them to store 64k (ish) characters. I'm just wondering if the block you are seeing is specifically for that field for some reason or because there is no spare capacity in the record. Something to consider - but it could be well off target as a suggestion.

Are you seeing on messages on screen - there is usually something offered.


Grant