0

Filter by <>NL("Filter",)

Jet Reports Historic Posts

April 15, 2013 10:32

None

Follow

Hi,

This is probably so simple, but I can't get it to work.

I have used an NL("Filter") function to produce a list of account numbers which fall into a certain criteria (say in cell D18)

I then need to pull entries from the Item Ledger Entry table for all customers EXCEPT those in the list above. If use D18 as the filter on the "Source No." field it works OK, but I can't find the correct syntax to use <>D18 as the filter.

Any help appreciated.

Regards,
Garry.

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts April 15, 2013 14:48 Official comment

  Hi Garry,
  
  Andy's solution does not work. You can't use <> and then reference and NL(Filter) function. First, I should say that the fastest and easiest way to do this if it's possible is to just change your filters so that they only include what you DO want rather than what you don't want. I realize that sometimes this isn't possible, but this is always a better choice if it's possible.
  
  If that isn't possible, then you can use NP(Difference) to get the difference between one list and another. So in your case, given that you're trying to get customer numbers, you need to find the difference between ALL customer numbers and the customer numbers you're returning in your function in D18. So the function might look something like this:
  
  =NL("Rows","Item Ledger Entry",,"Source No.",NP("Difference",NL("Filter","Customer","No."),$D$18))
  I should warn you that you CAN'T pull the NP(Difference) out into its own cell and reference it. You can reference the NL(Filter) functions, but you must embed the NP(Difference) in your other formula or else it won't work. Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts April 15, 2013 12:11

  Hi Garry,
  
  Try this one
  
  =nl("rows","Item Ledger Entry",[your field],"Source No.","<>"&D18)
  
  Andy

• 0
  
  Jet Reports Historic Posts April 15, 2013 15:20

  Hi Hughes,
  
  That works, thanks.
  
  I had tried something similar in a cell which I then referenced, but got an error that the filter was too long. However it seems to work fine when built into the function.
  
  Thanks for your help.
  Regards,
  Garry.

• 0
  
  Jet Reports Historic Posts April 15, 2013 15:44

  Hi Garry and Hughes,
  
  Ooops… you're right, that wouldn't work with NL(Filter) :oops:
  Sorry for posting erroneous solution…