3 Replies Latest reply on Nov 29, 2013 7:30 AM by CoraWoolsey

    Removing duplicate words from a field?



      Removing duplicate words from a field?


      Hey guys,


      I have a question about how to remove duplicate word from a field.  What id did, is I have a DB where I  was creating descriptions, for printer parts that had calculation fields to get the printer names that followed the phrase "For use in:"  all of the word after that are what I am using for yhe keywords.  What I would like to accomplish though is remove all of the additional duplicated words that are in that field (printer may show multiple times, HP, lexmark, and so on) I created a 2 field DB and imported them in SKU_Item and Keywords.  All of the words are separated by 1 space. Any sugestions wiould be helpfull.

        • 1. Re: Removing duplicate words from a field?

          Define a calculation field, cWordList, as:

          Substitute ( KeyWords ; " " ; ¶ )

          Add a serial number field to this table, PrimaryKey, so that each record is uniquely identified.

          In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Add it to your relationships like this:

          YourTable::PrimaryKey = YourTable 2::PrimaryKey

          Go to Manage | value lists and define a value list that lists values from YourTable 2::cWordList. Select "Include only related values, starting from YourTable". This now produces a value list of all keywords in the field with all duplicates removed. The relationship limits the list to only the current record so each record will have its own list.

          Now this calculation:

          valueListItems ( Get ( Filename ) ; "YourvaluelistnameInQuotes" )

          Will return the list of keywords with duplicates removed.

          Set Field [YourTable::Keywords ; valueListItems ( Get ( Filename ) ; "YourvaluelistnameInQuotes" ) ]

          will thus filter out the duplicate words.

          Use this version:

          Set Field [YourTable::Keywords ; Substitue ( valueListItems ( Get ( Filename ) ; "YourvaluelistnameInQuotes" ) ; ¶ ; " " ) ]

          to keep the list as a list of words separated by spaces instead of returns. (The words will also be rearranged into ascending alphabetical order.)

          • 2. Re: Removing duplicate words from a field?

            Thanks again PhilMod,


            Works Like a charm.  I've been learning a ton over the last few months as I was headed in the right direction with this, but didn'd quite have the finishing touches.  I got all the way up to the valueListitem calculation and got stuck, before I looked here to see if there was a reply.

            • 3. Re: Removing duplicate words from a field?

                   I am trying to do the same thing, but I don't appear to have the Set Field option in my Specify Calculation dialogue window. I am working with Filemaker Pro 10 basic version. First, does my version not have this feature? and second, is there another way I can achieve the same goal?