Still sorting problem

[vshapiro]

I already posted a treat for this problem and got some explanations but still cannot figure out how to do this.

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


-8.25
-7.00
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...........
my @Sort formula looks like:

if {?Sort} = "Provider Name" then {PACS_PROJECT.PROVIDER_NAME}
else
if {?Sort} = "Final date" then totext({PACS_PROJECT_FINANCIAL.FINAL_ISSUE_DATE},"yyyy-mm-dd&quot
else
if {?Sort} = "Amount Due" then totext({PACS_PROJECT_FINANCIAL.AMOUNT_DUE},"000000000&quot
else
if {?Sort} = "Balance Due" then totext({PACS_PROJECT_FINANCIAL.BALANCE_DUE},"00000000&quot

and in the report I display just PACS_PROJECT_FINANCIAL.BALANCE_DUE field

I really need help

Thank you

 

[Ngolem]

Hmmm....I tested it and you are right it seems that the negative sign is ignored in the sort.

My workaround is to add a "#" to negative numbers. this bubbles them to the top of the alpha heap in the order that you want...obviously you don't display them that way...you can do that directly with the specific field

Try this

//@Sort
stringVar result := ""; //I like assigning the values

if {?Sort} = "Provider Name" then
result := {PACS_PROJECT.PROVIDER_NAME}
else if {?Sort} = "Final date" then
result := totext({PACS_PROJECT_FINANCIAL.FINAL_ISSUE_DATE},"yyyy-mm-dd&quot
else if {?Sort} = "Amount Due" then
(
if {PACS_PROJECT_FINANCIAL.AMOUNT_DUE} >= 0 then
result := totext({PACS_PROJECT_FINANCIAL.AMOUNT_DUE},"000000000&quot
else
result := "#" + totext({PACS_PROJECT_FINANCIAL.AMOUNT_DUE},"000000000"
)
else if {?Sort} = "Balance Due" then
(
if {PACS_PROJECT_FINANCIAL.BALANCE_DUE} >= 0 then
result := totext({PACS_PROJECT_FINANCIAL.BALANCE_DUE},"000000000&quot
else
result := "#" + totext({PACS_PROJECT_FINANCIAL.BALANCE_DUE},"000000000"
);

result;

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[vshapiro]

thank you very much
I'll try right now and let you know

 

[vshapiro]

Ok. It 'almost' works. All negative figures displays:

-1.00
-2.00
-3.00 instead of

-3.00
-2.00
-1.00.

And one more thing. when this apply to dollar amount it works ok, but in the beginning of the report I have some cents and this is come up like:
-0.15
-0.01
-0.30
-0.05
any idea why???

 

[Ngolem]

The problems with the "cents" is due to your Totext format....You have eliminated the "cents" portion so now they appear randomly

Change the "#" to "Z" and you will reverse the order

try this

//@Sort
stringVar result := ""; //I like assigning the values

if {?Sort} = "Provider Name" then
result := {PACS_PROJECT.PROVIDER_NAME}
else if {?Sort} = "Final date" then
result := totext({PACS_PROJECT_FINANCIAL.FINAL_ISSUE_DATE},"yyyy-mm-dd&quot
else if {?Sort} = "Amount Due" then
(
if {PACS_PROJECT_FINANCIAL.AMOUNT_DUE} >= 0 then
result := totext({PACS_PROJECT_FINANCIAL.AMOUNT_DUE},"000000000.00&quot
else
result := "Z" + totext({PACS_PROJECT_FINANCIAL.AMOUNT_DUE},"000000000.00"
)
else if {?Sort} = "Balance Due" then
(
if {PACS_PROJECT_FINANCIAL.BALANCE_DUE} >= 0 then
result := totext({PACS_PROJECT_FINANCIAL.BALANCE_DUE},"000000000.00&quot
else
result := "Z" + totext({PACS_PROJECT_FINANCIAL.BALANCE_DUE},"000000000.00"
);

result;

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[vshapiro]

thank you very much. it works with cents. when I change to 'Z' the negative figures come up in the end of the report but still like
-1.00
-2.00
-3.00 instead of

-3.00
-2.00
-1.00

but, i guess it is nothing can be done about this....????

 

[bdreed35]

I modified Jim's formula a little:

//@Sort
stringVar result := ""; //I like assigning the values

if {?Sort} = "Provider Name" then 
   result := {PACS_PROJECT.PROVIDER_NAME}
else if {?Sort} = "Final date" then 
   result := totext({PACS_PROJECT_FINANCIAL.FINAL_ISSUE_DATE},"yyyy-mm-dd")
else if {?Sort} = "Amount Due" then 
   (
     if {PACS_PROJECT_FINANCIAL.AMOUNT_DUE} >= 0 then
        result := totext({PACS_PROJECT_FINANCIAL.AMOUNT_DUE},"000000000.00")
     else
        result := totext({PACS_PROJECT_FINANCIAL.AMOUNT_DUE}/100000000,"000000000.00");
   )
else if {?Sort} = "Balance Due" then 
(
     if {PACS_PROJECT_FINANCIAL.BALANCE_DUE} >= 0 then
        result := totext({PACS_PROJECT_FINANCIAL.BALANCE_DUE},"000000000.00")
     else
        result := "Z" + totext({PACS_PROJECT_FINANCIAL.BALANCE_DUE},"000000000.00");
   );

result;

~Brian

 

[vshapiro]

Thank you very much. The only thing I still not able to get - make negative figures come up from larger to smaller:

-3.00
-2.00
-1.00

 

[Ngolem]

dbreed35 - What is the purpose of this division???? that is the only change I see....

({PACS_PROJECT_FINANCIAL.AMOUNT_DUE}/100000000,"000000000.00"

if anything this will make a problem worse since you only sort to 2 decimal places as per the Totext mask.

vshapiro - DID YOU CHANGE THE "#" to "Z"??? that should have resorted the values in the opposite direction

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[bdreed35]

Jim -

Not really sure what I was thinking! I think I thought it was working but was actually sorting on the original field.

I just went back to one of the other posts for this topic and saw a formula posted by lbass. I think this is what I had in my head as to what I wanted to accomplish.

I am going to attempt to successfully merge lbass's fornula with Jim's:

//@Sort
stringVar result := ""; //I like assigning the values

if {?Sort} = "Provider Name" then 
   result := {PACS_PROJECT.PROVIDER_NAME}
else if {?Sort} = "Final date" then 
   result := totext({PACS_PROJECT_FINANCIAL.FINAL_ISSUE_DATE},"yyyy-mm-dd")
else if {?Sort} = "Amount Due" then 
   (
     if {PACS_PROJECT_FINANCIAL.AMOUNT_DUE} >= 0 then
        result := ToText({PACS_PROJECT_FINANCIAL.BALANCE_DUE},2,"")
     else
        result := "#" + totext(1/val(ToText({PACS_PROJECT_FINANCIAL.BALANCE_DUE},2,"")),6,"");
   )
else if {?Sort} = "Balance Due" then 
(
     if {PACS_PROJECT_FINANCIAL.BALANCE_DUE} >= 0 then
        result := ToText({PACS_PROJECT_FINANCIAL.BALANCE_DUE},2,"")
     else
        result := "#" + totext(1/val(ToText({PACS_PROJECT_FINANCIAL.BALANCE_DUE},2,"")),6,"");
   );

result;

Sorry for the bad post before.

~Brian

 

[vshapiro]

i chaged '#" to 'Z' and it did not really help.
it come up like this:

1.00
2.00
3.00
4.00
-1.00
-2.00
-3.00

and with '#' it looks like

-1.00
-2.00
-3.00
1.00
2.00
3.00

So...

 

[bdreed35]

vshapiro:

try my last post out.

~Brian

 

[vshapiro]

i did.
when I have large amounts in positive figures they come up like:
1000.00
101451.54
1035.48
10421.50
106292.30 and so on....

do you know how to solve it?
because all negative figures come up perfectly

 

[Ngolem]

Ohhhh I see the problem this is not easy to do sorting alphabetically

You want it

-3.00
-2.00
-1.00
0.00
1.00
2.00
3.00

So...Using a "#" brings the neg numbers to the top...but the first half you want sorted descending and the second you want ascending. There would be no problem if we strictly used numbers but since the first part of your formula uses text then all should be text to satisfy the IF-Then

I don't see a workaround here except for using 2 sort formulas...I think that is the only way around your problem.

We will divide your formula into 2

//@Sort1 for date and Provider name
stringVar result := "A";

if {?Sort} = "Provider Name" then
result := {PACS_PROJECT.PROVIDER_NAME}
else if {?Sort} = "Final date" then
result := totext({PACS_PROJECT_FINANCIAL.FINAL_ISSUE_DATE},"yyyy-mm-dd"

result; //if {?Sort <> these then result = a constant...ie. no sorting


//@Sort2 (For numeric values)
NumberVar Numresult := 1;

if {?Sort} = &quot;Amount Due&quot; then
Numresult := {PACS_PROJECT_FINANCIAL.AMOUNT_DUE}
else if {?Sort} = &quot;Balance Due&quot; then
Numresult := {PACS_PROJECT_FINANCIAL.BALANCE_DUE};

NumResult; //will = 1 if {?Sort} is different..ie a constant

Sort on @sort1 first and @sort2 second though it probably doesn't matter.

I've never really tried this but it might work






Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[vshapiro]

Jim
I just got it:
the only update to the formula is - for &quot;Balance Due&quot; (this is the only field which has negative numbers and it looks 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;
)

thank you very very much for all your help

Ngolem!
thank you very much

 

[Ngolem]

Glad I could be of some help....interesting solution

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!