3 Replies Latest reply on May 15, 2015 2:48 PM by rouelf_1

    Separating data into separate fields

    PaulWebb_1

      Title

      Separating data into separate fields

      Post

      I need the equivalent of Text to Columns in Excel. I have a field Serial ID that when imported can have one or more values. I created 10 additional fields Serial 1 to Serial 10. The data coming in looks like...

      FTX16215DUZ,FTX1622L6AC,FTX1633LUBP,FTX1639L1ZW,FTX1641FLLW

      I want to separate this out into 5 separate fields. I am trying to build a formula that will pull each serial number out and put it into a field using Auto Enter - Calculated Value

      Need some guidance.

      Thanks

      FMPA 14

      Screen_Shot_2015-05-15_at_15.10.37.png

        • 1. Re: Separating data into separate fields
          PaulWebb_1

          I got it!

          Let ( [

          @serial = Substitute ( SHP::SERIAL_ID; ","; "¶" ) ;
          @field = RightWords ( GetFieldName(Self) ; 1 ) ] ;

          GetValue ( @serial ; @field )

          )

          • 2. Re: Separating data into separate fields
            philmodjunk

            But you might also consider moving this data into a related table with one record in the related table for each serial number. This will provide greater flexibility when working with these numbers.

            • 3. Re: Separating data into separate fields
              rouelf_1

              I guess you got, and it might be more elegant than what is below.

               

              Something like this might also work for you:

              Create a new field, Serial_ID_Mod 

               

              Then Auto-Enter define the field Serial_ID_Mod =  Substitute ( SERIAL_ID ; "," ; ¶ )

               

              This creates a value list in the Serial_ID_Mod field. When Data is entered into SERIAL_ID all calculations will be performed.

               

              Then     Auto-Enter define the field Serial 1 = GetValue ( Serial_ID_Mod ; 1 )

                  Auto-Enter define the field Serial 2 = GetValue ( Serial_ID_Mod ; 2 )

                  Auto-Enter define the field Serial 3 = GetValue ( Serial_ID_Mod ; 3 )

                  Auto-Enter define the field Serial 4 = GetValue ( Serial_ID_Mod ; 4 )

               

              … and so on to 10.