0

Filter based on word in the text

Jet Reports Historic Posts

August 15, 2013 18:19

None

Follow

Hi,

How to apply a filter that would only consider those records that contain a word or part of the word.

Let's say I have a table with ID and Name, I will write NL,rows function and I want to apply a filter that will return only those names that contain a certain word.

Thanks

• Facebook
• Twitter
• LinkedIn

5 comments

• 
  Jet Reports Historic Posts August 15, 2013 18:44 Official comment

  Hi,
  
  You just want to use the * character before and after the word which should match any value which contains the word like this:
  
  =NL("Rows","Table",,"FilterField","*word*")
  Does that work?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 16, 2013 02:00

  thanks, it did work

• 0
  
  Jet Reports Historic Posts August 19, 2013 13:43

  Similar question, how to return only those names that don't have a certain word/letters in them?

• 0
  
  Jet Reports Historic Posts August 19, 2013 14:40

  This depends on the database. It's a lot harder to do in Microsoft NAV if you're using a NAV native server because the filter "<>*Word*" doesn't work in NAV native. However, this filter DOES work in some universal databases like SQL Server (and by extension in NAV if you are using a SQL Server backend or in GP). Does that make sense? Hopefully you are using one of the databases where this filter works. If not, then you have to use NP(Difference) something like this:
  
  =NL("Rows",NP("Difference",NL("Filter","Table","Field"),NL("Filter","Table","Field","FilterField","*Word*")))
  Does that help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 19, 2013 16:08

  The filter with <> did work, but thank for the option with difference, I will try it too.