Hi, I'm Using NAV 2013 R2 as database,
Im making an report that group some type of credits,
I get the list with this code:NL("Rows","tableHeader","Description","+Posting Date","01/01/2015")
then Im using an sum formula NL("Sum","tableLines","Credit Amount",","Description",$A1,"Posting Date","01/01/2015")
The problem is that the people who writes the description to the database makes mistakes and inputs description data like:
"AMERICAN BANK"
"AMERICAN BANK "
as you can see the second AMERICAN BANK has an space " " as last character, and this is a problem cause JetReports Unified both Descriptions as one (I think ignores the last space)
Making this Wrong result
Description———————- SUM
WEST BANK———————-1000.00
AMERICAN BANK—————-1300.00
AMERICAN BANK —————1300.00
================================
Total —————————-3300.00
================================
The correct Result is:
Description———————- SUM
WEST BANK———————-1000.00
AMERICAN BANK—————- 300.00
AMERICAN BANK —————1000.00
================================
Total —————————-2300.00
================================
HOw can I get the sum correct with the description?
Date
Votes
6 comments
-
Jet Reports Historic Posts I'm not familiar with TableHeader or TableLines as tables in Nav - but that may be just my database.
I imagine however that there is some type of Number or Code associated with these descriptors - can you use those to identify the lines, then add a column for the description?
If "American Bank" and "American Bank " were two separate customers in my system, they would have unique ID codes that I could use to pull my sales figures for (which I need to do for several of our customers with similar names). -
Jet Reports Historic Posts Tables names are just examples
Im working in a "G/L Entry" report
where there's is no ID for each description , and for the way of work, its neccesary to group by Description, the problem is work with Type Data like text, because as you said the best is work with numbers. :? -
Jet Reports Historic Posts You can format the description before retrieving it by using a excel formula instead of just a field name.
Using something like this might work.NL("Rows","tableHeader","=TRIM(NF(,""Description""))","+Posting Date","01/01/2015")
Be aware that this can increase the running time of the report, because the formula has to be applied to all the data before any results can be returned.
rmw
PS I didn't do a syntax check :) -
Jet Reports Historic Posts Hello JulioJam.
I see the problem you are referencing. I just put in a new credit memo with two lines, one with an ending space and one without. When I do a data dump of the table the records show with the space, but if I do a rows statement on the description it only brings back one result and treating both sets of text as the same with and without the space. If I use this data to do a sum I receive the same results as you do incorrectly increasing the total because it treats all the text the same.
I can clearly see the space in my returned results, but Jet is disregarding it. It looks like this might currently be a limitation of Jet Reports. -
Jet Reports Historic Posts Another thought. I have a field on my credit memo that doesn't keep end spaces. I wonder if your Navision partner could change the field to not store end spaces?
Hope this helps.
Jason -
Jet Reports Historic Posts Another solution, to prevent people mistyping, would be to create a drop down list for the users to choose from: https://msdn.microsoft.com/en-us/library/dd339058.aspx
This way you don't have to have this problem in the future. Doesn't help your current situation as you would have to modify all of the bad data to keep it consistent but going forward, this may be something to consider.
Please sign in to leave a comment.