1 2 Previous Next 21 Replies Latest reply on Jun 7, 2017 4:49 PM by genci

    Concatenate Records

    louie23

      Hello guys,

      how do we concatenate records of the same field with dot ( . ) and at the end we have ( example ) @filemaker?

      Thank you!

        • 1. Re: Concatenate Records
          LyndsayHowarth

          I am not exactly sure what you mean.

           

          You can concatenate field data within records by calculation with & inbetween fields... or & "your punctuation" &

          You can also list values in a single field across a set of records. You can do this by creating Valuelists or by using list(related::field)

           

          I need to understand more precisely what you are needing.

           

          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: Concatenate Records
            louie23

            I'm sorry Lyndsay Howarth,

            Lets say in the database, it has 300 records with their own numbers. This numbers are in the same field. So how can I combine those numbers of the 300 records? And place those 300 numbers in one field. Thank you

            • 3. Re: Concatenate Records
              wsvp

              You will need some type of relational context, I don't have enough information to understand your starting point.  But ultimately something like this should accomplish the objective.  You can decide how you want to separate them, This quick formula lists them.  The concept can be modified as needed.

               

              Let ( [

              L = List (TABLE::FIELD ) & "¶"

              ] ; Substitute ( L ; "¶" ; ".@filemaker¶" ) )

               

              You can use any number of custom functions or LeftValue count methods to remove the trailing carraige return if needed.

               

              Hope this helps.

              1 of 1 people found this helpful
              • 4. Re: Concatenate Records
                louie23

                Thanks wsvp, it works but it does not get all the numbers in all records..

                Example: +1-646-222-3333.+1-646-322-5338.+1-646-622-3533.+1-646-822-3903.+1-646-222-3333@filemaker

                • 5. Re: Concatenate Records
                  Mike_Mitchell

                  Louie -

                   

                  wsvp's method will work if all records in the target table are related to the current record. You can set up a Cartesian relationship to make this work.

                   

                  You can also use ExecuteSQL to do this. Thusly:

                   

                  ExecuteSQL ( "SELECT phone FROM {table}" ; "" ; "." ) & "@filemaker.com"

                   

                  HTH

                   

                  Mike

                  • 6. Re: Concatenate Records
                    wsvp

                    Louie

                     

                    I think I might understand better what you are trying to do,  I mis-understood your post ... Thinking you wanted ".@filemaker" after each record... I now assume that within each record the field contains multiple numbers with "." between them.  This revised formula may be better and will remove the trailing return...

                     

                    Let ( [

                    L = List (TABLE::NUMBER ) ;

                    F = Substitute ( L ; "." ; "¶" ) & "¶" ;

                    R = Substitute ( F ; "¶" ; "@filemaker¶" )

                    ] ; LeftValues ( R ; ValueCount ( R ) - 1 ) )

                     

                    Keep in mind that as Mike said, you will need a relationship to the table in question, for the List function to work. You can use a self join using the "X" = Cartesian operator.  Make sure the selected field is through that relationship.

                     

                    Mike's method would probably be a good choice also (I have no background in SQL), Unless you need it to be iOS compatible, the ExecuteSQL step is not iOS compatible.

                    • 7. Re: Concatenate Records
                      Mike_Mitchell

                      We're not using the Execute SQL script step. We're using the ExecuteSQL() function, which is iOS compatible. (Confusion is understandable, given the similarity in the names.)

                       

                      Mike

                      • 8. Re: Concatenate Records
                        LyndsayHowarth

                        When wsvp said "You will need some type of relational context" he was clarifying my suggestion of "by using list(related::field)"

                         

                        The bottom line is that until you said "Example: +1-646-222-3333.+1-646-322-5338.+1-646-622-3533.+1-646-822-3903.+1-646-222-3333@filemaker" we had no idea what you were wanting.

                        There are many ways to do this and both Mike and wsvp's suggestions should work just fine. You need, however, to adapt them to the specifics of your needs...

                         

                        - Lyndsay


                        • 9. Re: Concatenate Records
                          louie23

                          Thanks everyone you are all amazing!!! I'm sorry, I'm really a newbie here. Thanks for your patience

                          • 10. Re: Concatenate Records
                            LyndsayHowarth

                            So... with the wsvp suggestion you would just do 2 things:

                             

                            1. Create or use the appropriate relationship to the data...

                             

                            2. Use

                            Let ( [

                            L = List (RELATED TABLE::FIELD ) & "¶"

                            ] ; Substitute ( L ; "¶" ; "." ) )

                            • 11. Re: Concatenate Records
                              LyndsayHowarth

                              ... and Mike...

                               

                              I will be trying your suggestion myself...

                               

                              - Lyndsay

                              • 12. Re: Concatenate Records
                                LyndsayHowarth

                                As requested privately... a sampler....

                                • 13. Re: Concatenate Records
                                  louie23

                                  I'm sorry this can't be open with. I'm using FileMaker 11 and Windows

                                   

                                  Will I be able to open the sample file you sent if I download FileMaker 12 trial?

                                  Thanks!

                                  • 14. Re: Concatenate Records
                                    Mike_Mitchell

                                    In that case, ExecuteSQL won't work. It requires FileMaker 12. Go with List (wsvp's suggestion).

                                    1 2 Previous Next