0

Not all values visible with lookup

Jet Reports Historic Posts

July 08, 2011 09:46

None

Follow

Dear "jetreportscollegaes",

My problem I can't see all values when running the report when using lookupfunctions within the options.

=NL("Lookup";"Tablename";"Fieldname")

It only shows the first 16 found values, I also tried on articlenumber and then it shows more records but also not all of them.

Any suggestions?

Thx, regards,

Jørgen Hulscher
Netherlands

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts July 08, 2011 16:50

  Hi Jørgen,
  
  The reason you're not seeing all of the values is because of the lookup limits in the Application Settings. See attached. The "Lookup sample value limit" is the maximum number of values that will be displayed, and the "Lookup record scan limit" is the maximum number of records that will be scanned in order to retrieve those values. These are in place for performance reasons.
  
  You can always increase these values, but typically the better solution is to go to the master table to perform the lookup. For instance, if you are looking up customer numbers, it is better to use the Customer table than the Cust. Ledger Entry. This is because the table is smaller and contains one unique customer number per record (100 records = 100 customer numbers).
  
  Does this make sense? Please let me know if you have questions.
  
  Regards,

  • Settings.PNG
• 0
  
  Jet Reports Historic Posts July 08, 2011 17:23

  Thx, after my holidays i'll check it right away…
  
  Regards
  
  Jørgen

• 0
  
  Jet Reports Historic Posts July 29, 2011 10:19

  Another problem could be that - what I think - only unique values are listed and blank fields are not listed.
  
  One example:
  
  No. Name 100 pencil 101 pencil 200 ruler 201 300 pen
  =NL("Lookup";"Tablename";"No.") would list all 5 entries
  
  =NL("Lookup";"Tablename";"Name") would list 3 entries: pencil, ruler, pen
  
  But you could combine
  =NL("Lookup";"Tablename";{"No.";"Name"}) would list 5 entries each with No. and Name

• 0
  
  Jet Reports Historic Posts July 29, 2011 19:43

  Hi Cat,
  
  You are correct that only unique values are listed, but you are incorrect about blanks. Blanks will be listed if they are found (see Sam's post above about lookup limits and why they might not be found). The NL(Lookup) function like all NL functions will only list unique values or combinations of values.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 02, 2011 09:12

  Hi Hughes,
  
  thanks for your information - I must have overseen the blank, sorry. :oops:

• 0
  
  Jet Reports Historic Posts August 02, 2011 14:56

  Haha, no worries! It's easy to miss the blanks because they get sorted to the top of the list usually which means you don't necessary notice them unless there's other stuff in the same row.
  
  Regards,
  Hughes