| Author |
Message |
MicheleM
Newbie
Joined: 05 Apr 2011
Location: United States
Online Status: Offline
Posts: 26
|
   Topic: Convert a text field to a numeric value
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?
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
|
|
IP Logged |
|
MicheleM
Newbie
Joined: 05 Apr 2011
Location: United States
Online Status: Offline
Posts: 26
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Apr 2011 at 11:36am |
is it a blob?
What is your source type (SQL?) and field type at the source?
|
|
IP Logged |
|
MicheleM
Newbie
Joined: 05 Apr 2011
Location: United States
Online Status: Offline
Posts: 26
|
Posted: 05 Apr 2011 at 11:48am |
|
Lime survey/SQL and he said text.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Apr 2011 at 11:49am |
|
if you group on the field does it group correctly and does it sort the groups alpha?
|
|
IP Logged |
|
MicheleM
Newbie
Joined: 05 Apr 2011
Location: United States
Online Status: Offline
Posts: 26
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
Edited by DBlank - 05 Apr 2011 at 11:58am
|
|
IP Logged |
|
MicheleM
Newbie
Joined: 05 Apr 2011
Location: United States
Online Status: Offline
Posts: 26
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Apr 2011 at 3:49am |
out of ideas here...
Anyone else?
|
|
IP Logged |
|
|
|
