Jump to content
Sign in to follow this  
philipcaplan

Using Case with checkbox items

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Of course! I stand corrected, thanks!

UPDATE: So the calculation would be:

Substitute ( yourCheckboxField ; ¶ ; ", " )

Edited by Guest
Added update

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    Will Xu 
×

Important Information

By using this site, you agree to our Terms of Use.