Just to confirm, does your example show one block of text with multiple lines or 8 blocks of text?
Does it show 1 record or 8?
How will this data be "uploaded" to your FileMaker database?
Since you have multiple "Interests" listed, how should they be structured for export?
There are scripts and calculations that can parse out the text into individual fields, but we need to know the details before we can make a detailed suggestion.
I simplified this example to get my question across. This would all be 1 record, so one complete text block. and under the interests section that would be parsed off into a table where I would relate for instance the persons name three times and list their 3 interest as three separate records.
So, my structure would be to have a contact table. Which has the text block as one field and then all of the parsed contact information (name, address, city, state, etc.)
And then there would be a 2nd related table that would have the persons name (for sake of example) and would create each of the interests as a new record.
The information would be uploaded as individual text blocks, creating a new record each time.
The good news is that you have just one "field" per line and what appears to be a very predictable format. This greatly simplifies things. If I am assuming a consistancy that will not be the case with the actual data, you'll need to elaborate a bit so I can modify this approach.
Trim ( Right ( GetValue ( Contacts::TextBlockField ; 1 ) ; Length ( contacts::TextBlockField ) - 6 ) )
will return the Name data. An auto-enter calculation on the Name field or a script using set field with this expression can extract it to copy to another field.
Different versions of that will work for Name through state.
the 1 in GetValue returns the first row of text. The 6 represents the 6 characters of Name:<space>. Trim removes any leading or trailing spaces.
I would not relate the two tables with the name field. Add an auto-entered serial number on the contacts table and use it to generate the list of interests in the related interests table. (Name are not always unique and people change their names. Names also are easily misspelled and require correction after initial data entry.)
This script can parse out the list of interests into separate related records:
Set Variable [$ContactID ; value: Contacts::ContactID ]
Set Variable [$InterestsList ; Value: RightValues ( ValueCount ( contacts::TextBlockField ) - 5 ]
Go To Layout [INterests (Interests) ]
Set Variable [$K ; Value: $K + 1]
Exit Loop If [$K > ValueCount ( $InterestsList ) ]
Set Field [Interests::ContactID ; $ContactID ]
Set Field [Interests::Interest ; GetValue ( $InterestsList ; $K ) ]
Go To Layout [original layout]
One thing I didn't mention, is that the list of rows/fields could be variable. So I can't assume that the first row will be name and the second address, how can I get around that.
I just used name as my relating field for sake of example, I am using a unique auto-entered number.
Thank you for your help with the loop script.
Then you have to find position of the label text inorder to extract the trimmed text to the right of it.
Let ( [ T = Contacts::TextBlockField ;
start = Position ( T ;"Name:" ; 1 ; 1 ) + 6 ;
End1 = Position ( T ; ¶ ; start ; 1 ) ;
End = if ( End1 ; End1 ; Length ( T ) + 1 ) //allows for possibility that "Name:" is on last line of text block
Trim ( Middle ( T ; start ; End - start ) )
) // Let