I am trying to list all the Sold to Customer records in the Sales Credit Memo table for a given month, that do not have a corresponding Sold to customer record for the same month in the Sales invoice table.
Date
Votes
7 comments
-
Jet Reports Historic Posts Hi -
You could use a technique like this:=NL("Rows","Sales Cr.Memo Header","sell-to customer no.","=NL(""Count"",""Sales Invoice Header"",,""Sell-to customer no."",NF(,""Sell-to Customer No.""))","NUMBER&0")
If you want the entire record key (and not just the customer no.):=NL("Rows","Sales Cr.Memo Header",,"=NL(""Count"",""Sales Invoice Header"",,""Sell-to customer no."",NF(,""Sell-to Customer No.""))","NUMBER&0")Obviously, you would need to add a filter for your time frame [to both the main NL("Rows") and the NL("Count") function ]. -
Jet Reports Historic Posts If I follow your syntax, I get a #value error. This is what I am using.
=NL("Rows","Sales Cr.Memo Line","Sell-to Customer No.","Posting Date",$B$3,"Gen. Prod. Posting Group",$B$4,"Type","Item","=NL(""Count"",""Sales Invoice Line"",""Sell-to Customer No."",""Posting Date"",$B$3,""Gen. Prod. Posting Group"",$B$4",NF(,""Sell-to Customer No.""))","Number&0") -
Jet Reports Historic Posts Ah! Including cell references within an embedded function [in this case, the NL("Count") ] get's a little trickier.
Try this:=NL("Rows","Sales Cr.Memo Line","Sell-to Customer No.","Posting Date",$B$3,"Gen. Prod. Posting Group",$B$4,"Type","Item","=NL(""Count"",""Sales Invoice Line"",""Sell-to Customer No."",""Posting Date"","&$B$3&",""Gen. Prod. Posting Group"","&$B$4&",NF(,""Sell-to Customer No.""))","Number&0")
If you still get a #VALUE, what additional information is displayed when you select that cell and click the DEBUG button on the Jet ribbon? -
Jet Reports Historic Posts This is the debug error:
An exception of type Jet.Excel.ExcelEvaluationException, Jet.Excel was thrown.
Excel is unable to evaluate '=NL("Count","Sales Invoice Line","Sell-to Customer No.","Posting Date",120115..123115,"Gen. Prod. Posting Group",FCP,NF(,"Sell-to Customer No."))'. -
Jet Reports Historic Posts OK. I see how your dates are formatted.
Try this (slightly modified) function:=NL("Rows","Sales Cr.Memo Line","Sell-to Customer No.","Posting Date",$B$3,"Gen. Prod. Posting Group",$B$4,"Type","Item","=NL(""Count"",""Sales Invoice Line"",""Sell-to Customer No."",""Posting Date"","""&$B$3&""",""Gen. Prod. Posting Group"","""&$B$4&""",NF(,""Sell-to Customer No.""))","Number&0") -
Jet Reports Historic Posts I get the error "Empty Filter not allowed"
-
Jet Reports Historic Posts Oh! I hadn't noticed that you moved a few parameters around awhile back.
Let's try again.=NL("Rows","Sales Cr.Memo Line","Sell-to Customer No.","Gen. Prod. Posting Group",$B$4,"Type","Item","=NL(""Count"",""Sales Invoice Line"",""Sell-to Customer No."",""Gen. Prod. Posting Group"","""&$B$4&""",""Sell-to Customer No."",NF(,""Sell-to Customer No.""))","Number&0")For testing purposes, I've removed "Posting Date" from that query (I have a traditional NAV database where that field does not exist in the 'Line' tables - only the header tables).
If I add that field back in, I would expect it to look like this:=NL("Rows","Sales Cr.Memo Line","Sell-to Customer No.","Posting Date",$B$3,"Gen. Prod. Posting Group",$B$4,"Type","Item","=NL(""Count"",""Sales Invoice Line"",""Sell-to Customer No."",""Posting Date"","""&$B$3&""",""Gen. Prod. Posting Group"","""&$B$4&""",""Sell-to Customer No."",NF(,""Sell-to Customer No.""))","Number&0")As long as neither cell B3 nor B4 are blank (and assuming that the "Sell-to Customer No." field is populated in all records in the "Sales Cr.Memo Line" table), I would expect that function to work.
If it is possible that there are some records in that table where that field could be blank, you might try this:=NL("Rows","Sales Cr.Memo Line","Sell-to Customer No.","Posting Date",$B$3,"Gen. Prod. Posting Group",$B$4,"Type","Item","=NL(""Count"",""Sales Invoice Line"",""Sell-to Customer No."",""Posting Date"","""&$B$3&""",""Gen. Prod. Posting Group"","""&$B$4&""",""Sell-to Customer No."",""@@""&NF(,""Sell-to Customer No.""))","Number&0")As a general rule, I try to avoid the use of the @@ technique unless my data makes it necessary.
Please sign in to leave a comment.