How to sort currency field converted totext

[vshapiro]

I have currency field which contain negative and positive amounts. I need to convert it totext(I'll explain). But then I am sorting
by this field and my sort come up like:

5.25
6.00
-7.00
7.50
-8.25
9.00....

I need

-7.00
-8.25
5.25
6.00.....etc. And I cannot find a way to do this.


Why I am converting:
I have Parameter field SORT which contain all fields in the report. Then I have a formula Sort where all field should be Totext
Then I grouped by this SORT so user can select a field he wants to sort by. So... my sort is string...........

Thank you

 

[synapsevampire]

A numeric won't sort correctly as a string. And you haven't even touched on Boolean or dates yet.

<evil grin>

And why would you need:

-7.00
-8.25
5.25
6.00.....etc. And I cannot find a way to do this.

-7 shouldn't come before -8.25???

Anyway, you can pad numeric fields to get proper sortation.

-k

 

[vshapiro]

Any advices how I can get my sort properly?
Yes, it should be
-8.25
-7.00

sorry

 

[pandpp]

synapsevampire, you are eveil...

vshapiro,

You need to pad the left of your fields with spaces. Sorting works on the ordinal position of each characters ASCII value. Space is the lowest valued printable character. Add to that, it's hard to detect.

Something simple like Right({YourValue} + Space(10), 10) should do it for you. Now, if you sort on this, each value will have spaces to start, which comes before any other char. Hence the smallest number will be at the bottom.

The - also comes before 0, so the negative values will come before the positive ones.

Peter.

 

[lbass]

The following formula {@sortposneg} worked when tested, as long as the value was carried out to 6 decimals:

if val({@posneg}) < 0 then totext(val({@posneg})/(val({@posneg})*val({@posneg})),6,&quot;&quot else {@posneg}

//where {@posneg} is:

totext({table.posnegnumber},2,&quot;&quot

The string formula {@sortposneg} should be used only for the sort, with {@posneg} used for the display.

-LB

 

[lbass]

Oops, I should have simplified the formula to:

if val({@posneg}) < 0 then totext(1/val({@posneg}),6,&quot;&quot else {@posneg}

-LB

 

[vshapiro]

It is working with positive-negative but when I have figures like
10451.00, 10458.00, 106875.00, 10852.00 it come up as

10451.00
10458.00
106875.00
10852.00......???

 

[jerry5766]

Have you tried to use the formatting options of the TOTEXT() function? TOTEXT({field_name}, &quot;######.##&quot. This is what I use when sorting by a numeric field.

 

[lbass]

Change the formula to:

if val({@posneg}) < 0 then totext(1/val({@posneg}),6,&quot;&quot else totext(val({@posneg}),&quot;000000.00&quot //add zeros up to the maximum digits in the field to the left of the &quot;.&quot;

This pads the numbers on the left so that they should sort correctly.

-LB

 

[jerry5766]

I just noticed you said Currency field. Try
TOTEXT(TONUMBER({field_name}), &quot;######.##&quot.

 

[vshapiro]

lbass!

thank you very much. It works perfectly. it even works like:

if {?Sort} = &quot;Balance Due&quot; then
(
if {PACS_PROJECT_FINANCIAL.BALANCE_DUE} >= 0 then
totext({PACS_PROJECT_FINANCIAL.BALANCE_DUE},&quot;00000000.00&quot
else
&quot;#&quot; + totext(1/val(ToText({PACS_PROJECT_FINANCIAL.BALANCE_DUE},2,&quot;&quot),6,&quot;&quot;
)

jerry5766
I did try this. did not work


Thank you very very much for all your help

Veronica