6 Replies Latest reply on Feb 26, 2016 1:58 AM by m.swanston

    Split a data item into two for all records in a field


      So I'm still a relative novice with FM - I support a tool written by an external developer and so just fix bugs and try to add/improve functionality. One of the changes I've been asked to look at it is to break up a 'column' of data into two (where possible). When the developer was here, I am sure I watched him do something similar, but can't for the life of me remember what/how he did it.

      So the data looks like this (but for over a thousand records):



      3055566 04.13


      3057982 04.13

      3057982 04.01

      3057982 04.13

      3057982 04.13


      3023607.04.13 (US)

      3052095 04 25


      So as you can see, there are some records that only contain a seven digit number, and others that contain both the seven digit number and the appropriate task code, but either delimited by a space or a full stop/period. I have created a new field called d_Task_Code, and I would like to put the 5 digit task code into the new field and leave only the 7 digit code behind (removing any delimiter), so it would look something like this:

      d_Oracle_Code     d_Task_Code


      3055566                04.13

      3052494                04.13

      3057982                04.13

      3057982                04.01

      3057982                04.13

      3057982                04.13


      3023607               04.13 (US)

      3052095               04 25


      Is it possible to write something that would apply this split to all records? For the few that are incorrect ie the last one in the example, would it be possible to insert a full stop/period as well?

      Thanks in advance


        • 1. Re: Split a data item into two for all records in a field



          here is my suggestion:


          • Take a backup of your database, just in case !!!
          • Create two new fields:
            • d_Oracle_Code_Copy
            • d_Task_Code
          • Copy data from d_Oracle_Code to d_Oracle_Code_Copy A copy not a calculation field. This field with the actual values will be used to create the two sub-parts.
          • Create a script that will:
            • Take the first part in d_Oracle_Code_Copy and set d_Oracle_Code accordingly
            • Take the second part in d_Oracle_Code_Copy and set d_Task_Code accordingly
          • Run the script
          • Check that everything is fine and if so delete field d_Oracle_Code_Copy
          • 2. Re: Split a data item into two for all records in a field

            You only need 5 lines of script


            1) find all records with d_task_code empty


            2) replace d_task_code with formula 1


            3) replace d_oracle_code with formula 2


            4) find records with d_oracle_code =="."


            5) replace d_oracle_code with ""



            formula 1: Middle(d_oracle_code;9;2) & "." & Middle(d_oracle_code; 12; 999)


            formula 2: Left(d_oracle_code; 7)


            you can get rid of 4) and 5) by means of an IF in formula 1 but probably replace just the few is faster than evaluating the IF for every record.

            • 3. Re: Split a data item into two for all records in a field

              I worked out a solution with no scripting or finds needed, just a series of calculations and the following fields :


              1. d_Oracle_Code Text // Original Input Data
              2. d_Oracle_Code 01_to_07 Calculation (Text) = Left ( d_Oracle_Code ; 7 ) // First 7 Numeric Characters from Original Input
              3. d_Oracle_Code 08_to_08 Calculation (Text) = "" // Null Character
              4. d_Oracle_Code 09_to_10 Calculation (Number) = Middle ( d_Oracle_Code ; 9 ; 2 ) // Leading Digits of d_Task_Code
              5. d_Oracle_Code 11_to_11 Calculation (Text) = "." // Separator Character for d_Task_Code
              6. d_Oracle_Code 12_to_13 Calculation (Number) = Middle ( d_Oracle_Code ; 12 ; 2 ) // Trailing Digits of d_Task_Code
              7. d_Oracle_Code 14_to_18 Calculation (Text) = Middle ( d_Oracle_Code ; 14 ; 5 ) // Pick Up Trailing 5 Character in Input and add them to d_Task_Code
              8. d_Task_Code Calculation (Text) = d_Oracle_Code 08_to_08 & d_Oracle_Code 09_to_10 & d_Oracle_Code 11_to_11 & d_Oracle_Code 12_to_13 & d_Oracle_Code 14_to_18 // Concatenate the individual components of d_Task_Code

              1. Is the original input, unchanged.

              2. Is the first 7 numeric characters of the input extracted from the input. They are stored as TEXT in the output but can be NUMBER just as easily.

              3. through 7 are fields used to format the output to be stored in 8.

              8.  simply concatenates 3. through 7. as required.

              Again, it could be sexied up with one or two "IF" statements but it will work solidly, reliably and predictably. I've run the 10 record listed through it and it produces the required output. With only 1,000 records to be processed any speed improvement must be off-set against complicating the processing. !The bits following the // in the code above are comments.


              I can zip the data-base and email it id anyone is interested.


              Pat McGrath.

              • 4. Re: Split a data item into two for all records in a field

                Thanks Pat, though I don't want the intermediate columns once I'm done so whilst I can understand the logic, I wanted to run something that applied the change across the data. But appreciate your time and effort.



                • 5. Re: Split a data item into two for all records in a field

                  OK, thanks. Couple of questions though (as this was kind of the approach I was looking to take):

                  How do you copy a field (and it's contents)? I copied the field and pasted it within the Manage Database window, but the copy contains none of the data.

                  I wrote my script, but it only works for the first record. Being a developer in other languages, I am used to looping through records to achieve similar solutions, but am still a novice in FM so maybe I'm missing something obvious!

                  Here's my script:




                  • 6. Re: Split a data item into two for all records in a field

                    Actually, after some googling, I found there was a loop, so have made some small changes:


                    Am going to try this out and see what happens!