Hi All,
Please can someone help.
I need to return item rows in NAV where the sum of the invoiced quantity must be less than a certain figure ie. 70 based on certain types of Item ledger entries and a link to the BOM ledger entry table and i don't want to use the Hide+? option as i dynamically build a pivottable structure and don't want 0 values coming in through the background.
This is what i have tried.
=nl("Rows","Item",{"No.","Description","Inventory","Qty. on Sales Order","Qty. on Purch. Order","Lead Time Calculation"},"Blocked","False","No.","@@"&ItemNo,"=-((nl(""Sum"",""Item Ledger Entry"",""Invoiced Quantity"",""Item No."",NF(,""No.""),""Entry Type"",""Sale|Consumption"",""Location Code"",""*"",""Posting Date"",""*"",""Key="",{""Item No."",""Entry Type"",""Variant Code"",""Drop Shipment"",""Location Code"",""Posting Date"",""Entry No.""})-nl(""Sum"",""Item Ledger Entry"",""Invoiced Quantity"",""Item No."",NF(,""No.""),""Entry Type"",""Negative Adjmt."",""Location Code"",""*"",""Posting Date"",""*"",""Key="",{""Item No."",""Entry Type"",""Variant Code"",""Drop Shipment"",""Location Code"",""Posting Date"",""Entry No.""},""Link="",""BOM Ledger Entry"",""Reference Entry No."",""=Entry No."",""Key="",{""Reference Entry No."",""Entry No.""}))/6)","NUMBER&<70")
I know about the text limitation (I reference cells to get around this) but the issue i actually have is that if i remove the second nl(sum) from the formula, it works.
If i remove the first nl(sum) of the formula, the second part only works if i remove the "Link=" which i need.
If i try to use the entire formula less the "Link=" it still does not work which i suspect is that Jet does not allow multiple formulas in the calculation either unless i have to do this differently in my formula when trying to do first nl(sum) - Second nl(sum).
Your assistance is greatly appreciated.
Kind Regards
Tom
Date
Votes
2 comments
-
Jet Reports Historic Posts That is a pretty complex function to try and debug. You may need to contact a designer directly for help with that one.
To try and troubleshoot on your own, have you tried breaking up the functions into separate cells and referencing those cells to see where the breakdown happens?
If you can see the cell that isn't working properly, you may be able to figure out what needs to be fixed with that part of the function. -
Jet Reports Historic Posts Hi JET-btr,
Thanks for the reply.
I have indeed tried breaking it down and its only when i use "link=" in the formula or any sort of calculation ie (+,-,*,/) etc when its put together that the error occurs.
Anyway, thanks for the feedback.
Tom
Please sign in to leave a comment.