Sorting on Alpha and Numeric using parameters 2

[KingArthur]

I have designed a report that produces a list that I would like to sort on the persons name or the value of their loan balances.

I can do this using the record sort expert, but I would like to give the user the option to choose.

I can create a parameter and the use the result in a formula field to sort on like so:-

if {?Sortby} = "Name" then {@ContractorName} else {@LoanBalance}

However, as the name is alpha and the loan numeric, the formular is rejected.

I've tried converting the loan 'ToText' but ran into many problems, especially as there are some negative values too.

Any help would be appreciated.

Thanks

Martin

 

[Turkbear]

Hi,
Usually that cannot be done, as you have found, but maybe this:

if {?Sortby} = "Name" then
 AscW({@ContractorName}) 
else {@LoanBalance}

This converts the name into a Number ( the ASCII ( Unicode) value of the string)..It should sort as expected..( I think - I am not where I can test it yet)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[KingArthur]

Perfect! Working fine! Report finshed!

I thought of changing the number to text. Didn't even know it was possible to change text to a number.

Many thanks

Martin

 

[Turkbear]

Hi,
Glad it worked since I can use it in some of my reports
( Frankly, I had not thought of that method until you posted your issue..So, Thanks to you as well)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Turkbear,

I just tested this and it sorts by the first letter in the string and is also case sensitive (as I'm sure you realized).

Another approach would be to create two formulas and add them as sort fields:


if {?Sort by} = "Name" then {@Contractor's Name}

if {?Sort by} = "Balance" then {@LoanBalance}

If the Option is not selected, the formulas will default to "" or 0 respectively, and have no impact on the sort.

-LB

 

[Turkbear]

Hi,
Nice...
Glad to see that there are always alternatives..

As to the sorting, I thought AscW() evaluated the ASCII value of the entire string ( the W seems to indicate Word ) not just the first letter...



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[KingArthur]

Hi LB

Yes I did notice I'd jumped the gun a bit and that it was only sorting on the first letter.

I was halfway through creating some long and untidy code to try and expand the sort to the first 10 letters when yoursolution came through which was a much better solution.

I'm supprised many others have not had this same problem and that a solution has not already been built into Crystal.

Anyway, thank you to both very much for your assitance.

Regards

Martin

 

[Turkbear]

Hi,
OOPS, I should have read more carefully..

The AscW (str) function returns the Unicode value of the first character of the string.



I hate Fridays ( But not Friday's)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
If you want to sort on the entire string's summed Unicode ( ASCII) value this would work:

@sorter

numberVar inx := 1;
numberVar Sn := 1;
for inx := 1 to Length ({Table.StringField}) do
Sn := Sn + AscW({Table.StringField}[inx]);

Sn

Not sure of its usefulness however, but might be interesting...






To Paraphrase:"The Help you get is proportional to the Help you give.."