[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Parse a Race Field Into 5 Separate Fields

Posted on 2011-05-11
Medium Priority
312 Views
I have a Race Code Field, which is a multiple choice drop-down field on a menu. This Race Code field is storead as the following: 1&2&3&4&5.  1=White/Caucasian; 2=Black/African American; 3=American Indian/Alaska Native; 4=Asian; 5=Native Hawaiian/Other Pacific Islander.  What I need to do, is separate this field into 5 separate fields and I need to enter the actual value of this field into the new fields, not the code itself.  Also, the catch is, that not all 5 races are selected everytime and they could be a combination of the numbered sequence above.
0
Question by:carolannp
• 3
• 3
• 2

LVL 26

Expert Comment

ID: 35739241
Create 5 separate formulas--one for each possible race--like follows:

//@RC1
If
Instr({table.racecode},'1') >0
Then
'White/Caucasion'
Else
''
~Kurt
0

LVL 101

Expert Comment

ID: 35739246
You can use the split function to split the string into its components

Local StringVar myArray;
myArray := Split({YourField});

Can you show some sample data?
Will there always be 5 values?

mlmcc
0

Author Comment

ID: 35739964
Here are some other sample data examples: 2&4&5; 1&3; 1; etc..  The main issue is there will not always be 5 values?  Any other suggestions?
0

LVL 101

Expert Comment

ID: 35740480
The split will work regardless of the number of values.

How do you want the result to be displayed?

If you want it as

White/Caucasian, Black/African American

Try

Local NumberVar Index;
Local StringVar myArray;
myArray := Split({YourField},"&");

For Index := 1 to UBound(myArray) do
If myArray[Index] = "White/Caucasian" then
myArray[Index] := "White/Caucasian"
Else if myArray[Index] = "Black/African American" then
myArray[Index] := "Black/African American"
Else if myArray[Index] = "American Indian/Alaska Native" then
Else if myArray[Index] = "Asian" then
myArray[Index] := "Asian"
Else if myArray[Index] = "Native Hawaiian/Other Pacific Islander" then
myArray[Index] := "Native Hawaiian/Other Pacific Islander"
Else
myArray[Index] := "Data Error"

Join(myArray,", ")

mlmcc

0

LVL 26

Expert Comment

ID: 35740621
What you said:

What I need to do, is separate this field into 5 separate fields

"Fields" are individual values on a page, whether they be separate database fields, formula fields, etc...  By using 5 different formulas, one for each race code, you'll end up with 5 unique fields you can place on the page.  If a specific race code isn't represented then that formula will return nothing.

mlmcc's formula splits out the various possible values and returns an array of descriptions within a single field.  Is this what you actually want - to substitute a list of descriptions for the list of values within a single field?

~Kurt
0

Author Comment

ID: 35740905
No, I need 5 separate fields.
0

LVL 101

Expert Comment

ID: 35740958
You can't create "field" however you can create formulas to display the individual pieces.

What is the purpose of the "fields"?

Is the order of the values important?

WhilePrintingRecords
Global StringVar Race1;
Global StringVar Race2;
Global StringVar Race3;
Global StringVar Race4;
Global StringVar Race5;

Local NumberVar Index;
Local StringVar myArray;
myArray := Split({YourField},"&");
Race1 := "";
Race2 := "";
Race3 := "";
Race4 := "";
Race5 := "";

For Index := 1 to UBound(myArray) do
If myArray[Index] = "White/Caucasian" then
Race1 := "White/Caucasian"
Else if myArray[Index] = "Black/African American" then
Race2 := "Black/African American"
Else if myArray[Index] = "American Indian/Alaska Native" then
Else if myArray[Index] = "Asian" then
Race4  := "Asian"
Else if myArray[Index] = "Native Hawaiian/Other Pacific Islander" then
Race5  := "Native Hawaiian/Other Pacific Islander"

You can then treat them as fields with formulas

WhilePrintingRecords;
Global StringVar Race1;
Race1

Create 1 formula for each

mlmcc

0

LVL 26

Accepted Solution

Kurt Reinhardt earned 2000 total points
ID: 35741249
I already provided the code for creating 5 individual formulas, just substitute the number and description in each subsequent formula.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirementsâ€¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyâ€¦
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the adminâ€¦
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
###### Suggested Courses
Course of the Month19 days, 3 hours left to enroll