Sort Numerically

[jenschmidt]

Hello! Good Afternoon! How er'ya!

Super-duper easy question for all you really smart people!

I have a report sorted by a string field that contains numbers - but it sorts 10, 11, 12, 1, 20, 22, 2, etc and I want it to sort 1, 2, 10, 11, 12, 20, 22 etc - what is the formula to make that happen?

jennifer.giemza@uwmf.wisc.edu

 

[mbarron]

Create a formula that changes the string to a number:

tonumber({your.field})

Then group on the formula. Delete the "Group Name" field that gets created. Insert your original field in the group header band. Mike

 

[jenschmidt]

Sorry about that! After I posted, I realized the answer to the question. Here's the twist I wasn't sure about - what if the field contains numbers and characters such as:

This is how it sorts: This is how it should sort:
00100F 00100F
0100A 0100A
10 2
10000B 10
100B 10A
100S 10B
10A 100B
10B 100S
11 10000B
12 11
13 ETC
14
15
16
17
18
19
2
20
20
21
22
22
23
24
25
26
30A

Is it possible to sort that type of info correctly? jennifer.giemza@uwmf.wisc.edu

 

[mbarron]

What is the sort logic? At first I thought it was by first position then second then third etc... but the "2" threw me off.

This is how it sorts: This is how it should sort:
00100F 00100F
0100A 0100A
10 2
10000B 10
100B 10A
100S 10B
10A 100B
10B 100S
11 10000B
12 11
13 ETC
Mike

 

[jenschmidt]

I would like the sort to be in numeric order - I'm not sure where the numbers that start with zero would really fall out. jennifer.giemza@uwmf.wisc.edu

 

[mbarron]

if you use the val() function you end up with:
2
10
10A
10B
11
12
13
14
15
16
17
18
19
20
20
21
22
22
23
24
25
26
30A
00100F
0100A
100B
100S
10000B


Which is nothing like your "how it should sort" Mike

 

[jenschmidt]

I think that's what I'm looking for! Thanks a bunch!! jennifer.giemza@uwmf.wisc.edu