1 of 1 people found this helpful
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:
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!)
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.
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 ; "-" ; ¶ )
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....
This worked great! Thank you so very much.