11 Replies Latest reply: May 15, 2014 10:05 AM by Grant Perkins RSS

    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?

        • Trouble with External Look Ups
          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

            • Trouble with External Look Ups
              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.

                • Trouble with External Look Ups
                  Olly Bond

                  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

                    • Trouble with External Look Ups
                      JLain _

                      Yes, invoice number

                        • Trouble with External Look Ups
                          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

                            • Trouble with External Look Ups
                              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.

                                • Trouble with External Look Ups
                                  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?

                                    • Trouble with External Look Ups
                                      Olly Bond

                                      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

                                        • Trouble with External Look Ups
                                          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?

                                            • Trouble with External Look Ups
                                              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?

                                                • Trouble with External Look Ups
                                                  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