0

Filters on multiple fields

Jet Reports Historic Posts

August 29, 2014 09:06

None

Follow

Hi All

I'm a beginner to Jet and am trying to create a report based on a change log table in NAV. In Excel I'll enter a value and this value could be in one of 3 different fields in the table. How do you do an "OR" so that it filters the results based on this.

Thanks

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts August 29, 2014 16:48

  Hi PB
  
  I'm not sure I fully get what you are asking because you are saying you want an OR but you also will enter a value in Excel. Will this be hard coded to always these three values?
  
  If for example, I wanted to create a list of rows of customer numbers but I want to list all customers that are in the state of FL or GA or IL, it would look like this:
  
  =NL("Rows","customer","no.","state","FL|GA|IL")Using the pipe to specify OR in my filter values.
  
  Normally I would put my FL|GA|IL in a different cell and then reference that cell in the filter value though to look like this:
  
  =NL("Rows","customer","no.","state","$C$5")Does that help?

• 0
  
  Jet Reports Historic Posts September 01, 2014 12:58

  Hi
  
  What I'm getting at using your example was if there were three different fields in the "customer" table, some could contain the state and some could not. I want the filter to be able to look at all three fields and return any records which have the specified value in any of the three fields.
  
  Hope that makes sense.
  
  Thanks

• 0
  
  Jet Reports Historic Posts September 03, 2014 15:41

  I may be mis-reading what you're looking for as well, but I think I understand it to be soemthign like this:
  
  Looking at customer field, either the Name, the City or the State could have the value you are looking for (we'll say it's "AB")
  
  So you want your filter to work something like this:
  
  Cell B3 = *AB* (with the asterisks meaning that AB could be anywhere in the field)
  Cell B5 = NL("ROWS","CUSTOMER","NO.",OR("STATE",$B$3,"NAME",$B$3,"CITY",$B$3))
  
  Unfortunately - I'm guessing that if it's possible, this would take some serious time in the system, likely involving a union array.
  My solution is to handle it with three NL ROWS commands, replicating directly below one another
  
  Cell B3 = *AB* (with the asterisks meaning that AB could be anywhere in the field)
  Cell B5 = NL("ROWS","CUSTOMER","NO.","STATE",$B$3)
  Cell B6 = NL("ROWS","CUSTOMER","NO.","NAME",$B$3)
  Cell B7 = NL("ROWS","CUSTOMER","NO.","CITY",$B$3)
  
  Of course, if you wanted to, you could add spaces between these, to break up the groups visually so you would easily see where the common indicator falls (a solution I use when I'm error-checking our database).

• 0
  
  Jet Reports Historic Posts September 04, 2014 07:09

  Following the example above, there is also the possibility of joining array's with customer numbers.
  And that would look something like this:
  
  Cell B3 = *AB* (with the asterisks meaning that AB could be anywhere in the field)
  Cell B5 = NL("Filter","Customer","No.","State",$B$3)
  Cell B6 = NL("Filter","Customer","No.","Name",$B$3)
  Cell B7 = NL("Filter","Customer","No.","City",$B$3)
  
  The next cell can then contain the actual NL(Rows) function with a combination of the three fields above:
  
  Cell B8 = NL("Rows","Customer",,"No.",NP("Join",$B$5,$B$6,$B$7))
  
  I would prefer this, because it keeps the customers sorted all together, instead of 3 portions and it will filter duplicates automatically.
  Duplicates are customers that have a name AND a city that contains *AB*. A customer like that will be shown twice in the solution from Heather.
  
  HTH
  
  rmw