| Author |
Message |
LearningAsIGo
Newbie
Joined: 04 Apr 2011
Online Status: Offline
Posts: 15
|
  
Topic: Field contains 3 rows of data, need only the third
Posted: 04 Apr 2011 at 10:05am |
|
First time posting, so please bare with me if I do not provide all the accurate information needed.
A database table was setup that includes a field - {B3APO_ATTRIBUTE.B1_ATTRIBUTE_VALUE}
The data inside the field is always 3 rows of data, e.g.
Department
Municipality
Subdivision
The only data I require is the the subdivision, and I only need to use it in my group footer. I do not think there are spaces between the rows, but I am not certain about this.
The subdivision is also all Caps so I know I will need to run it against ProperCase. I just have no idea how to get the subdivision data only.
Any help is greatly appreciated. Sorry if this has already been asked and answered, I did not locate it in the forum thus far.
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Apr 2011 at 10:10am |
filter your data in the select expert
{B3APO_ATTRIBUTE.B1_ATTRIBUTE_VALUE} = 'SUBDIVISION'
|
| IP Logged |
|
LearningAsIGo
Newbie
Joined: 04 Apr 2011
Online Status: Offline
Posts: 15
|
Posted: 04 Apr 2011 at 10:13am |
|
Ok, let me apologize for the mental block.
Those are the rows of data that show, but each record has a different name, there are over 100 subdivision names. They are not labeled "subdivision".
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Apr 2011 at 10:18am |
Ahhh...
You mean that there is one row of data with 3 items in text in that one field, correct?
if so ,when you place that field on a row in crystal you get it like this...
Department Name
Municipality Name
Subdivision Name
not like this...
Department Name Municipality Name Subdivision Name
Correct?
|
| IP Logged |
|
LearningAsIGo
Newbie
Joined: 04 Apr 2011
Online Status: Offline
Posts: 15
|
Posted: 04 Apr 2011 at 10:21am |
|
Exactly, and I only require the 3rd row of data, the rest is useless and I want to suppress it.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Apr 2011 at 10:26am |
OK.
they are all in the same row of data so you cannot suppress the "first 2 rows" but you can change the field to exclude them.
try making a formula field as ....
right({B3APO_ATTRIBUTE.B1_ATTRIBUTE_VALUE},len({B3APO_ATTRIBUTE.B1_ATTRIBUTE_VALUE})-instrrev({B3APO_ATTRIBUTE.B1_ATTRIBUTE_VALUE},CHR(13)))
and placing it on your row
Edited by DBlank - 04 Apr 2011 at 10:28am
|
| IP Logged |
|
LearningAsIGo
Newbie
Joined: 04 Apr 2011
Online Status: Offline
Posts: 15
|
Posted: 04 Apr 2011 at 10:36am |
|
In the group footer, I am still getting the first row, not seeing anything else.
copied this right over into a formula - right({B3APO_ATTRIBUTE.B1_ATTRIBUTE_VALUE},len({B3APO_ATTRIBUTE.B1_ATTRIBUTE_VALUE})-instrrev({B3APO_ATTRIBUTE.B1_ATTRIBUTE_VALUE},CHR(13)))
|
| IP Logged |
|
LearningAsIGo
Newbie
Joined: 04 Apr 2011
Online Status: Offline
Posts: 15
|
Posted: 04 Apr 2011 at 10:38am |
|
Here is one of the actual fields:
Black Jack
Unincorporated
MISTY HOLLOW
All I want displayed is "MISTY HOLLOW".
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Apr 2011 at 10:40am |
ignoring the gf for right now,
you made a fomrula field with the above formula.
in the detail section if you place the orioginal field next to the fomrula field do you get
Orginal Field Formula field
Black Jack MISTY HOLLOW
Unincorporated
MISTY HOLLOW
|
| IP Logged |
|
LearningAsIGo
Newbie
Joined: 04 Apr 2011
Online Status: Offline
Posts: 15
|
Posted: 04 Apr 2011 at 10:43am |
|
No, doing a side-by-side comparison, they are identical.
|
| IP Logged |
|
|
|
