Hello,
Is it possible to filter a nl(row) function based on a date difference? I'm trying to flow sales order # into a group report based on the sales order headers' order date and the sales order lines' planned shipment date where the date difference is less than 3.
Thanks.
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi,
If you are using NAV, then this is possible via a calculated filter. I think it would look something like this:=NL("Rows","Sales Line",,"=NF(,""Planned Shipment Date"")-NL(,""Sales Header"",""Order Date"",""No."",NF(,""Document No.""),""Document Type"",NF(,""Document Type""))","NUMBER&<3")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hello,
That worked on my NAV database. I'm trying to help someone on a modified system with Jet. We got an error, but I might have to change some field names in the query. Does Jet automatically know what key to look for in the nested function NF(,""Planned Shipment Date"") ?Also, What is NUMBER? Is that assigning a variable to the return? I did not see anything in the knowledgebase about calculated filters.
Thanks,
N. -
Jet Reports Historic Posts Hi,
I'm using a universal connector. I need to count the number of records using the following query:=NL("CountUnique","RR_RNR_HEADERS","RR_RNR_NUMBER","COMPANY_CODE",$C$4,"RR_DATE",$C$3,"CS_REGION_CODE",$D17,"RR_SECTION_UNIT",K$16,"RR_WORK_DONE","Y","RR_WORK_DONE_APPROVED_DATE",$C$3,"RR_WD_PENDING_APPR_LEVEL","999","Schema=","PLUS_PROD")
But I need to add in a filter whereby difference between RR_ACTUAL_COMPLETE_DATE and RR_TARGET_COMPLETE_DATE is <=0. Both fields can be retrieved form the same RR_RNR_HEADERS table.
Is this possible? FYI, I can't use NF here because there'll no primary key in this table after I removed the "field", which is RR_RNR_NUMBER.
Please assist. Thanks. -
Jet Reports Historic Posts Hi,
njpd: It would make sense that you might have to change some field names, since most people work off of customized versions of NAV. The key for the NF in a calculated filter like this is always the primary key for the current record from the main function (from the Sales Line in this case). The NUMBER& part of the filter just tells Jet that the results of your calculated filter field are going to be a number (not a date or string), so Jet should treat it like a number for filtering purposes. Calculated filters are a very complex topic that is hard for most people to grasp with or without a KB article. They are useful in some cases, but generally not encouraged since they are somewhat slow and very hard to understand for most people.
mavis: Calculated filters can't be used in a universal data source. You can either use conditional hide and just hide the rows you don't want to see or else you can write a SQL query and use the SQL= functionality of Jet to do the query. See the documentation for SQL= here: http://help.jetreports.com/14.0/Essentials/index.html?SQL=.html
Regards,
Hughes
Please sign in to leave a comment.