4 Replies Latest reply on Jul 14, 2014 10:22 AM by caterinap

    Remove duplicates in each record of a text field

    caterinap

      Title

      Remove duplicates in each record of a text field

      Post

           Hello,

           My database has a text field. Some of the characters (only 4 possible: a,b,c,d) can be repeated multiple times in a given record. I would like to remove duplicate characters from each record of this field (and if possible sort characters, but this is not essential) as follows:

           my field: abcd / abbc / cbdc / bba / cabcb    into-->     my field: abcd / abc / bcd / ab / abc

           It seems that the custom functions  "UniqueValues" and "SortValues" in briandunning.com do that but unfortunately I don't have Filemaker Pro Advanced. I have Filemaker Pro 12.0v3. Is there a way to do that in a script?

           Thank you!

        • 1. Re: Remove duplicates in each record of a text field
          philmodjunk

               Is

               abcd / abbc / cbdc / bba / cabcb

               the data found in a single field of a single record or is the data shown the data for 5 different records with the / serving as a kind of "record separator"?

               My point being, that I see a way to get what you want fairly simply if each "/" delimited pattern was a value in a different record...

          • 2. Re: Remove duplicates in each record of a text field
            caterinap

                 Apologies, my typing was not clear.

                 abcd / abbc / cbdc / bba / cabcb is the data for 5 different records ("/" is indeed a kind of "record separator")

            • 3. Re: Remove duplicates in each record of a text field
              philmodjunk

                   Then we are in business.

                   Set up a calculation field that separates each character from the original field with a return to produce

                   a¶b¶c¶d / a¶b¶b¶c / c¶b¶d¶c

                   We'll call this field "cLetterList".

                   it can be defined as:

                    List ( Middle ( YourField ; 1 ; 1 ) ; Middle ( YourField ; 2 ; 1 ) ; Middle ( YourField ; 3 ; 1 ) ; YourField ( String ; 4 ; 1 ) ; YourField ( String ; 5 ; 1 ) )

                   Use as many calls to the Middle function as you need to handle the longest possible string. (If you had FileMaker Advanced, I'd recommend a custom function as such a function can be set up to better handle a flexible number of characters.)

                   Define a related table of records, Alphabet, with a single text field. Enter one letter of the alphabet into this text field in each record to get a total of 26 related records. We'll call that single text field "Letter".

                   Define this relationship:

                   YourTable::cLetterList = Alphabet::Letter

                   You can create the records of Alphabet in alphabetical order, or you can double click this relationship line and specify an ascending sort order based on Letter for Alphabet.

                   Then this calculation: List ( Alphabet::Letter ) will omit all duplicate letters and list them in alphabetical order.

                   You can use a looping script with set field or a Replace field Contents operation where the above calculation is used to update your original field in order to remove duplicates and alphabetize.

              • 4. Re: Remove duplicates in each record of a text field
                caterinap

                     Thank you very much, it worked perfectly!