Create duplicate records if carriage return exists

[blasko]

I can't wrap my head around how to deal with this. I have a field (Field 1) with hard returns. I would like to use these returns as the basis for creating individual records. I need to script this due to the number of records involved. Here's an example of my starting data:

Record Number Field 1 Field 2

1 abc Joe

def

2 ghi Fred

jkl

mno

This is the desired result:

Record Number Field Field 2

1 abc Joe

2 def Joe

3 ghi Fred

4 jkl Fred

5 mno Fred

[comment]

It's hard to tell for sure from such an abstract example, but I believe you should end up with something like:

ChildID....Value....ParentID

1..........abc......1

2..........def......1

3..........ghi......2

4..........jkl......2

5..........mno......2

in a related table.

[blasko]

Sorry. My example got screwed up. Let me simplify the question:

If I have a field with a carriage return in the data, how can I create two records out of that single record with data above the carriage return in record 1 and data below the carriage retien in record 2?

[comment]

IMHO, your example was clear enough. The part I am questioning is that you apparently want to create the separate records in the same table as the original one. You didn't say what this is about, but normally you'd want to leave the existing record as is in the parent table, create the two (or more) individual records in a child table, and link them back to their parent record.

[blasko]

I could create the records in a child table, but I don't really need to preserve the source data. In either case what I'm struggling with is how to parse the data based on teh carriage returns. Working on some math calculations using Position and Length fuctions...

[comment]

what I'm struggling with is how to parse the data based on teh carriage returns.

That's the easy part - just loop over the return-separated list, using =

GetValue ( $yourList ; $i )

each time increasing $i by 1, until =

$i > ValueCount ( $yourList )

I don't really need to preserve the source data

Well, the idea is to store "Fred" in one place only instead of three.