My contract management team as asked for a report that compares how many items that were invoiced in a given month were items that were on a sales price worksheet.
I started with this NL
NL("Count","Sales Invoice Line",,"Posting Date",G$10,"Type","<>@@","Variant Code","@@","No.",NL("filter","Sales Price","Item No.","Starting Date","''|"&H9,"Ending Date",H8&"|''"))
Counting the # of sales invoice lines during the posting period, that had a line type of NOT BLANK, and no variant code. (All variant code items are on the price worksheet and calculated in a seperate cell) where the "No." field of the sales invoice line matched an item number in the sales price table that was open during the posted period. However, i also need to filter the sales price list by the customer number from the sales invoice line in this calculation.
In a seperate calculation, i need to do the above, but not filter by the customer number, but filtering by the customer type code (global dimension 1) thru the dimension value table to get the "CODE" field to match up to the "Sales COde" field in the sales price table.
Any help would be appreciated.
If anything needs clarification or further description, please let me know.
Thanks in advance for your help.
Date
Votes
2 comments
-
Jet Reports Historic Posts I think i would use "link=" instead. It's a little harder to use, but i think it will work. Try
NL("Count","Sales Invoice Line",,"Posting Date",G$10,"Type","<>@@","Variant Code","@@","Link=", "Sales Price","Item No.","=No.","Starting Date","''|"&H9,"Ending Date",H8&"|''","Customer No.",NF(,"Customer No."))
It's midnight here, so I'm not 100% sure of the field names (I'm to tired to start Excel to look). But I'll check back tomorrow evening and see if this works for you.
Also, I'm not sure what you need on the second formula and why need need to use the dimension values table. -
Jet Reports Historic Posts I haven't used the link feature before, but i will definately give it a shot.
the reason i need to get to the dimension value table is that our contract management group has setup items on the sales price worksheet 4 different ways.
1. By variant code, no big deal on this one, sales invoice lines with a variant code <>@@
2. by customer number, the sales invoice line customer no. & item must match the sales price table, code & Item no. field and be within the posting range.
3. by global dimension 1 code, the sales invoice line global dimension 1 code. & item must match the sales price table, code & Item no. field and be within the posting range.
4. by divsion (custom field in the dimension value table - mulitple global dimension 1 codes make up a divsion) (dimension value.code links to sales invoice line.global dimension 1) so i have tofigure that link out, because that field is not in the sales invoice line table and match item numbers to the sales price table.
Please sign in to leave a comment.