I've created a report that is essentially the same information as the cash application report inside of NAV, except it also includes credit memos. I'd like to build the report so that it creates a tab for each payment type in my company, rather than having to run it multiple times. I know that I should use the NL(Sheet function for this, but I have no idea how. Can someone help me? I've attached the file.
Thanks!
Date
Votes
11 comments
-
Jet Reports Historic Posts Hi,
It looks to me like you would just want an NL(Sheets) function like this:=NL("Sheets","Payment Method","Code","Code",$H$5)
Then you could modify your NL(Rows) function in B9 to reference the NL(sheets) function instead of the option value in H5. You might also only want to see payment methods which actually have vendors, which you could do by changing the above to this:=NL("Sheets","Payment Method","Code","Code",$H$5,"Link=","Vendor","Payment Method Code","=Code")
Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
This does help, but where do I put the first NL Sheets formula?
Thanks,
Amanda! -
Jet Reports Historic Posts Hughes,
You can ignore my previous question, as I believe I got it working. Despite having used your second suggestion, I am getting tabs for payment methods that do not have values. Attached is my updated report. What else am I missing?
Thanks!
Amanda -
Jet Reports Historic Posts Hi,
I think you just need to add another nested link= to the Vendor Ledger Entry similar to what you're doing with NL(Filter) in cell B9. Your NL(Sheets) formula might look more like this:=NL("Sheets","Payment Method","Code","Company=",$D$3,"Code",$B$5,"Balance Due","<>0","Link=","Vendor","Payment Method Code","=Code","Link=","Vendor Ledger Entry","Vendor No.","=No.","Due Date",NP("DateFilter",,$H$3),"Remaining Amount","<>0")
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
This has been such a great learning experience for me. It works beautifully. I need to put one more filter on my invoice lines to exclued items that are on hold. I've put the following code in but the " " doesn't seem to capture what I'm trying to do. I still get all entries within the rest of the filters (i.e. date, vendor). I am trying to exclude any entries where there is some value in the On Hold field in the vendor ledger entries. Any suggestions?=NL("Rows","Vendor Ledger Entry",,"Vendor No.",$C10,"Remaining Amount","<>0","Company=",$D$3,"Due Date",NP("DateFilter",,$H$3),"On Hold"," ")
Thanks!
Amanda -
Jet Reports Historic Posts Amanda,
It sounds like you are trying to filter to only see the blanks values, right? I think you need to use 2 single quote characters as your filter like this:=NL("Rows","Vendor Ledger Entry",,"Vendor No.",$C10,"Remaining Amount","<>0","Company=",$D$3,"Due Date",NP("DateFilter",,$H$3),"On Hold","''")
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts That was it! Thanks again for all the help! I love this community.
-
Jet Reports Historic Posts Okay, I have two more questions related to this report.
1. Within the report I've created a tab that shows any invoices that are on hold so that they can be taken off hold before payments if necessary. Is there a way to make the report only show results if there are results? Currently the tab shows up and I get the empty filter error.
2. I need to figure out how to filter the report by two different kinds of dates. Essentially, if I am making a payment today (9/5/13) I want to capture all invoices that have a discount date thru 9/11/13 (so we take all discounts) but ONLY if it has a discount, otherwise I want it to only caputure invoices due thru 9/9/13.
This is how the cash application report works directly inNAV.
Clear as mud?
Thanks! -
Jet Reports Historic Posts Looking again for an answer to how to hide a sheet if there are no results. Right now I get empty filter results of #VALUE! and I'd like those sheets to be hidden.
Thanks,
Amanda -
Jet Reports Historic Posts How about something like this in cell A1 of that sheet:
=IF(ISERR(CellWithErrorInIt),"Auto+HideSheet","Auto+Hide+Values") -
Jet Reports Historic Posts How about something like this in cell A1 of that sheet:
=IF(ISERR(CellWithErrorInIt),"Auto+HideSheet","Auto+Hide+Values")
Actually, that won't work, because A1 can't be a calculated value, but the good news is, when you have Hide+? in A2, you can put the calculation in B2 and when the outcome is HideSheet, Jet will hide the sheet for you..
See here as well: http://kb.jetreports.com/article/AA-00522/0/Conditionally-Hiding-Rows-Columns-or-Sheets.html
HTH
rmw
Please sign in to leave a comment.