4 Replies Latest reply on Jun 5, 2017 7:02 AM by coherentkris

    Remove duplicate values from a single field

    Hoochimagoo

      Hi, I have multiple text fields that allow checkbox value list entries of text strings of up to 10 words.  These multiple lists are then collected (excuse my poor terminology) into a single text field separated by a ",".  Some of the value text strings are duplicates as they have come from different value lists.  I was wanting to remove the duplicate text strings from this single field.  Could someone please direct me to a fix for this?

       

      I came across this from another discussion:  FileMaker Custom Function:RemoveDuplicates ( Text )

       

      But was not sure if this would work for a text string or simply a single word.

       

      Thank you. 

        • 1. Re: Remove duplicate values from a single field
          coherentkris

          If your input is a csv list then Substitute(text;",";¶) will turn the list into a proper value list for the CF you mentioned.

          For example...

          If your field contains blue,red,yellow,blue,green

          Substitute will produce blue¶red¶yellow¶blue¶green

           

          blue

          red

          yellow

          blue

          green

           

          Applying the CF to the field will produce either blue¶red¶yellow¶green.

           

          Warning... the CF in question appears to be case sensitive i.e blue and Blue are not equal so..

          blue,Blue,red,yellow,blue,green will reduce to blue¶Blue¶red¶yellow¶green

          1 of 1 people found this helpful
          • 2. Re: Remove duplicate values from a single field
            Hoochimagoo

            Sorry I should have explained better.  The final field list is generated from multiple other fields all from within FileMaker.  There is no CSV input.  The issue is that some of the feeding fields have the same entries in there value lists so the final field will have duplicate entries.  Often though these will be strings of text of up to 10 words.  Is that clear as mud?

            • 3. Re: Remove duplicate values from a single field
              erolst

              Hoochimagoo wrote:

               

              I came across this from another discussion: FileMaker Custom Function:RemoveDuplicates ( Text )

               

              But was not sure if this would work for a text string or simply a single word.

              Both, neither – "it depends".

               

              In short, that function removes duplicate values from a CR-delimited list, where a value can be a single character, a word, or a phrase. Since a checkbox control generates such lists, this is what you want.

               

              btw, FM 16 (finally) offers a built-in UniqueValues() function.

              1 of 1 people found this helpful
              • 4. Re: Remove duplicate values from a single field
                coherentkris

                When you wrote "into a single text field separated by a ","" you wrote the textbook definition of a CSV list. It does not matter where the values come from.. 1 field, many fields, related records whatever.. CSV does NOT always mean a .csv file.

                A csv list parsed into a proper FMP value list and fed into the CF you mentioned will results as stated.

                As Erolst stated you don't even need the CF in v16 of FM

                1 of 1 people found this helpful