One field to 5 fields calculation

[ddreese]

I have a race field in my database, with a checkbox field format with the following races: White, Black, Native American, Asian, Pacific Islander, Other, Unknown

When the user selects the race, I need it to split into 5 seperate fields (these fields are being exported to a state report, which is why they all need to be seperate fields).

For instance, if the user checked off the following race selections: White, Black, Asian ... I would need it to split that selection into the following fields:

CALC_race_code_1 = White

CALC_race_code_2 = Black

CALC_race_code_3 = Asian

CALC_race_code_4 = blank

CALC_race_code_5 = blank

And actually, to complicate things slightly, each race has to be converted into these codes:

1 = black, 2 = American Indian, 4 = asian, 5 = white, 6 = other, 7 = pacific islander

So ultimately the output of those calc fields would be:

CALC_race_code_1 = 5

CALC_race_code_2 = 1

CALC_race_code_3 = 4

CALC_race_code_4 = blank

CALC_race_code_5 = blank

I assume this can be done with some sort of 'middle' function or something, but I don't have enough calculation experience to do it in the small time frame I have in the morning when no one else is in the database (I have about 30 concurrent users during the day).

Any help greatly appreciated!

[mr_vodka]

Why cant you use PatternCount to find if it has been selected and then a Case function in the calc?

CALC_race_code_1 = Case (PatternCount (CheckBoxField, "White") > 0; 5)

CALC_race_code_1 = Case (PatternCount (CheckBoxField, "Black") > 0; 1)

CALC_race_code_1 = Case (PatternCount (CheckBoxField, "Asian") > 0; 4)

[ddreese]

That should work for one field, yes. But what about the other 4? I can't re-use that formula for each field because it would always display 5 if white was selected, regardless of if it was already displayed in one of the other 5 fields.

[SlimJim]

The content of a checkbox field is a value list so you could extract the values as:

CALC_race_code_i = MiddleValues(Checkboxfield;i;1)

i running from 1 to 5. The coding can be done by a Case function. So the following should work:

CALC_race_code_i =

Let(Text = MiddleValues(Checkboxfield;i;1);

Case(

text = black; 1;

text = American Indian 2;

text = asian; 4;

text = white; 5;

text = other; 6;

text = pacific islander; 7;

""))

I'm assuming that if a checkbox is blank then the result should be blank

[ddreese]

Oooh, I think that's exactly what I'm looking for, slim jim, you read my mind! I just don't know enough about those functions to have done it on my own. Thanks a bunch, I'll try that first chance I get! :)

[SlimJim]

Sorry. I note that I have not put quotes round any of the tested text "black" etc, they do need them.