Using Case with checkbox items

[philipcaplan]

In FMPro 10, I have a field called "Staff" which displays a set of about 30 names.

On a layout, I have (using Field/Control Setup) checkboxes against these names, so the user can select any number of Staff to assign to a particular event.

I now want to create a Calculation field which concatenates the names of all the 'checked' names into one field, so I can display them as a string of text on another layout.

How do I do this?

[LaRetta]

Create a calculation (result is text) and be sure to set to Unstored in Storage Options of:

Substitute ( ValueListItems ( Get ( FileName ) ; "YourVLname" ) ; ¶ ; ", " )

This calculation can be copy/pasted directly into your new calculation and the only thing you need to change is to make YourVLname inside the quotes IDENTICAL to your actual value list name you created in your value list for the checkbox. Note that if you make a mistake in typing the value list name identically, you will NOT receive a warning or error when you leave the calculation ... it will simply not work. So be sure it is typed exactly.

[comment]

Ahem,

a Calculation field which concatenates the names of all the 'checked' names into one field

They already are in one field, separated by a carriage return. If you want another separator, use the Substitute() function as shown above - but use it on the field, not on the value list.

[LaRetta]

Of course! I stand corrected, thanks!

UPDATE: So the calculation would be:

Substitute ( yourCheckboxField ; ¶ ; ", " )

Edited May 17, 2009 by Guest
Added update

[philipcaplan]

That works! Thank you both, LaRetta and Comment.

Using the formula:

Substitute ( Staff ; ¶ ; ", " )

where Staff is the fieldname with the checkboxes, I correctly get just the checked names, like this:

LIH (Litchfield, Helen), OND (O'Neill, Dermot)

Unfortunately I'm now told that I also have to strip the people's names in parentheses from the resultant calculation, plus any spaces between the 3-letters and the opening parenthesis, leaving just the 3-letter codes separated by commas, like this...

LIH, OND

Many thanks if you would be kind enough to tell me the full calculation that will do this?

Thanks again, Philip

[comment]

In the Staff table, do you have separate fields for the code, first name and last name?

[philipcaplan]

Staff isn't a table, it's a field.

No, it's a simple Text field; the individual members of staff appear only in a Value List, where they're in the form:

XXX (name)

I was expecting there to be a calculation using Pos and the ( and ) to strip out the (name)'s.

[comment]

A calculation like that, while possible, would be unduly complex - because the number of selected staff can vary, and the calc needs to process each one individually.

I'd suggest you solve the real issue here instead: define a table of Staff, with separate fields for Code, FirstName and LastName, and also a calculation field cFullName (result is Text) =

LastName & ", " & FirstName

Then change your value list to use values from the Staff::Code, and to also show values from the cFullName field.

There is also another issue here with these codes (what will you do if you hire a Peter Carey in addition to Phillip Caplan?), but I'll leave that for later.