Hello.
Try this:=NL("Rows";"Table";;"From Date";"=""<""&nf(;""To Date"")")
Date
Votes
6 comments
-
Jet Reports Historic Posts Hello,
I haven't found any threads that solved my problem so I guess it's time I submitted my first own question now:
I have a table with two Date Fields "FromDate" and "ToDate". I want to display only "valid" entries where FromDate<=ToDate (among other filters). I also want to count these with additional filters applied, thus I cannot just hide "invalid" rows.
What I tried so far has been:NL("Rows";"Table";;"FromDate";NP("DateFilter";;NF(;"ToDate")); […additional filters])
This method returns values but doesn't actually filter out invalid entries. If I try something like: "From Date";"<="&NF(;"ToDate") I get an invalid Filter error.
The rest of the filters work fine by the way.
Thanks in advance for any help! -
Jet Reports Historic Posts Hey,
thanks for your suggestion. Sadly I get an invalid Filter error again, stating that "<31.07.2005" is not a valid Filter for the field From Date, because "31.07.2005" is not a valid date. Which is strange because I am able to use that Filter as a hardcoded value. Additionally, the report takes about 7 Minutes to complete although I used "Limit=";"1" :-/
Any further ideas as to what I should be doing? -
Jet Reports Historic Posts For a date you need the ".."
You could use the NP datefilter function or just put the ".." instead of the "<" and it may work.
Hope this helps. -
Jet Reports Historic Posts The only way I can get the report to run is with the NP daterfilter function and in this case it won't filter any values.
I tried to simplify the case by creating a new file with just two Counts.
One Count without any filter and another one where I try to create a filter which only returns valid entries (where FromDate is smaller than or equal to ToDate).
So basically this SQL statement translated to Jet:
SELECT * FROM Table WHERE FromDate<=ToDate;
These are my attempts:=NL("Count";"Table";;"FromDate";".."&NF(;"ToDate"))returns "invalid Filter '..'" error=NL("Count";"Table";;"FromDate";"<="&NF(;"ToDate"))returns "invalid Filter '<='" error=NL("Count";"Table";;"FromDate";NP("DateFilter";;NF(;"ToDate")))returns the same number as the Count without any filters, presumably because NF(;"ToDate") returns a blank value?=NL("Count";"Table";;"FromDate";"=""..""&NF(;""ToDate"")")=NL("Count";"Table";;"FromDate";"=""<=""&NF(;""ToDate"")")=NL("Count";"Table";;"FromDate";"=NP(""DateFilter"";;NF(;""ToDate""))")These all return "'18.01.1999' is not a valid date" error (18.01.1999 is the FromDate of the first entry in Table)
Both Fields FromDate and ToDate have the type "Date" in the Jet Browser.
So I am at a bit of a loss here. I'm not even sure that the way I am using NF is the intended way to approach this, but a lot of the reports I will have to design will be dealing with that table, so I'd really like to figure out what I should be doing. -
Jet Reports Historic Posts Can you convert the dates to INTs to compare them?
=NL("Count";"Table";;"=INT(NF(;""From Date""))";"=""<""&INT(NF(;""To Date""))")
Is it possible that the fields are at Date type in the database? -
Jet Reports Historic Posts The comparison needs to be done on every record.
The only way to do that is using an Excel formula to pass the comparison as a whole to the database and filter on the outcome, which is true or false.=NL("Count";"Table";;"=NF(;""From Date"")<=NF(;""To Date"")";TRUE)Be aware that you will skip the use of any keys in NAV this way.
The Excel formula will be evaluated for every record, before returning any result.
Additional filters should limit the number of records to be evaluated, but still this is much slower then regular Jet formula's.
If this is to be used a lot, I would recommended adding the comparison as a seperate field to the table itself.
rmw
Please sign in to leave a comment.