Greetings to All!
It's been a while since I've posted but I'm stumped!
Long story short, I need to create a report that shows how much product the company has sold by producer (Vendor). I have found this difficult, mainly because of the filters between tables. I can get to the Lot No. and through that table to the Order No. from the Vendor (which contains the vendor info) but I cannot seem to work the filters properly. Here's what I have so far:
=NL("Sum","Item Ledger Entry","Invoiced Quantity","Entry Type","Sale","Posting Date",E$7,"Item No.",$B11,"Lot No.",NL("Filter","Lot No. Information","Lot No.","Original Purchase Order No.",NL("Filter","Purch. Inv. Header","Order No.","Buy-from Vendor No.",$D21)))
Any thoughts? I'm not even sure if i can NL("Filter" inside of an NL("Filter" but I gave it a shot.
The Lot No. Information contains the Order No. I can then go into the Order No. and retrieve the Vendor No.
Any help and/or wisdom from the community will be greatly appreciated.
Thank you.
Date
Votes
2 comments
-
Jet Reports Historic Posts Do you change vendors (item by item) on a regular basis and, thus, need to know which vendor supplied which item at the time the item was sold?
If not, you might be able to simplify things a bit by creating a grouping report where you list out you vendors, then all items associated with that vendor, and then the invoiced quantity sum for those items.
Otherwise (although my NAV does not include a "Original Purchase Order No." field in the "Lot No. Information" table), you appear to be on the right track [although you might want to take a look at the Jet KB about using LINK=. It tends to be faster than NL(Filter) ].
-HP -
Jet Reports Historic Posts Do you change vendors (item by item) on a regular basis and, thus, need to know which vendor supplied which item at the time the item was sold?
Yes. We actually have multiple vendors at one time providing the same item to us, regularly. And yes we need to know which vendor supplied which item at the time the item was sold. I have the spreadsheet laid out and all other formulas are working with the exception of this most important one.Otherwise (although my NAV does not include a "Original Purchase Order No." field in the "Lot No. Information" table)
Yeah, I think this was a special addition to our NAV.[although you might want to take a look at the Jet KB about using LINK=. It tends to be faster than NL(Filter) ]
I have not yet ventured in to the use of LINK=. I read about it in the Jet manual and I got confused. Figured out NL("Filter" first so that's what I've been using. I'll have to look into it though because NL("Filter" does slow things down a bit.
Thanks for the reply!
Please sign in to leave a comment.