1 2 Previous Next 20 Replies Latest reply on Sep 25, 2016 10:11 AM by dtcgnet

    Script Question

    bdembar

      How do I write a script to combine comma delimited fields without duplicating words into a list?

       

       

      I currently have 10 fields that have comma delimited lists that I would like to combine into one field in list form.

        • 1. Re: Script Question
          Mike_Mitchell

          I'm not 100% certain I understand what you want, but let's take a look at this.

           

          You can use a Set Field script step and use the following calculation (substitute your own field names):

           

          Let ( [

               field1List = Substitute ( table::field1 ; "," ; "¶" ) ;

               field2List = Substitute ( table::field2 ; "," ; "¶" ) ;

               .

               .

               .

               field10List = Substitute ( table::field10 ; "," ; "¶" ) ;

               allFieldList = List ( field1List ; field2List ; field3List ... field10List ) ;

               allFieldList = cfDedupe ( allFieldList ; "" )

          ] ;

           

          Substitute ( allFieldList ; "¶" ; "," )

           

          )

           

          Now, you're probably asking, "What the heck is cfDedupe?"   

           

          It's a Custom Function I use for removing the duplicates from a list. It's reproduced below:

           

          /*

          Author: Tom Seidler, 2012 [based on Peter Roots CF: http://www.briandunning.com/cf/1109]

          Method: tail end recursion and use of substitute means this can handle lists of (theoretically) infinite size with as many as 50k variants in the list.

           

          An essential aspect: ["¶" & _this & "¶" ;"¶¶" & _this & "¶¶"]

          This allows the guaranteed removal of all occurences of _this, even if they are next to each other, in a list...

           

          Also borrows Ray Cologon's Trim4 methodology to clear all empty values.

           

          input = return-delimited list of values

          output = initially empty

           

          */

           

          Let (

           

          [

          _this = GetValue ( input ; 1 ) ;

          _null = If ( Left ( input ; 1 ) = "¶" ; 1 ) ;

          _valueList = If ( not _null ; Substitute ( "¶" & input & "¶" ; ["¶" & _this & "¶" ;"¶¶" & _this & "¶¶"]; ["¶" & _this & "¶" ; ""] ; ["¶¶";"¶"] ) ; input ) ;

          _l1r = If ( Left ( _valueList ; 1 ) = "¶" ; 1 ) ;

          _r1r = If ( Right ( _valueList ; 1 ) = "¶" ; 1 )

          ] ;

           

          Case ( Length ( _valueList ) ;

          cfDedupe ( Middle ( _valueList ; 1+_l1r ; Length (  _valueList ) - ( _l1r + _r1r ) ) ; List ( output ; _this ) ) ;

          output )

           

          )

           

          If you don't have FileMaker Advanced (and hence can't use the CF), then let us know and we'll use another method.

          • 2. Re: Script Question
            bdembar

            Thanks so much!  I do not have FileMaker Advanced.  Only pro. 

             

            My fields are:

            Percussion 1

            Percussion 2

            .

            .

            .

            Percussion 10

             

            and I want to combine those to a field called Instrumentation.  All of the fields are comma delimited and I do not want duplicates in the combined Instrumentation field.

             

            Thanks!

            • 3. Re: Script Question
              David Moyer

              Hi,

              using Mike's method of creating the list, here's another way to build a de-duped list from that.

              Capture.PNG

              • 4. Re: Script Question
                David Moyer

                Sorry - there's a bug in there.  The line 7 calculation should be:

                not IsEmpty($value) and PatternCount("¶" & $newList  & "¶"; "¶" & $value  & "¶") = 0

                • 5. Re: Script Question
                  Mike_Mitchell

                  David's method will work fine. Another way to parse the list is to use FilterValues to compare what you have against what you've already added to the destination.

                   

                  Set Variable [ $count ; Value: 1 ]

                  Set Variable [ $originalList ; Value: {my stuff above} ]

                  Loop

                       Set Variable [ $curVal ; Value: GetValue ( $originalList ; $count ) ]

                       If [ IsEmpty ( FilterValues ; $newList ; $curVal ) ]

                            Set Variable [ $newList ; List ( $newList ; $curVal ) ]

                       End If

                       Set Variable [ $count ; $count + 1 ]

                       Exit Loop If [ $count > ValueCount ( $originalList ) ]

                  End Loop

                  Set Field [ Instrumentation ; $newList ]

                  • 6. Re: Script Question
                    bdembar

                    Thanks!  Very helpful, yet I really am a beginner at this.  I'm not sure how to combine both of your scripts.

                    • 7. Re: Script Question
                      David Moyer

                      to populate Mike's $originalList make it equal to:

                      Let (

                        [

                          field1List = Substitute ( table::field1 ; "," ; "¶" ) ;

                          field2List = Substitute ( table::field2 ; "," ; "¶" ) ;

                           .

                           .

                           .

                          field10List = Substitute ( table::field10 ; "," ; "¶" ) ;

                          allFieldList = List ( field1List ; field2List ; field3List ... field10List )

                        ];

                        Substitute ( allFieldList ; "¶" ; "," )

                      )

                      • 8. Re: Script Question
                        David Moyer

                        To populate my $rawList, use the above calculation, but substitute Substitute ( allFieldList ; "¶" ; "," ) with just allFieldList

                        • 9. Re: Script Question
                          Mike_Mitchell

                          Oops! That won't work since we're now parsing in a script instead of using the CF. It should be:

                           

                          Let (

                            [

                              field1List = Substitute ( table::field1 ; "," ; "¶" ) ;

                              field2List = Substitute ( table::field2 ; "," ; "¶" ) ;

                               .

                               .

                               .

                              field10List = Substitute ( table::field10 ; "," ; "¶" ) ;

                              allFieldList = List ( field1List ; field2List ; field3List ... field10List )

                            ];

                           

                            allFieldList

                           

                          )

                           

                          (Because if you use the Substitute, you'll lose the list.)   

                           

                          Then, last line of my script becomes:

                           

                               Set Field [ Instrumentation ; Substitute ( $newList ; "¶" ; "," ) ]

                          • 10. Re: Script Question
                            David Moyer

                            I see.  I missed that.

                            • 11. Re: Script Question
                              dtcgnet

                              The attached file uses only native FM functionality, and it works without any scripts. There is a calculated field similar to what Mike mentioned that creates one big list of all of the items in your 10 percussion fields. There is also an unstored calculated field which uses the following formula:

                               

                              Substitute ( ValueListItems ( "" ; "PercussionValues" ) ; ¶ ; ", " )

                               

                              PercussionValues is a value list based on a self join Table Occurrence based on PK = PK, and the value list uses only related values.

                               

                              The resulting list of values is auto-alphabetized (not sure if you want that), and also not case sensitive ("sticks" and "Sticks" will be seen as the same thing).

                               

                              Give it a shot.

                              • 12. Re: Script Question
                                bdembar

                                Thanks!  This seems to be the best solution, however, I am unable to get the Deduped list to show up.  I think that I did everything right, but it is just blank.  It does work on your example.

                                • 13. Re: Script Question
                                  dtcgnet

                                  It will update on record commits. If you incorporate it into your own solution, make sure that the deduped list field is an unstored calculation.

                                  • 14. Re: Script Question
                                    bdembar

                                    I did incorporate into my solution and it is an unstored calculation, but still not working. Not sure what I need to do.

                                    1 2 Previous Next