0

Define or Customise Lookup Options (e.g. Y/N) [SOLVED]

Jet Reports Historic Posts

July 12, 2013 10:07

None

Follow

Hi All,

I've been looking around the forum and online for a way to define custom values to select from in the lookup of my Jet Report refresh options. I finally found out how to do it! After providing the Title, Value and Lookup columns for your Option rows, put the following in the Lookup column:

Example 1 (Y/N): =NL("Lookup",{"Y","N"},"Y/N")
Example 2 (Rainbow): =NL("Lookup",{"Red","Orange","Yellow","Green","Blue","Indigo","Violet"},"Rainbow")

Hope that helps someone else :)

Thanks,
SD

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts July 12, 2013 14:42

  Hi SD,
  
  You can also put the custom values in a range of cells and reference that range. For your rainbow example, if you put Red in F5, Orange, in F6, all the way through Violet in F11, then your formula could be:
  
  =NL("Lookup",F5:F11,"Rainbow")
  I hope that is helpful!
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts July 17, 2013 15:00

  Thanks Hughes,
  
  I wonder - is there a way to reference multiple Tables or a table and add custom values?
  
  For example, combine =NL("Lookup","Cost Centres","Code") and =NL("Lookup",{"PLC","nonPLC"},"Code")
  
  I have tried:
  =NL("Lookup",{"Cost Centres","PLC","nonPLC"},"Code") - results in "Cost Centres","PLC","nonPLC"
  =NL("Lookup","Cost Centres","Code")&NL("Lookup",{"PLC","nonPLC"},"Code") - results in "PLC" and "nonPLC"
  =NL("Lookup",NL("Lookup","Cost Centres","Code")&{"PLC","nonPLC"},"Code") - results in "LookupPLC" and "LookupnonPLC"
  
  Haven't tested yet but a potential work around is using NL("Rows","Cost Centres","Code")to list the cost centres and a dynamic range reference to include other options. However, the user has to run the report once/save in report mode to then get the rows values for refresh.
  
  For now, I am using =NL("Lookup","Cost Centres","Code") but if the user manually inputs one of the other options instead ("PLC" or "nonPLC") then the report will run as expected still. The user just has to know about it.
  
  Any more efficient ideas?
  
  Thanks,
  SD

• 1
  
  Jet Reports Historic Posts July 17, 2013 15:37

  Hi,
  
  Yes this is definitely possible using an NP(Union) like this:
  
  =NL("lookup",NP("Union",NL("Filter","Cost Centres","Code"),{"PLC","nonPLC"}),"Code")
  Does this work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts July 25, 2013 13:05

  Thanks, worked perfectly :)