9 Replies Latest reply on Dec 11, 2015 2:48 AM by Hoochimagoo

    Merging multiple fields with different separating text


      For 3 separate field lists such as (field1_list ; field2_list ; field3_list)

      When merging multiple field lists into a single field with comma separation I have been using:

      Substitute (List ( field1_list ; field2_list ; field3_list; ); ¶ ; “, ” )

      This works very well. However, I would like to modify this to be able to put different text between the first and second fields and the second and third fields.  And in the future perhaps even more fields.  So rather than just use a comma separation I may use a "that" as well as an "and" to give:


      field1_list that field2_list and field3_list


      However I would still like to have the comma separation between the items of the three lists.


      I expect one way would be to create intermediate fields with the appropriate calculations that create the comma separated lists and join the first and second fields followed by then merging these fields with "that" or "and" separations.  I was just wondering if someone knows of a more single step calculation method?

        • 1. Re: Merging multiple fields with different separating text

          let ( [

          x=1 // (just a placeholder)


          // the list

          ; lst = "A¶B¶C¶D" 


          // the separator

          ; sep = ", "


          // the conjunction

          ; cnj = " and " 


          // position o fthe last pilcrow in the list

          ; pos = position ( lst ; ¶ ; 1 ; patterncount ( lst ; ¶ ) )


          ] ;


          substitute (

               replace ( lst ; pos ; 1 ; cnj )

               ; ¶

               ; sep





          Result = "A, B, C and D"




          Chris Cain


          • 2. Re: Merging multiple fields with different separating text

            Thank you Chris for your quick reply.  I can get this to work now with multiple fields with:


            lst = List( field1_list ; field2_list ; field3_list ; field4_list ; )

            The output becomes: A, B, C and D


            What I would like is the ability to output something like is:


            A, B and C also D


            That is to say to be able to add more than one conjunction


            And yes I'm very new at FM.

            • 3. Re: Merging multiple fields with different separating text

              Since "field1_list", "field2_list", and so on are Fields that are Lists, what is in the lists?


              More simply, can you provide a copy of what is in your "field1_list" field, "field2_list" field, and so on? It will help with whatever you're trying to achieve.


              If what you have is actually just an "A", a "B", a "C", and a "D", then you would be better served with a calculation that concatenates things like you'd want. But if, for example, "field1_list" actually contains a list, and you create a new List field which combines THAT list with the list from field2_list, field3_list, and field4_list, the things you want won't make much sense.


              So...what is ACTUALLY in field1_list? field2_list? field3_list?

              • 4. Re: Merging multiple fields with different separating text

                Yes I accept your point.  I am requesting two different calculations.


                In the first instance if we just stick to the more simple of the two, what I would like is to be able to use multiple (more than just 2 such as the "comma" and "and') conjunctions.  But I wondered if there is a way of doing this in a single calculation where say 4 fields (not lists in this instance) concatenate into one field with the output as above.  So A, B, C, D will be either a single word or a small string of text such as "lift a weight".  What I would like is a calculation that I can vary the number of fields that concatenate as well as the number and type of conjunctions.  I hope that is a little clearer and thank you for your consideration and advice. 

                • 5. Re: Merging multiple fields with different separating text

                  I think you may just want to explore a calculated concatenation field. Don't make it too difficult. Add some flair if you want.


                  You're experimenting and learning, so...do it in ways that can't really hurt you.


                  Create a new calculation field. Call it "LastIsAnd". In this field, you want a comma, comma, command and and.

                  LastIsAnd = FieldA & ", " & FieldB & ", " & FieldC & ", and " & FieldD & "."


                  Pay attention to what's between the quotation marks. A comma and a space. Or a command an and and a space. You can put whatever you want there. You don't need a list.


                  New field: "AlwaysAnOr":

                  AlwaysAnOr = FieldA & " or " & FieldB & " or " & FieldC & " or " & FieldD


                  Hope that helps a bit.

                  • 6. Re: Merging multiple fields with different separating text

                    I had assumed you had actual lists in each of the fields ("field1_list", etc.), so the function I provided works with variable lengths. If list 1 contained "A¶B", 2 contained "C¶D", 3 contained "E¶F¶G" and 4 contained "H", then with the list you described you'd get "A, B, C, D, E, F, G and H".


                    If you just have four values, in four fields, though, then dtcgnet is right that it'd just be easier to concatenate the values, separators and conjunctions with &.


                    If you do have a variable-length list, though, you could still follow the pattern for variations. For instance, for the example you gave, you could calculate the position of the next-to-last pilcrow, and replace the next-to-last pilcrow with another conjunction.


                    Chris Cain


                    • 7. Re: Merging multiple fields with different separating text

                      Thank you both for your helpful advice.


                      You were correct Chris with your initial assumption regarding the joining of lists.  In your example above the output I was hoping to gain would look something like: A, B and C, D or E, F, G may be H. So there would be more than just a "comma" and an "and" conjunction.  That is, more than two.  I'd like to throw in an "or" and a "may be" as well.  I was attempting to do this in the simplest fashion (calculation) without having to create so manner in between fields that would first merge "A¶B" with "C¶D" and "E¶F¶G" with "H" and then create another field to bring them altogether.  Any advice on a method of making this easier would be helpful or am I using the right approach?


                      I appreciate dtcgnet guidance when the fields are not lists but just single strings.  I'm sure this will be helpful in those situations.  I will have a play with them.

                      • 8. Re: Merging multiple fields with different separating text

                        You can combine things together that would also give you what you want. For instance:



                        Substitute (List ( Field1 ); ¶ ; “, ” ) & " and " & Substitute (List ( Field2 ); ¶ ; “, ” ) & " or " & Substitute (List ( Field3 ); ¶ ; “, ” ) & " may be " & Substitute (List ( Field4 ); ¶ ; “, ” )


                        Doing it with a Let statement would make it a bit easier to read and debug.


                        Let ( [

                        ~Field1 = Substitute (List ( Field1 ); ¶ ; “, ” ) ;

                        ~Field2 = Substitute (List ( Field2 ); ¶ ; “, ” ) ;

                        ~Field3 = Substitute (List ( Field3 ); ¶ ; “, ” ) ;

                        ~Field4 = Substitute (List ( Field4 ); ¶ ; “, ” ) ;

                        ~Separator1 = " and " ;

                        ~Separator2 = " or " ;

                        ~Separator3 = " may be "



                        ~Field1 & ~Separator1 & ~Field2 & ~Separator2 & ~Field3 & ~Separator3 & ~Field4 )

                        • 9. Re: Merging multiple fields with different separating text

                          Excellent!  I am very appreciative to both of you for your assistance.  I hope others will benefit from what you have provided as much as I.  I have begun playing with these calculations and varying them in magical ways and yes you are correct with using the let function for ease, particularly when the number of fields used begins to grow.


                          As a side question, as I am on the steep FM learning curve, besides trial and error experience would you recommend any particular reference material for learning calculation and script tasks?  I am working my way through The Missing Manual at the moment.