2 Replies Latest reply on Feb 4, 2014 10:39 AM by maser

    FM 12 (or 13):  How to globally replace multiple checkbox set values in a field record?

    maser

      Title

      FM 12 (or 13):  How to globally replace multiple checkbox set values in a field record?

      Post

           A field is set up as a "checkbox set" and gets field value list items from a different table field.  Example that in any given record, the user will be presented with these checkbox items:

           A

           B

           C

           D

           And, in any given record -- any number of checkbox values can be selected:  A, AC, ABD, etc...

            

           User of the database would like to *rename* the checkbox values:

           A --> E

           B --> F

           C --> G

           D --> H

            

           But *retain* the fact that the box was originally checked as part of the rename (i.e., think each checkbox value is a user names where the last name of the user has changed through marriage..   So the end result from the above example of 3 records would now read E, EG, EFH for the same 3 records.

            

           The brute force method would be to *add* E, F, G and H as items in the field and then go through each record in the table and manually select the corresponding new boxes and then delete A-D, but that seems like it would be harder than it should be.

           (And, of course, just *renaming* A --> E in the original field table removes the checkbox on "A", so that's out -- I would have thought that might have worked, but it does not in FM 13)

            

           Any suggestion for a better way to do this?  

        • 1. Re: FM 12 (or 13):  How to globally replace multiple checkbox set values in a field record?
          philmodjunk

               You could use Replace Field Contents with the Calculation option once for each value (in your example, that's 8 times, 4 times to add new values, 4 times to remove old values ) to make this update. Once the update is done, then you can update the values table to match.

               Replace Field Contents [no dialog; CheckBoxField ; //Put one of the calculations shown below here]

               //Adds new value E in every record that has A
               If ( Not IsEmpty ( FilterValues ( CheckBoxField  ; "A" ) ) ; List ( CheckBoxField ; "E" ) ; CheckboxField )

               //Removes value A from every record in which A has been selected:

               Let ( TheList = Substitute ( ¶ & CheckBoxField & ¶ ; ¶ & "A" & ¶ ; ¶ ) ;
                        Middle ( TheList ; 2 ; Length ( TheList ) - 2 )
                      )

               Before using Replace field contents, save a back up copy of your file. It can modify every record in your table and cannot be undone.

               Before the first Replace Field Contents, do a Show All records to pull all records into your found set so that the Replace Field Contents action will modify every record in your table.

               Both of these calculations exploit the fact that values selected in check box value sets are stored as text separated by return characters.

          • 2. Re: FM 12 (or 13):  How to globally replace multiple checkbox set values in a field record?
            maser

                 Awesome!  Thanks.  This worked perfectly!