Value List & text version of VL

[Raizel]

field = "Music Format"

Value List (checkbox)
Album
Cassette
CD
EP
Single
iMusic
Same field done in text format (temp). However, when the checkbox VL is empty, content still exists in text field version.

My aim is getting my calculation to work. Field = "zFormat" used in my header.
Case (
MUSIC FORMAT = "ALBUM"; "ALBUM";
MUSIC FORMAT = "CASSETTE"; "CASSETTE";
MUSIC FORMAT = "CD"; "CD";
MUSIC FORMAT = "EP"; "EP";
MUSIC FORMAT = "SINGLE"; "SINGLE";
MUSIC FORMAT = "iMUSIC"; "iMUSIC";

MUSIC FORMAT = "iMUSIC"  and MUSIC FORMAT = "CD"; "CD + iMUSIC"
MUSIC FORMAT = "ALBUM"  and MUSIC FORMAT = "iMUSIC"; "ALBUM + iMUSIC"
)

[comment]

If I follow this correctly (??) you want the zFormat field to be a Calculation field (result is Text) =

Substitute ( Music Format ; ¶ ; " + " )

This will return a list of the values that are checked in the Music Format field, separated by +.

Note that values will be listed in the order in which they were checked. If you want to follow the order of the value list, there is more work to do.

 

I am afraid I couldn't understand this part:

44 minutes ago, Raizel said:

Same field done in text format (temp). However, when the checkbox VL is empty, content still exists in text field version.

 

P.S. This test:

MUSIC FORMAT = "iMUSIC"  and MUSIC FORMAT = "CD"

will never be true. If A is equal to B then it cannot be also equal to C. To test if both values are checked (possibly alongside others) you can use:

ValueCount ( FilterValues ( "CD¶iMusic" ; Music Format ) ) = 2

 

Edited May 14, 2021 by comment

[Raizel]

The text version of my VL had stuff I didn't see so thought it wasn't working. Don't need it now that my zFormat field works.

I replaced my calculation with your Substitute and got the results I was trying to achieve. Doesn't matter the order, I know it's based upon order checked. Thanks.

[Raizel]

Step back. The Substitute is working under all conditions so I'm getting duplicate Format where only one is checked. I'm only wanting Substitute when two Formats are checked.

Where Format has "Single" checked (one occurrence) then zFormat will equal """ because Format will be used.

Where Format has "Single" and "iMusic" checked (two occurrences),  zFormat will equal "SIngle + CD" not "Single + Single + CD".

[comment]

3 hours ago, Raizel said:

I'm getting duplicate Format where only one is checked

I am unable to reproduce the problem. Does the attached file work for you as expected?

Checks2Txt.fmp12

[Raizel]

Managed to "cear" those with two checkboxes and redo correctly. Thanks.