5 Replies Latest reply on Jul 10, 2009 5:45 PM by etripoli

    Eliminate Multiple Mention of Same Word in Field

    jcarey

      Title

      Eliminate Multiple Mention of Same Word in Field

      Post

       Hi Everybody_

       

      Working on FMP 8.5 

       

      We have individual items available in many different colors that we need designated into MASTER COLOR CATEGORIES for our web site.

       

      For example:

      PRODUCTS AVAILABLE IN:   Cardinal Red, Forest Green, Kelly Green, Navy Blue, Royal Blue, Bubblegum Pink, Texas Orange, White

      We have created a calculation to translate those each of those names into one field --> MASTER COLOR CATEGORIES.   The above data would return:   

      MASTER COLORS CATEGORIES: Red, Green, Green, Blue, Blue, Pink, Orange, White

      Problem: Our web site software rejects the above data because of multiple mentions of the same word.  

       

      We need a script or calculation to eliminate duplicate mentions of the same word in a field and return MASTER COLOR CATEGORIES like this.

       

      MASTER COLORS CATEGORIES NEW: Red, Green, Blue, Pink, Orange, White

       

      Any ideas would be welcome.

       

       

       

      Thanks

        • 1. Re: Eliminate Multiple Mention of Same Word in Field
          etripoli
            

          Custom function, just provide it a Return-separated list:

          Name: Distinct

          Variables: Values

           

          Case(
              not IsEmpty( Values );
                  Let (
                      [
                          FirstValue = LeftValues ( Values; 1 ) ;
                          RestOfList = RightValues ( Values; ValueCount ( Values ) - 1 ) ;
                      ] ;
                      If (
                          IsEmpty ( RestOfList ) ;
                          Substitute( FirstValue; "¶"; "" ) ;
                          If (
                              PatternCount ( RestOfList; FirstValue ) ;
                              Distinct ( RestOfList ) ;
                              FirstValue & Distinct ( RestOfList )
                          )
                      )
                  )
          )










          • 2. Re: Eliminate Multiple Mention of Same Word in Field
            comment_1
              

            jcarey wrote:

            We have created a calculation to translate those each of those names into one field


             It would be useful to know how you translated "Cardinal Red" into "Red", for example. Do you have a list of master colors?


            • 3. Re: Eliminate Multiple Mention of Same Word in Field
              jcarey
                

              here are our ultimate master color categories

               

              black

              blue

              brown

              gold

              green

              grey

              neutral

              no_color

              orange

              other_color

              pink

              purple

              red

              silver

              white

              yellow

               

              I am arriving at these colors using a simple substitute calculation where I give it the original colors names.

               

              Substitute ( Vendor Colors;["MAROON"; "RED"]; ["MAIZE"; "YELLOW"]; ["NAVY"; "BLUE"]; ["ROYAL"; "BLUE"]; ["AQUA"; "BLUE"]; ["CORAL"; "BLUE"]; ["FOREST"; "GREEN"]; ["KELLY"; "GREEN"]; ["HUNTER"; "GREEN"]; ["BURGUNDY"; "RED"]; ["KHAKI"; "BROWN"]; ["GLASS"; "NO COLOR"]; ["CARNATION"; "PINK"]; ["SKY"; "BLUE"]; ["SPRING"; "GREEN"]; ["CANARY"; "YELLOW"]; ["CREAM"; "BROWN"];.......and so on and so on.....

               

               

               

              • 4. Re: Eliminate Multiple Mention of Same Word in Field
                comment_1
                  

                You could do this all in one step:

                 

                 

                Let ( [
                avail = Substitute ( AvailableColors ; [ ", " ; ¶ ] ; [ " " ; ¶ ] )
                ] ;
                Substitute ( FilterValues ( MasterColors ; avail ) & ¶ ; [ "¶¶" ; "" ] ; [ ¶ ; ", " ] )
                )

                 

                where AvailableColors = "Cardinal Red, Forest Green, Kelly Green, Navy Blue, Royal Blue, Bubblegum Pink, Texas Orange, White" and MasterColors is the list above (return-separated).

                 

                 

                BTW, it would be best to keep the available colors as separate records in a related table, or at least as a return-separated list.

                 


                • 5. Re: Eliminate Multiple Mention of Same Word in Field
                  etripoli
                    

                  comment has a point, instead of having to do a substitution, just have a look-up table with the Available Colors in separate records, matched up with their Master Colors.  Add records to relate the Master Colors to themselves, relate the table to your existing table(s), and to itself based on Master Color = Available Color, then in your existing table create a calculation field = Substitute ( List ( Master Colors Table Self::Master Color ), ¶, ", " )

                   

                   

                  Current Table           Master Colors                       Master Colors Self Available Colors   <--> Available Color : Master Color <--> Available Color (return separated)      (1 per record)