4 Replies Latest reply on Dec 15, 2016 9:06 AM by PatPhelan

    Split 1 text field into three separate ones

    PatPhelan

      I am relatively new to FileMaker and have a database I imported from another source.

       

      One text field is structured thus: City, State - Structure Name

      City and State are separated consistently by a comma; state and structure name are consistently separated by a dash; the structure name includes everything to the right of the dash and may contain punctuation.

       

      I would like to put the City in a city field, state in state, and structure name in a separate field.

       

      I'm unsure how to accomplish this and would greatly appreciate help.

       

      Thank you.

        • 1. Re: Split 1 text field into three separate ones
          erolst

          Create a backup copy of your database!

           

          Then try this:

           

          - make sure all records are shown

          - click into the City field

          - Choose Records > Replace Field Contents

          - select 'calculated result'

          - in the dialog, enter this calculation:

           

          Let (

            theList = Substitute ( yourImportField ; [ "," ; Char(10) ] ; [ "-" ; Char(10) ] ) ;

            Trim ( GetValue ( theList ; 1 ) ) // 1 = city, 2 = state, 3 = structure

          )

           

          -  click OK, and Replace.

           

          Repeat this for State and Structure, each time with a different digit, as indicated in the comment.

           

          Should something go awry, close and trash the current file, and start over with the backup file (after you created a new backup, of course!)

          1 of 1 people found this helpful
          • 2. Re: Split 1 text field into three separate ones
            philmodjunk

            Consider this calculation:

            Let ( TheList = Substitute ( YourFieldHere ; [ "," ; ¶ ] ; ["-" ; ¶ ] ) ;

                     Trim ( GetValue ( TheList ; 1 )

                   )

             

            Will extract just the City name. Change the 1 to 2 and you'll get the state and 3 gives you the structure. You can use these calculations several different ways:

            In fields of type calculation with a text result type.

            In a series of Replace Field Contents operations (one for each field) that extract the data and put it in a text field

            In a looping script using set Field to copy over the data.

            • 3. Re: Split 1 text field into three separate ones
              everyman

              For the sake of clarity, let's call the existing field OldField, and the new fields City, State, and Structure.

               

              There are multiple ways to approach this, but I think the most straightforward is to convert OldField into a value list.  In the example code I have used the variable $_oldField as a stand-in for OldField:

               

              let ( [

                _step1 = substitute ( $_oldField ; "," ; ¶ ) ;

                _step2 = substitute ( _step1 ; "-" ; ¶ )

                ] ;

                _step2

              )

               

              Assuming this is assigned to variable $_newFieldList, then:

               

              set field [ table::City ; trim ( getvalue ( $_newFieldList ; 1) ) ]

              set field [ table::State ; trim ( getvalue ( $_newFieldList ; 2) ) ]

              set field [ table::Structure ; trim ( getvalue ( $_newFieldList ; 3) ) ]

               

              There's room for improvement, but this should get you started....

              • 4. Re: Split 1 text field into three separate ones
                PatPhelan

                This worked great! Thank you so very much.