comma separated value list from checkbox set

[matt03wfu]

A pre-emptive thank you to the great contributors that make up this forum!

1) I am creating a music database, with one field being "Format" in which I have music release format values listed as a checkbox set. These values include, for example: 7", 2x7", 12", 2x12", 3x12", CD, 2CD, 3CD.

2) I want to create a separate calculation field that creates a simple comma-separated list of the checked values, in the same order that the values are listed in the checkbox set. (example: if 7", 12", and CD are checked, they appear in that order)

3) I've tried using calculations that I've found on other forums, but the fact my values have quotation marks integrated became problematic (the calculations also had quotations). Even when I dropped the quotations, I was running into trouble with my field displaying values that weren't necessarily checked in the checkbox set field. For example, if 2x7 and 2CD were the only values checked, it would display 7, 2x7, CD, 2xCD even though 7 and CD hadn't been checked.

Could anyone suggest a calculation (or another method) that would achieve the field with comma-separated list that I am aiming for?

Thanks kindly for your time!

[Vaughan]

There are a couple of issues here.

One is that a checkbox set only displays values that are in the value list. There can be other values in the field but FMP has no way to display them since there are no checkboxes it can use. This often happens when values are removed from the value list but they have already been added into some records. This may explain why the field has some values that may not have been displaying as checkboxes.

As for creating comma separated values; it should not be hard. Try this:

Substitute( checkboxfield ; "P" ; ", " )

... where P is the paragraph symbol.

If you expect to be able to export the data in this field as a csv file, then you have a problem. The data cannot contain characters that are reserved for delimiters: csv uses commas, returns and quotes. Apart from removing the quotes the only other option is to use a different export format like tab or html.

[comment]

2) I want to create a separate calculation field that creates a simple comma-separated list of the checked values, in the same order that the values are listed in the checkbox set.

Try =

Substitute ( 

FilterValues ( ValueListItems ( Get (FileName) ; "YourValueList" ) ; CheckboxField ) ;

¶ ; ", " )

Note that only values listed in "YourValueList" will be included. If CheckboxField contains other values, this will "hide" the problem.

[matt03wfu]

Thank you very much, this achieved exactly what I needed and I am grateful!

Try =

Substitute ( 

FilterValues ( ValueListItems ( Get (FileName) ; "YourValueList" ) ; CheckboxField ) ;

¶ ; ", " )

Note that only values listed in "YourValueList" will be included. If CheckboxField contains other values, this will "hide" the problem.