Crystal Sort order

Hi hope this is easy.  Would like to build a formula for Grouping/sorting purposes.  I would like to sort a currency field descending then a character field.  I was able to use the ToText Function to convert the Currency to a string then concatenate it to my text field. Problem is the currency doesn't seem to want to format.  Here is the Syntax: totext({TimeEntry.TEBillRate},0,'####')+{Employee.EmpTitle}.  It is displaying as $85 + the sting, I would like to have it come out with a couple leading blanks with no dollar sign like this "  85"+Text.  thanks  

GregFromSeattleAsked:
Who is Participating?
 
agandauConnect With a Mentor Commented:
Perhaps you could cast your currency as a cDbl first.
Also, one way to pad the beginning of the expression is by using the Space() function.

This, for example, should get you close to where you want to be.

space(10 - len(totext(CDbl ({TimeEntry.TEBillRate}),0,'####'))) + totext(CDbl ({TimeEntry.TEBillRate}),0,'####')+{Employee.EmpTitle}  

It will calculate the length of your billrate cast as a string, so if it's 85, the length of "85" is two, and it will pad with 8 leading spaces.

Hope this helps.
0
 
mlmccConnect With a Mentor Commented:
I think you can only use 0
leading 0s

Try this

 totext(CDbl(({TimeEntry.TEBillRate},0,'####'))+{Employee.EmpTitle}

mlmcc
0
 
James0628Connect With a Mentor Commented:
mlmcc's formula should work, but you need to remove/move a couple of parentheses.  The following should work:

 totext (CDbl ({TimeEntry.TEBillRate}),0,'####')+{Employee.EmpTitle}


 FWIW, the main thing in both mlmcc's and agandau's suggestions is to convert the field to a regular numeric data type (using CDbl) first.  Apparently your field is a currency data type and when converting one of those to a string, CR automatically adds a "$".  If you convert your field to a regular number first, CR won't do that.

 James
0
 
GregFromSeattleAuthor Commented:
All comments helped but agandau helped the most with the leading blanks to get it to sort properly thank you all!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 For the record, the "####" format should also give you leading spaces.  If you want more spaces, just add more #'s.

 James
0
All Courses

From novice to tech pro — start learning today.