11 Replies Latest reply on Jan 12, 2012 8:05 AM by LaRetta_1

    Combining fields into one

    rkevwill

      Title

      Combining fields into one

      Post

      I am looking to combine name and address fields into one large "address container"  Many years and versions of filemaker ago, I found this very useful for various labels and forms.

      Example

      "First Middle Last

      Company

      Address One

      Address Two

      City, State Zip"

      Those are all unique fields, and I want to draw the data into one larger field. Any tips on how to do this?

        • 1. Re: Combining fields into one
          DavidHearn

          What I would do (but it probably isn't the only or best way) is:

          Create a new field (eg: Address Container" as a Calculation field. In the calculation options enter all the original fields you want to include as follows:

          First Middle Last  & ¶  & Company & ¶ & Address One & ¶ & Address Two & ¶ & City & ¶ & State & ¶ & Zip

          Ensure the "calculation result is" drop down at the bottom of the window says "text". This will create the one field you need but has a drawback that if one of your original fields is empty it will leave an empty line in your container. Hopefully all original fields do have data in them.

          If some of your orginal fields are blank and you DO NOT want to leave an empty line then the way I woud do it (and again this probably isn't the best way but it will work) is as a script (which can be auto triggered to run upon zip field exit for example if you wish).

          Firstly set up your Address Container field as a normal text field and then create a script along the lines of (Untitled is the database name):

          If [ IsEmpty ( Untitled::First Middle Last ) ]

          Else

          Insert Calculated Result [ Untitled::Combined3; Untitled::First Middle Last & ¶ ]

          [ Select ]

          End If

          If [ IsEmpty ( Untitled::Company ) ]

          Else

          Insert Calculated Result [ Untitled::Combined3; Untitled::Company & ¶ ] (NOTE: ENSURE "SELECT ENTIRE CONTENSTS" IS DISABLED)

          End If

          If [ IsEmpty ( Untitled::Address One ) ]

          Else

          Insert Calculated Result [ Untitled::Combined3; Untitled::Address One & ¶ ]

          End If

          If [ IsEmpty ( Untitled::Address Two) ]

          Else

          Insert Calculated Result [ Untitled::Combined3; Untitled::Address Two & ¶ ]

          End If

          If [ IsEmpty ( Untitled::City ) ]

          Else

          Insert Calculated Result [ Untitled::Combined3; Untitled::City & ¶ ]

          End If

          If [ IsEmpty ( Untitled::State ) ]

          Else

          Insert Calculated Result [ Untitled::Combined3; Untitled::State & ¶ ]

          End If

          If [ IsEmpty ( Untitled::Zip ) ]

          Else

          Insert Calculated Result [ Untitled::Combined3; Untitled::Zip ]

          End If

          Hope this helps.

           

          • 2. Re: Combining fields into one
            philmodjunk

            Why not just add the fields as merge text on your labels layout?

            <<First Middle Last>>
            <<Company>>
            <<Address One>>
            <<Address Two>>
            <<City>>, <<State>> <<Zip>>

            You can use the slide up option in sliding and visibility to set these fields to slide up to eliminate an empty space should address two be empty.

            If you still want to put it all in one block, this calculation will combine all the text and close up any empty spaces should Address two be empty:

            List ( First Middle Last; Company; Address One; Address Two; City & ", " & State & "  " & Zip )

            Note: I don't recommend using the Insert steps to add text to a field unless there is no alternative. Insert steps fail silently if the target field is not physically present on the layout. Set Field can be used for the same purpose and does not have that limitation.

            • 3. Re: Combining fields into one
              rkevwill

              Phil,

              In my past experience, the reason I wanted one field, is the flexibility it gives me to move the contents around, in one piece, to accommodate the field placement on various labels, and with various printers. Its a personal preference. (now remember, this was with FM back in the mid 90's). Just ignore the reason why at present.

              When I used your script, here's the error I received. (attached). Also note, in empty combined fields, it also deposited the comma. Have a tweak you might suggest?

               

              • 4. Re: Combining fields into one
                rkevwill

                David, I received the same error with your calc, but am still working on it.

                • 5. Re: Combining fields into one
                  rkevwill

                  Ok, stop the presses. I have succeeded in combining the fields, but I am having issues with putting a space in between certain fields. Here is the calculation I am using.

                  List ( First & Middle & Last & Company; Address; Address2; City &  State & "  " & Zip )

                  Here is what I get (ignore the double spacing between lines. For some reason, that comes with the pasting here)

                  JohnQsmith

                  122 no place

                  ste 900

                  carmelIN  46032

                  Note, no space in the 3 name fields. Also note, no space between city and state. Tips on correcting the script? BTW, testing with another entry, the fields do collapse fine, when empty.

                  I am getting there, slowly, but getting there.

                  • 6. Re: Combining fields into one
                    rkevwill

                    Update again. Here is the calc I have updated, but having the brain dead issue in the spacing in the lines.

                    List ( First & Middle & Last; Company; Address; Address2; City &  State & "  " & Zip )

                    Result is

                    JohnQsmith   (note spacing issue here)

                    What inc.

                    122 no place

                    ste 900

                    carmelIN  46032 (and here)

                    • 7. Re: Combining fields into one
                      philmodjunk

                      I assumed that First Middle Last was a single calculation field. Since they are separate fields, use:

                      List ( First & " " & Middle & " " & last ; ...

                      Since some people may only have a single name or a name with no middle initial, you may want to get a bit more sophisticated and use:

                      List ( Substitute ( List ( First ; Middle ; Last ) ; ¶ ; " " ) ; //put the rest of your expression here )

                      This will give you

                      John Q Smith

                      or

                      John Smith (when there is no middle name/initial)

                      or

                      Prince (if this person/company has no first or middle name)

                      • 8. Re: Combining fields into one
                        rkevwill

                        Here is the calculation I ended up with. It seems to work flawlessly, and most importantly, its bringing back in my old mind the symbols etc. I thank you much. Now, for an explanation on the single field. It saves a lot of work when copying and pasting that information in a single block on related forms. (again, I remember this from the old days). Also, it was most important for labels, back when label placement was a little dicey with different printers. Probably not needed these days, but it was an old comfort level to me again.

                         

                        Again, thank you guys for fixing this for me!

                         

                        Kevin

                        • 9. Re: Combining fields into one
                          LaRetta_1

                          List ( Substitute ( List ( First ; Middle ; Last ) ; ¶ ; " " )

                          Hi Phil, how about:

                          TrimAll ( First & " " & Middle & " " & Last ; 0 ; 0 )

                          • 10. Re: Combining fields into one
                            philmodjunk

                            Hi LaRetta and Happy New Year.

                            Just saw raybaudi use a very creative version of that TrimAll in another context to remove extra returns. Take your pick. Either work for me.

                            • 11. Re: Combining fields into one
                              LaRetta_1

                              Yeah, using TrimAll() to remove returns was first shown to me by JT ( Queue ) over on FM Forums over five years ago now and I've used it ever since.  It requires one function instead of two so is more efficient as well.  :^) And Happy 2012 to you too, Phil!!