Storing a value list as ::: delimited

[Fearless545]

I have a value list that uses "custom values" (hard coded values, not values from another table) that I use to track the source of something. The source could be from multiple places, so I am using a "checkbox set" so multiple sources can be selected.

For example, the source list is:

Source1

Source2

Source3

When I select 2 values, the data is stored as:

Source1Source2

Is there any way I can select the delimiter? I need it stored:

Source1::Source2

I don't even HAVE to have it stored this way, it could be done when I export it to a coma delimited file.....the software I am importing this data to requires it to be ::: delimited.

I am very new to FMP, but have a lot of MYSQL experience from years ago.

Thanks!

-Owen

[comment]

You could define a calculation field (result is Text) =

Substitute ( CheckboxField ; ¶ ; "::" )

and export it instead of CheckboxField.

[Fearless545]

That worked, thanks! Now I am running into a related problem

If I have:

Source1

Source2

Source3

and have checked all 3 boxes, so I have (in the original field, not the calculation field) all 3 sources listed in the field as Source1Source2Source3

If I later remove Source3 from the list and create Source4, I can't find any way to remove Source3 from the fields it's been saved to....then I start to get warning messages because when it tries to validate the field, it fails because Source3 is now invalid.

I see in the future needing to remove sources from the list and updating the Db to have those old sources removed....I don't want to blindly blank out the source field because the other ones that are selected will still be valid.

Anyone have any suggestions?

Shouldn't there be a way to remove "source3" when I remove it from the value list?

[comment]

A value list is nothing but a means to facilitate entry into a field, and displaying the entered values in a particular way. Modifying the value list has no effect on actual field contents.

If you remove an item from a value list AND you also want to remove it from all the records that contain it, you must modify the actual field contents, e.g. by replacing the field contents with a calculated result =

Let ( [

xcl = Substitute ( ¶ & CheckboxField & ¶ ; ¶ & "YourItem" & ¶ ; ¶ )

] ;

Middle ( xcl ; 2 ; Length ( xcl ) - 2 )

)

Notes:

1. This will work faster if you find the records that contain the item first;

2. Replace Field Contents has no undo; make sure you have a backup!

3. This is not something you should be doing often; if you find that you do, you may have a deeper issue with your data model.