Hi All,
I am creating a Jet table which pulls information from the Navision Item Ledger table. In particular I need the "Invoiced Quantity" field and the flowfield "Cost Amount (Actual)". In the Navision table both of these fields are held as a negative number.
What I want to do is to change these values from negative to positive. I can get round it by adding two additional calculated columns to the table whereby I multiply the original value by -1 to reverse the sign. It would be much better though if I could do this "on-the-fly" as it were, but I can't find a way to do this.
Is what I am trying possible, or do I need to just do it by adding the additional columns to the table?
Thanks for your help.
Garry.
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi Garry,
Of course you can create an "on-the-fly" calculation simply by using the absolute function of excel. Here's the sample:=ABS(NL("first","Item Ledger Entry","Quantity","Entry No.","124"))
would that work for you ?
Andy -
Jet Reports Historic Posts Hi Andy,
In a "normal" Jet report yes, that works fine. But I can't see how to do this when creating a Table as the Table fields don't use the NF function, they simply contain the field name.
I have added the file below so that you can see what I am trying to do. It is the values in columns M and O that I cam trying to change.
Jet Table Example.xlsx
Thanks again,
Garry. -
Jet Reports Historic Posts Ahhh… I see.
The problem occurs because you are using NL Table. Well… I don't know how to solve it either.
I thought it would make sense to use NP(Formula) for this case. But then I found out that NL(Flowfield) wouldn't work inside NP(Formula). And neither would the "Invoiced Quantity" which is not a flowfield.
Maybe Hughes or other community members know how to solve it? Or maybe this would be a nice feature request for future releases of Jet Reports. For now, though, I think adding additional columns would be your only solution.
Andy -
Jet Reports Historic Posts Hi Gary, Hi Andy,
I suppose you made your report using the Table Builder?
In that case you can always add a calculated column by clicking on the "Add Formula" button on the bottom (see picture). You can then refer to your tables you want to change from negative to positive using a "@". And in front of the reference just put a minus "-". This will put all your negative number into positive ones (and vice versa). Then you get a formula like this one: =NP("Formula";"-[@[Invoiced Quantity]]").
Good luck!
Regards,
Bert
Please sign in to leave a comment.