From 1 field to multiple records

May 20, 200322 yr

How do I to split one field ("repair actions") into different records.

The field contains sentences separated by a paragraph mark.

Every sentence has to become one record.

What is the correct formula to do this? To me it looks hard because every sentence contains a different number of words.

To make it even harder I'd like to take along the value of a keyfield ("IDService") in the new record.

So what I need is a record per sentence with the fields ("repair actions" - 1 sentence) and the according key field ("IDService").

But maybe you know the right way to do it. Thankyou for reading and eventually answering this question.

May 20, 200322 yr

I think it might be easier to fix the data in a text editor first. If I'm reading your post right, it would appear that this is being imported and then you want to fix the mess. Although you can do what you want, it isn't just a "What is the correct formula to do this?", if it were, you would already know how to do this.

Instead, consider using a text editor (or Word) to fix the text the way you want if and then import it. I use BBEdit (it's a Mac application, but there are Win equivalents). Use the Tab delimitated format for export and import into FileMaker, each return creates a new record each tab signifies a next field.

So, what I would do, is to do a find and replace of each return, and replace with a return, your field IDService and a Tab. This will make each sentence appear as:

IDService (tab) your sentenc

IDService (tab) your next sentence

etc.

Remove all multiple returns so that it looks like this:

IDService (tab) your sentence

IDService (tab) your next sentence

And the it is ready to be imported.

HTH

Lee

May 20, 200322 yr

You could create a repeating field (I can't believe I'm recommending this!) that parses out the field you need to separate. Set each repetition of the repeating field to a different sentence using the "position" function. Then, when you import the data into the new DB, you can separate the repetitions into separate records.