0

NL function with filter from another table

Rosita Chan

April 30, 2019 06:51

Answered

Follow

I am trying to build a NL function with filter from another table.

Primary table: Bank Account Ledger Entry
Secondary table: Bank Account Statement

Filters:
1) Open - False
2) Posting Date - ..28/2/2019 <from NP cell
3) Bank Account No. - <from Option page
4) Statement Date. - 28/2/2019.. <from NP function

"Statement date" is not include in "Bank Account Ledger Entry", I can only get the date by using the "statement no." on both Bank Account Ledger Entry and Bank Account Statement Table.

Below is the formula I set:
=NL("Rows","Bank Account Ledger Entry",,"Open","False","Posting Date",$C$7,"Bank Account No.",Options!$C$2,"Statement No.",NL("Filter","Bank Account Statement","Statement No.","Statement Date",NP("DateFilter",$H$8)))

With this formula, the report still give me all the data without the filter on "statement date". May I know what I did wrong?

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Harry Lewis April 30, 2019 13:44

  Hello Rosita -

  If you enter this function:

  =NL("Rows","Bank Account Statement","Statement No.","Statement Date",NP("DateFilter",$H$8))

  Does it return only the Statement No. values you want?

• 0
  
  Rosita Chan May 02, 2019 03:26

  Hello Harry, 

  =NL("Rows","Bank Account Statement","Statement No.","Statement Date",NP("DateFilter",$H$8))

  The above function dose not return what I want.

  However, I fixed it by adding one more filter - Bank Account No. the function as below:

  =NL("Rows","Bank Account Statement","Statement No.","Statement Date",NP("DateFilter",$H$8),"Bank Account No.",Options!$C$2)

  Thanks a lot!
  Now I get the result I want.