here is my suggestion:
- Take a backup of your database, just in case !!!
- Create two new fields:
- 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
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.
I worked out a solution with no scripting or finds needed, just a series of calculations and the following fields :
- d_Oracle_Code Text // Original Input Data
- d_Oracle_Code 01_to_07 Calculation (Text) = Left ( d_Oracle_Code ; 7 ) // First 7 Numeric Characters from Original Input
- d_Oracle_Code 08_to_08 Calculation (Text) = "" // Null Character
- d_Oracle_Code 09_to_10 Calculation (Number) = Middle ( d_Oracle_Code ; 9 ; 2 ) // Leading Digits of d_Task_Code
- d_Oracle_Code 11_to_11 Calculation (Text) = "." // Separator Character for d_Task_Code
- d_Oracle_Code 12_to_13 Calculation (Number) = Middle ( d_Oracle_Code ; 12 ; 2 ) // Trailing Digits of d_Task_Code
- 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
- 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.
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.
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: