Multiple DateFilter Using NP

Hello,

I am trying to make a detail report pulling information from our data warehouse. I want to pull all active sales prices. To do this, my filter needs to look at our field "Ending Date" for a value that is in the future or not populated. I discovered, in our data warehouse if the Ending Date is not populated in NAV, Jet returns 01/01/1753. Therefore, I want my filter to be 01/01/1753 or greater than today. I created an NP(DateFilter) function for both individually that work successfully. I attempted a NP(Union) and receive #VALUE.

My Check Error results are:

The data provider returned the following error:

Conversion failed when converting date and/or time from character string.

I have attempted to nest the NP function in my NL function and tried to create my NP functions outside of my NL function. Both return the same error.

=@NL("Rows","Sales Price",,"DataSource=","JetDWProd","Sales Code",$K$2,"Ending Date",NP("Union",$K$3,$K$4))

Cell K3: =@NP("DateFilter",,"01/01/1753")

Cell K4: =@NP("DateFilter",TODAY())

I am new to Jet so I may be using the wrong functions. Any shared information or recommendations will be welcome.

Thank you,

Kristina Frank

Bryan Robinson May 06, 2020 12:28

DateFIlter will return the dates in a format used for filtering. It does not return a set of dates. For example, your K3 returns a single filter of "..01/01/1753" and K4 is returning "05/06/20.."

Additionally, using the TODAY() function will make your report run very slow as Excel is re-evaluate the function every time a cell is modified or created. Any volitile functions such as TODAY() or NOW(), should be in a different cell outside of the function and wrapped in an NP("Eval")

To get your date filter you need:

Put =NP("eval","=NP(""DateFilter"",TODAY())") in a cell in the top left corner of the report, typically with your other filters.
The filter for Ending Date should just be "01/01/1753|<<CELL REFERENCE>>" where CELL REFERENCE references the cell created in Step 1.

As an example, this will create the filter of 01/01/1753|05/06/20.. (either that specific date or is today or after)

Multiple DateFilter Using NP

1 comment