Print Page | Close Window

Convert a text field to a numeric value

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12817
Printed Date: 23 May 2024 at 5:17pm


Topic: Convert a text field to a numeric value
Posted By: MicheleM
Subject: Convert a text field to a numeric value
Date Posted: 05 Apr 2011 at 11:02am
I have this large database created by our IT department, one of criteria I need to sort by is "site specific locations" and he has made that a text field.  When I put the field into my report it lists out the clinic names, however, I'm not able to sort or group this field... my IT guys say it is a Crystal problem with text.  So now I'm wondering if it is possible to create a formula to convert the text to a numeric value, that I could sort by.  We have several different clinic names... and it shows up in the report as ABC Grand Rapids, ABC Holland  I have tried a couple of different things and just can't seem to get it right.  Is this possible?


Replies:
Posted By: DBlank
Date Posted: 05 Apr 2011 at 11:20am
Did your IT guy clarfy the "Crystal problem'? I have never had an issue sorting a text field. What CR version are you using?

Posted By: MicheleM
Date Posted: 05 Apr 2011 at 11:33am
11.0 it shows up to bring into a report, but not when you want to sort or group.

Posted By: DBlank
Date Posted: 05 Apr 2011 at 11:36am
is it a blob?
What is your source type (SQL?) and field type at the source?

Posted By: MicheleM
Date Posted: 05 Apr 2011 at 11:48am
Lime survey/SQL and he said text.

Posted By: DBlank
Date Posted: 05 Apr 2011 at 11:49am
if you group on the field does it group correctly and does it sort the groups alpha?

Posted By: MicheleM
Date Posted: 05 Apr 2011 at 11:54am
No, it is in the fields to bring over into the report, but does not show up as a field to do sorting, grouping or alpha. He said it was MySQL.

Posted By: DBlank
Date Posted: 05 Apr 2011 at 11:58am
can you make a formula field that references it like
left(table.field,25)
then sort or group that formula field?

Posted By: MicheleM
Date Posted: 06 Apr 2011 at 2:09am
No, just all dumped into the same table.   When I go into the formula editor this is what shows up for that field when I insert it to create a formula:
 
{lime_survey_99946.99946X10X1298}
 
and this field represents one of several clinics that they can choose from, either by clicking on the location or picking it from a drop down box in the survey itself.

Posted By: DBlank
Date Posted: 06 Apr 2011 at 3:49am
out of ideas here...
Anyone else?

Posted By: Keikoku
Date Posted: 06 Apr 2011 at 5:23am
No, just all dumped into the same table.   When I go into the formula editor this is what shows up for that field when I insert it to create a formula:

{lime_survey_99946.99946X10X1298}

and this field represents one of several clinics that they can choose from, either by clicking on the location or picking it from a drop down box in the survey itself.


If you create a formula called for example "name" and then simply wrote


{lime_survey_99946.99946X10X1298}


save and then put it on the report, can you at least group on it?

Posted By: MicheleM
Date Posted: 06 Apr 2011 at 5:28am
Oh my gosh, it was that simple.  You guys ROCK!  I absolutely can sort and group.
 
Thanks so much!!!


Print Page | Close Window