Using Case (or If?) to Replace Field Content in a Checkbox Set after updating the Value List

[ArchFile]

Hello!

I have a list of attributes in a field that are displayed as a checkbox set. Thus, multiple selections can be made for this field.

I have updated my value list for this checkbox set in order to refine it for my current needs. The value list change 1) collapses the value list to provide fewer options that someone could select (eliminates vague values leaving only the more precise ones) and 2) updates the wording of the value list to eliminate confusion.

I now want to update the responses to this field by using Replace Field Contents: that is, replace entries that were recorded using the old value list with a corresponding value from the new value list. However, since this is a "check all that apply" checkbox set, I'm not sure what to do as there will be multiple values to find/replace.

By replacing the field content, I also want to delete any existing content. I aim to design a Replace calculation, preferably using Case, that uses verbatim the old value list (test 1) and tells it what to replace each value with (result 1) which is verbatim to the new value list, which appears as a checkbox set (note: adding "other" was never an option).

What I have done does not work: it isn't deleting the old data (I copied the field in duplicate and displayed it as an edit box so I know the values are still there) nor checking any of my checkboxes using the new value list, so I am doing this all wrong. I don't have much experience using Case.

Here's my unsuccessful trial:

Case(

DecorationTechnique = “Incised”, “Incised leather-hard paste”,

DecorationTechnique = “Leather-hard paste”, “Incised leather-hard paste”,

DecorationTechnique = “Wet paste”, “Incised wet paste”,

DecorationTechnique = “Etched”, “Etched/Engraved”,

DecorationTechnique = “Painted”, “Painted - Positive”,

DecorationTechnique = “Painted Positively”, “Painted - Positive”,

DecorationTechnique = “Painted Negatively”, “Painted - Negative”,

DecorationTechnique = “Molded”, “Mold-made”,

DecorationTechnique = “Modeled”, “Modeled (Hand-sculpted)”,

DecorationTechnique)

 

Old value list:

Incised

Leather-hard paste

Wet paste

Etched

Painted

Painted Positively

Painted Negatively

Molded

Modeled

 

New Value list:

Incised leather-hard paste

Incised wet paste

Etched/Engraved

Painted - Positive

Painted - Negative

Mold-made

Modeled (Hand-sculpted)

 

Desired result: say, if one my records contains three attributes in the field -- “Incised”, “Leather-hard paste” and "Molded” -- and I want to replace these attributes with “Incised leather-hard paste” and “Mold-made” (which would successfully collapse the first two attributes (which were previous selected using the old value list) into one attribute (that is present in the new value list) and keep the third attribute, but updates its wording.

Advice? Should I be using "If" not "Case?"

Thank you!!

 

[Ben Kreunen]

Neither. Substitute is what you need here as all you're doing is multiple substitutions.

Substitute(DecorationTechnique;[“Incised”;“Incised leather-hard paste”];[“Leather-hard paste” ; “Incised leather-hard paste”]; ...etc )

 

[comment]

I would suggest you try it this way:

Let ( [
numerical = Substitute ( YourField ; 
[ "Incised" ; "1" ] ; 
[ "Leather-hard paste" ; "2" ] ; 
[ "Wet paste" ; "3" ] ; 
[ "Etched" ; "4" ] ; 
[ "Painted Positively" ; "6" ] ; 
[ "Painted Negatively" ; "7" ] ; 
[ "Painted" ; "5" ] ; 
[ "Molded" ; "8" ] ; 
[ "Modeled" ; "9" ] ) 
] ;
Substitute ( numerical ; 
[ "1" ; "Incised leather-hard paste" ] ; 
[ "2" ; "Incised leather-hard paste" ] ; 
[ "3" ; "Incised wet paste" ] ; 
[ "4" ; "Etched/Engraved" ] ; 
[ "5" ; "Painted - Positive" ] ; 
[ "6" ; "Painted - Positive" ] ; 
[ "7" ; "Painted - Negative" ] ; 
[ "8" ; "Mold-made" ] ; 
[ "9" ; "Modeled (Hand-sculpted)" ] ) 
)

Note the order of substitutions - "Painted Positively" before "Painted".

If your field contains both "Incised" and "Leather-hard paste" for example, you may end up with duplicate values. If that matters, add another step of filtering the values against your value list items.

Note also that Replace Field Contents has no undo; test your formula in a calculation field first, and make sure you have a backup.

 

[ArchFile]

Just checking back in to say the straight up "Substitute" command worked perfectly, thank you both so much!! Easy and straightforward.