Grouping and Sorting by Odd and Even Numbers

Question

I am trying to create a report which has addresses in form of house Nbr and street Name. I want to group all address by street name and then order them by house nbr which is a string but should sort like a number. Ideally i would like the odd ascending and then the evens descending so that my list would look like 1,3,5,7,9 .... 8,6,4,2 How would i go about this ? I created first group on street name and then 2nd group on house number with a formula for sorting of nbrs i created a Formula Field OddEven with

ToNumber({tbl_FarmMaster.sano}) MOD 2

but i am having hard time applying that to my group

Answer 1

Create two formulas like below. Let's call them oddFirst and negativeEven.

oddFirst formula:

ToNumber({tbl_FarmMaster.sano}) MOD 2 == 1 then
   1 //it is odd
else
   2 //it is even

negativeEven formula:

if ToNumber({tbl_FarmMaster.sano}) MOD 2 == 1 then
   ToNumber({tbl_FarmMaster.sano}) //it is odd
else
   -ToNumber({tbl_FarmMaster.sano}) //it is even, note the negative sign

Then create two groups to sort:

1. first by the formula oddFirst
2. second by the formula negativeEven

Show the {tbl_FarmMaster.sano} field.