Hi everyone,
How about this one… In my G/L Account table I have a field called "No. 2". This field contains a value to group my G/L Accounts by. The value is 1 or 2 digits. The field is setup as a Code field - alphanumeric - but the value is always a number.
When I create an NL Unique function to group by this field it looks like this:=NL("Rows","G/L Account","No. 2")But the sorting I get shows 1 10 11 12 2 21 22 (in Rows of course).
Too bad, 'cause I wanted 1 2 … 10 11 12 21 22. So, I thought I could use the Excel VALUE command in my NL :idea: :=NL("Rows","G/L Account","=VALUE(NF(,""No. 2"")")
But now I get '0' as end result :(
Pressing my Easy button didn't help, but fortunately there's the Jet Community! Anybody an idea why this doesn't work?
Date
Votes
8 comments
-
Jet Reports Historic Posts Hans,
They easiest way to solve this is by using "leading 0's" in the field (01, 02, 03). Your sorting will be ok then.
Otherwise, changing your field to "integer" would help too.
NAV promotes this "problem" in it's G/L Account table anyway (with the G/L Account numbers), it deliberately takes a Text field to invoke this type of "wrong" sorting:
1
10
10000
11
11000
Rename them to:
00001
00010
10000
00011
11000
And your sorting will be:
00001
00010
00011
10000
11000
———
Trough Jet I can't really think of a solution right away.
You have to look out with these types of fields anyway, because "filter ranges" will return unwanted results (1..20 will return 1, 11, 12,…) -
Jet Reports Historic Posts Hi Jan,
Thanks for the reply. I understand how the sorting works and how to create a "work around" solution. But that's not the reason I posted this here.
I've done this type of command before with LEFT, RIGHT, YEAR etc. But not with VALUE… and now that it doesn't work I wonder why. -
Jet Reports Historic Posts Hans,
You can try…=NL("Rows","G/L Account","=TEXT(NF(,""No.2""),""0000"")") -
Jet Reports Historic Posts Yes, that's a nice one, Sherman. But do you have any idea why VALUE doesn't work? Maybe you could ask the developers?
Or to put it in a different way: which Excel commands may be used in the NL like your TEXT command and my LEFT, RIGHT etc.? Or may not be used? Is there a list or some kind of rule? -
Jet Reports Historic Posts Today the story continues….
During an on site training course we had the same problem on 1 of the PCs with a LEFT and MID command. 4 students had no problems at all with this type of command and 1 student continuously got 0 as the return value.
The commands looked like this:
=NL("Rows";"Item";"=MID(NF(;""No."");4;1)")
and
=NL("Rows";"Fixed asset";"=LEFT(NF(;""No."");4)")
So I'm afraid there's something more to this command than I thought up until now. But what….?
We used Excel 2003, Jet Reports 7 and NAV 4 SP3. -
Jet Reports Historic Posts Hans,
I went back to your original posting about the using the VALUE function and found a problem with your formula=NL("Rows","G/L Account","=VALUE(NF(,""No. 2"")")
should be=NL("Rows","G/L Account","=VALUE(NF(,""No. 2""))")
Unfortunately, Jet Reports doesn't report errors when you have a problem with a calculated field.
I suspect that this may have been the problem in your class also.
As far as I'm aware, all excel functions should work when embedded in a NL formula -
Jet Reports Historic Posts Hi Sherman,
You were right about the VALUE function. Thx. Missed the bracket.
But the other 2 functions are exactly as they were in the report. No typing errors - we compared them with the functions on the other computer. So… maybe there's more? -
Jet Reports Historic Posts Hans,
Sorry, but I don't know why you would have different results for one student. Your formulas look correct.
Please sign in to leave a comment.