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!

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)

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.

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

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! :)

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