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

    Concatenate Records


      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

          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

            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

              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

                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

                  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"





                  • 6. Re: Concatenate Records



                    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

                      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.)



                      • 8. Re: Concatenate Records

                        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

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

                          • 10. Re: Concatenate Records

                            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

                              ... and Mike...


                              I will be trying your suggestion myself...


                              - Lyndsay

                              • 12. Re: Concatenate Records

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

                                • 13. Re: Concatenate Records

                                  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?


                                  • 14. Re: Concatenate Records

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

                                    1 2 Previous Next