2 Replies Latest reply on Feb 17, 2013 4:36 PM by deathrobot

    Help with sticking value list values

    deathrobot

      Title

      Help with sticking value list values

      Post

           I am running a music libary and created a simple Filemaker database to help in managing of each song's keywords. I have a database with one record per song. There is one field called "Keywords". On the record layout, I have many iterations of that field, each using values from a different Value List (e.g. one for "style", one for "tempo", one for "instrumentation", etc.). Keywords are selected by checkboxes. I also have one calculation field that combines all the keywords in a comma separated list:

           Substitute ( Substitute ( Keywords ; "/" ; ", " ) ; "¶" ; ", " )

           The problem I am having is that if I alter any values in one of the value lists, where that value was already checked on a record, it does not get removed from the calculation field, and I cannot figure out a way to get rid of it. For example, say I have one value list consisting of:

           keyword 1
           keyword 2/keyword 3 (some values consist of more than one keyword; they get parsed by the calculation field)

           and I have "keyword 2/keyword 3" checked on a record. If I later decide I should not have combined those keywords, I edit my value list to be:

           keyword 1
           keyword 2
           keyword 3

           so that I can keep keyword 2 checked, but uncheck keyword 3. When I do, my calculation field shows the original keyword 2 and keyword 3 plus a duplicate keyword 2. If I uncheck keyword 2 - in fact, even if I uncheck EVERYTHING on the current record - the original keyword 2 and keyword 3 will still show in the calculation box. It's like the exist but are hidden. I've turned off indexing of the keyword field and keep the calculation unstored, but nothing works. 
            
           Any ideas?
            
           Thanks,
           Michael

        • 1. Re: Help with sticking value list values
          philmodjunk

               As you have discovered, altering the values defined for a value list does not change the data stored in any field formatted with that value list. And checkboxes and radio buttons will hide any data in the field that does not match one of the displayed values.

               You will need to set up a system that changes the values in the field before you make a change to the values in the value list.

               Example:

               If you want to change a check box value from "Apple" to "Macintosh". You can enter find mode and select "Apple", then perform the find to find all records where this field contains the value "apple".

               You can then use replace field contents to replace the value "Apple" with the value "Macintosh" before finally updating your value list to use Macintosh in place of Apple.

               Here's a calculation you can adapt to use with Replace FIeld Contents to replace the value "Apple" with the value "Macintosh":

               Let ( [ TheList = Substitute (  ¶ & YourTable::YourCheckboxFIeld & ¶ ; ¶ & "Apple" & ¶ ; ¶ ) ;   // Remove "Apple" value
                          NewList = Middle ( TheList ; 2 ; Length ( TheList ) - 2 )  // Remove extra returns
                        ]
                        List ( NewList ; "Macintosh" ) // Add "Macintosh" value
                      )

               Replace field contents can be performed manually or in a script.

          • 2. Re: Help with sticking value list values
            deathrobot

                 Hi Phil,

                 Thanks very much! That solverd the situation.

                 Michael