0

Won't allow me to filter NL by certain fields?

Megan Paterson

February 08, 2023 10:23

None

Follow

I am trying to use an NL to get rows from a table in my microsoft dynamics NAV db, but on certain fields it wont allow me to filter by it? I have checked in sql and all works normally there and also inside the NAV system, so i'm thinking something with JET is conflicting. I am trying to filter a date created field by a cell that just says =TODAY() -1 - when ran, nothing happens, but if i put * in that same cell it works fine. I have tried filtering the same way with other day fields and all works fine so its not the formula thats causing it.The

The first image above you can see an * in cell K2, this works and it shows the first row (in design mode)

But if i change that to say 06/02/2023 00:00:00, I get #VALUE! (see below)

This also does the same if i try and filter by the last column which is just a text field, but I can filter by the Pallet ID no. which is the same format as source ID. 

Any help would be appreciated as its doing my head in. 

• Facebook
• Twitter
• LinkedIn

16 comments

• 0
  
  Barry Thompson February 08, 2023 10:49

  The second example has an incorrect date?

  

• 0
  
  Megan Paterson February 08, 2023 10:53

  Still does not work when correct date inserted

• 1
  
  Barry Thompson February 08, 2023 10:58

  can I see the full formula you are using?

   

• 0
  
  Megan Paterson February 08, 2023 10:59

  The NL formula?: =@NL("rows","Posted Pallet Tracking Line",,"Location Code",Options!$C$2,"Document No.","SSH*","Date Created",$K$2) and the NF to pull date created: =@NF($B$4,"Date Created")

• 0
  
  Barry Thompson February 08, 2023 11:48

  What happens if you replace the $K$2 with Today()-1

   

• 0
  
  Megan Paterson February 08, 2023 13:37

  I get the same error.

• 0
  
  Barry Thompson February 08, 2023 14:06

  Do you have any records where the date created is equal to Today()-1?

   

• 0
  
  Megan Paterson February 08, 2023 14:24

  Yes I checked this in the SQL server database and there is definitely records that match my criteria, as I managed to replicate my exact requirements in a sql query and it worked perfectly fine. The only inconvenience with this is I can schedule that which is why i would prefer to get JET working. 

• 0
  
  Barry Thompson February 08, 2023 14:32

  Not doubting you, but, you have two other criteria in that formula, When you run the report using * it will return all the records that match the first two. Can you find rows that match todays date?

• 0
  
  Megan Paterson February 08, 2023 14:53

  Yes there are 295 records with todays date, and over 2600 records with yesterdays date. Like I said, I ran the query in sql for the exact same criteria and there is plenty of data there that matches it.

• 0
  
  Megan Paterson February 08, 2023 14:57

  As you can see below, 295 rows with todays date, that also match the other criteria of location code and document no starting with SSH

  

  Blocking out data for privacy reasons

• 0
  
  Wilfred Crane February 08, 2023 15:20

  Could you try the date filter function, linking your NL statement to the datefilter?

  NP("DateFilter",Startdate,Enddate) ----   The StartDate and EndDate could both be linked to anohter cell containing Today()-1.

   

• 0
  
  Megan Paterson February 08, 2023 15:34

  That didn't work either:

  See above. 

• 0
  
  BryanAtTigunia February 08, 2023 16:30

  One thing I noticed in your initial post was the date you show in the report shows as Date Time.  Is this field a date/time field or just a date?  If it is a date/time field, when you just enter a date such as 02/08/2023, that really means 02/08/2023 0:00:00.  Unless you have records that posted at the stroke of midnight, you won't get any results.

  You could in theory, use a filter like 02/08/23..02/09/23 because that means from midnight to midnight and it is unlikely you have any records that post at the stroke of midnight.  If you want to be exact, you could also use something like >=02/08/23&<02/09/23

  If you have to use Today(), put that in a separate cell with an NP("Eval","=Today()") and use that to determine your dates.  Otherwise you are killing the performance of your report.

• 0
  
  Megan Paterson February 09, 2023 08:49

  This didn't work either, same results as above.

• 0
  
  BryanAtTigunia February 09, 2023 17:40

  At this point, I would recommend you work with a consultant as someone will need to dig into the report and the data to see what is going on live.