Parse a Race Field Into 5 Separate Fields

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.  
carolannpAsked:
Who is Participating?
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
I already provided the code for creating 5 individual formulas, just substitute the number and description in each subsequent formula.
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Create 5 separate formulas--one for each possible race--like follows:

//@RC1
If
  Instr({table.racecode},'1') >0
Then
  'White/Caucasion'
Else
  ''
~Kurt
0
 
mlmccCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
carolannpAuthor Commented:
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
 
mlmccCommented:
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
      myArray[Index] := "American Indian/Alaska Native"
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
carolannpAuthor Commented:
No, I need 5 separate fields.
0
 
mlmccCommented:
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
      Race3 := "American Indian/Alaska Native"
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.