Hi, I have the following formula:
E14 = =NL("filter","G/L Entry","entry no.","Posting Date",$D$2,"Document No.",IF($D$3="No",0,"*"),"Source Code","CASHRECJNL|GENJNL|PAYMENTJNL|FAGLJNL|BANKREC","Reversed","False","Source Type","Vendor")
F14 = =NL("filter","Detailed Vendor Ledg. Entry","Transaction No.","Entry Type","Application","Unapplied","False","vendor ledger entry no.",$E$14)
G14 = =NL("table","Detailed Vendor Ledg. Entry","Vendor Ledger Entry No.","Unapplied","False","Entry Type","Application","Transaction No.",$F$14,"Vendor Ledger Entry No.","<>"&$E$14)
the problem is with G14 because I use "Vendor Ledger Entry No.","<>"&$E$14.
How should I write this formula correctly ? Thank you
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hi,
Cristian: Actually the problem in this case has nothing to do with putting the <> in the Jet formula. The formula they have would work fine except that the cell reference is referencing the results of an NL(Filter) function. The problem is that you can't use <> with the results of an NL(Filter) function.
Andy: So I'm assuming that what you want is to filter by all the vendor ledger entry numbers in the Detailed Vendor Led. Entry table which DON'T match the filters in your NL(Filter) function, is that correct? In this case, you could use NP(Difference). You might change your formula in G14 to look something like this:=NL("table","Detailed Vendor Ledg. Entry","Vendor Ledger Entry No.","Unapplied","False","Entry Type","Application","Transaction No.",$F$14,"Vendor Ledger Entry No.",NP("Difference",NL("filter","Detailed Vendor Ledg. Entry","Vendor Ledger Entry No."),$E$14)
NP(Difference) will take all the values in the first set and remove from those all the values in the 2nd set, so this would take all the vendor ledger entry numbers and eliminate from those all the ones that match your filters in E14. Note that you could extract the NL(Filter) function and put it in another cell, but you CAN'T extract the NP(Difference) formula or it won't work; it must be embedded in the formula. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
I already encountered that problem when I tried to put a filter on the date.
How I did, is I wrote the "<>" in the cell directly and then made a cell reference to that cell in the JetReports formula.
In your example, you add the "<>" in E14 directly and then make the reference in JetReports to E14.
Or let's say, you write in F14 "="<>"&E14 and then you reference F14 in he JetReports formula.
If somebody has a simpler way of doing it, please let me know!
Kind regards,
Cristian -
Jet Reports Historic Posts Dear Hughes & Cristian,
Thanks for your answers. NP Difference works, but it got side effect. The report is running way longer now.
I guess it's because when I use "<>", Jet only scans for a couple hundred records & find those who don't match.
But if I use NP Difference, then the whole table is being scanned. And I can only see this getting worse as our database grow.
But thank you, I already found some other way to filter this table after seeing your answer. :D
Cheers,
Andy -
Jet Reports Historic Posts Hi Andy,
Great to hear you got it figured out! It's always better for performance to try to find a combination of filters to give you the records you DO want rather than to get the ones you don't want and do a difference. That's not always possible, but usually it is. Glad to know you've got things figured out.
Regards,
Hughes
Please sign in to leave a comment.