0

Excel function within NL - Filter

Wilfred Crane

May 01, 2018 09:27

None

Follow

Hi,

 

I'm sure this is really simple, but having a major senior moment.

I'm trying to retrieve data from one table, but filtered using data from another table. Usually pretty simple using NL("Filter"....

However, I would like to filter on the first 6 characters of a field.

In SQL : Select Field From Table1 Where Left(Field,6) in (Select Field2 From Table2)

I've tried various methods of getting the LEFT(Field,6) bit to work but so far to no avail.eg. NL("Rows","Table1","Field","LEFT(Field,6)",NL("Filter","Table2","Field2")

Any help would be much appreciated.

 

Thanks,

Wilf

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Harry Lewis May 01, 2018 14:11

  Hello Wilf -

   

  Here is a simple example using LINK=

  

  He

  Here is the same example using NL(Filter)...

  

  [ the NL(Filter) could, of course, also be within the NL(Rows)... I simply tend to separate them for ease of reading ]

• 0
  
  Wilfred Crane May 01, 2018 14:57

  Thanks Harry,

   

  I may not have explained the problem clearly. It's not actually the filter part which is causing me a problem, it's persuading Jet to compare the left hand six characters of a field against the filter...

• 0
  
  Harry Lewis May 01, 2018 15:09

  So, something like this?

  NL("Rows","Table1","Field","=Left(NF(,""Field""),6)",NL("Filter","Table2","Field2"))

• 0
  
  Wilfred Crane May 02, 2018 08:35

  Thanks again, Harry. The formula above looks like the kind of thing I was expecting.

  However, I'm afraid there's still no joy. I wonder if it's due to our Jet version / Database ? (Jet Essentials 2015 Update 1 15.1.15308.0, Dynamics GP).

  Wilf

• 0
  
  Wilfred Crane May 02, 2018 08:48

  Forgot to mention, the error I get is :

  

• 0
  
  Harry Lewis May 02, 2018 15:07

  Hello Wilfred -

  Yes, it very much is due to the database.  The technique of using an embedded NF() function does not work with Dynamics GP.

  What I would do is to conditionally hide those rows that I do not want to see.  For example, let's suppose I only want to see customer records where the customer is *not* in a particular set of cities.  To keep the example somewhat relevant, I'll look at the first 6 characters of the City field.

  

  My NL(Filter) function in C2 contains the list of values I want to compare against.  The NP(Join) in cell C3 puts that list in a format that I can use with the Excel FIND() function in cell B5.

  The function in B5 compares the first 6 characters of the City field (displayed in cell G5) against the list in C3.  If any match is found, the text HIDE returned.  

  When the report is run, any row with HIDE in column B will be hidden.

  Edited by Harry Lewis May 02, 2018 15:07
• 0
  
  Wilfred Crane May 03, 2018 06:54

  Hi Harry,

   

  Thanks for all of your help.

  The report I was trying to modify already utilises the conditional 'Hide' statement and I was trying to speed up / tidy up the report by not bringing back data that wasn't needed.

  It was worth an ask!

  Thanks again for all your help,

   

  Wilf