[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
8
Medium Priority
?
312 Views
Last Modified: 2012-05-11
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
Comment
Question by:carolannp
  • 3
  • 3
  • 2
8 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
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

by:mlmcc
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

by:carolannp
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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 101

Expert Comment

by:mlmcc
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
      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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
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

by:carolannp
ID: 35740905
No, I need 5 separate fields.
0
 
LVL 101

Expert Comment

by:mlmcc
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
      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
 
LVL 26

Accepted Solution

by:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question