0

Help with Filters

Yvonne Jury

January 30, 2018 20:30

Answered

Follow

 Hello,

In the formula below I'm selecting work orders where the document no. either has a current batch owner equal to a cell value, OR the new batch owner is equal to a cell value.  It works fine, but I need to refine it further.  Each document no. has multiple line nos. where the line nos. can have different owners.  I don't want to include any line nos. from a document no. where the new batch owner is not equal to the cell value or the current batch owner is not equal to the cell value.

=nl("Rows","Posted work order line",,"document no.",nl("Filter","posted work order line","document no.","current batch owner",$C$2),"document no.",nl("Filter","posted work order line","document no.","new batch owner",$C$2))

So if the above formula returns the results below, and I only want rows where the owner is AGVAP, I only want to return Line No. 30000.

Document No.	Line No.	Current Batch   Owner	New Batch Owner
WO-1800070	10000	SVI001	SVI001
WO-1800070	20000	SVI001	SVI001
WO-1800070	30000	AGVAP	AGVAP
WO-1800070	40000	DAO003	DAO003
WO-1800070	50000	SVI001	SVI001

How can I combine the Document No. AND the Line No. into the filter?

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Harry Lewis January 31, 2018 21:48

  Hi Yvonne -

  I think I understand what you are after.  Let me create an example using a standard NAV table - Cust. Ledger Entry

  I've placed two codes in cells C3 and C4:

  

  and I want to list all records where the EITHER:

  • - the Customer Group Code equals the value in cell C3... OR
  • - the Customer Posting Group matches the value in cell C4

  I need to put an Excel OR function as a FilterField inside my NL("Rows") function and I need to put in the word "@TRUE" in the matching Filter.

  I'll need to be careful with my quote marks, but it should look like this:

  =NL("Rows","Cust. Ledger Entry",,"=OR(NF(,""Customer Group Code"")="""&$C$3&""",NF(,""Customer Posting Group"")="""&$C$4&""")","@TRUE")

  The Jfx would look like this:

  

  I hope that helps.

   

  Edited by Harry Lewis January 31, 2018 21:49
• 0
  
  Yvonne Jury January 31, 2018 23:12

  Yes!  Thank you, Harry!