Hi,
I have a problem with a formula which is based on the Item Ledger Entry. What I want to do is sum the total quantities of the different pallet types that we have on stock.
Let me start from the beginning. We have a couple of customized programming in our Microsoft Dynamics NAV which needs to be considered.
We are selling our products as pallet units only. Therefore we are using the table "Item units of measure" to determine the packing specs for our products. The relevant figures are "Quantity per unit" and "pallet type code"
From the Item Ledger Entry we can get the "Item Nr.", "Unit of measure", "quantity"
I started with 2 Filters:
in B15: =NL("filter";"5404 Artikeleinheit";"2 Code";"50160 Palettenartencode";$A15)
in C15: =NL("filter";"5404 Artikeleinheit";"1 Artikelnr.";"50160 Palettenartencode";$A15)
to get the Item No. and the Unit of measure that have the relevant pallet type code
Afterwards I use these filters to calculate the qunatities of pallets.
=NL("Sum";"Item Ledger Entry";"=nf(;""restmenge"")/NL(""First"";""Item units of measure"";""Quantity per unit"";""Item No."";nf(;""Item No."")"";""Code"";nf(;""Unit of Measure""))";"Item No.";$C15;"Unit of measure";$B15)
With this formula I get a #value error. I get corrct results if I try to run the single parts of this formula separately. I am sure that there must be a mistake in the syntax. BTW I am from Germany and i tried to translate the names of the tables and fields. It is possible that there is a wrong translation but the names are definitely correct in my Excel sheet.
Maybe someone of you is able to help me.
Thanks
Andreas
Date
Votes
5 comments
-
Jet Reports Historic Posts Hi Andreas,
So I have a couple of questions. First, if you do a Debug on the cell with the formula, what does it tell you? If I were debugging this formula, I would want to know if the problem was with the calculated field you are using or NL(Filter) formulas. First I would do a debug to see what the error message is. Then I would try removing the calculated field and maybe just sum the restmenge field and see if that fixed the problem. Then if that didn't fix it, I would put the calculated field back and try removing the filters for Item No.,$C15 and then for Unit of measure,$B15 and see if the problem was with one of the filters. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
thanks for your answer. I tried the debugging but I only got an error message which I attached to this post.
To find the error I made some testing:
The filters in b15 + c15 remain unchanged
=NL("count";"5404 Artikeleinheit";;"2 Code";B15) - the result seems to be correct
=NL("count";"5404 Artikeleinheit";;"1 Artikelnr.";C15) - the result seems to be correct
=NL("Summe";"32 Artikelposten";"13 Restmenge";"2 Artikelnr.";C15;"5407 Einheitencode";B15) - the result seems to be correct
The filters seem to be working. But I get the attached error message when I tried to apply the filters to the third function.
Regards
Andreas -
Jet Reports Historic Posts Hmmm, what version of Jet Reports are you using Andreas? This looks like a bug that could have already been fixed…
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
we are using Jet Reports 2010 Version 10.1.11040.0 with Microsoft Navision NAV 2009 SP1.
Regards
Andreas -
Jet Reports Historic Posts Hmm, in this case you should log a ticket on our support site for this. Since it's an unexpected exception message like this, we should deal with it there. Thanks!
Regards,
Hughes
Please sign in to leave a comment.