Parse data from a field into separate records

[EllenG]

I'm looking for a way to parse out data from one field into single records. The current field lists values with a carriage return . So, for instance, the field "date" may have 5 lines:

1/10/04

2/1/04

3/1/04

4/15/04

5/20/04

I want to parse out each line into a separate record, so that my "new date field" will have 1 date only in the field. If there are 5 dates listed, I would end up with 5 records.

The number of line entries in the field varies by record. Animal record 1 may have 3 lines of dates, animal 2 may have 5 lines, etc.

I think I came across a formula for doing this, but I can't find it now. Can someone please point me in the right direction??

Thanks.

Version: Developer v6

Platform: Windows XP

[Reed]

I can think of a way to script this in FM7 using the ValueCount() and MiddleValues() functions, but if you're using FM6, then I'm not sure.... do you have to use v6?

[CyborgSam]

Idea for FM6 script: use PatternCount to get the number of paragraph markers (carriage returns). Then parse the field in a loop using position to find the apropos returns and middle to get the value between them, creating a new record for each value past the first. Set the original record's field to the first line's value.

It's late & I've been FileMakering all day, so if this doesn't make sense reply & I'll write a quick example tomorrow.

[EllenG]

Hi Cyborg. An example would be appreciated.

Thanks.

(The file I am converting is in FM6. My runtime is also in FM6. Can't convert to FM7 because I have too many users on Win98 and some on ME or OS-9..)

[Ugo DI LUCA]

Hi,

Here's one way of handling the job, which wouldn't work with 7 due to the now known bug about repetitions.

There are a few steps for matter of detail, but by joining all these steps in one, it could become real quick.

Also, you'd use ValueListItems in script rather than plain unstored calc.

This method allows to

Sort your list of dates

Check if dates were already inputted into the related file (that could be the same file also)

Import the residual set into new records, using repeating fields.

HTH

FileMaker Version: v6.x

Platform: Mac OS 9

ParseImport.zip

[CyborgSam]

Ellen->

Finally got some time to work up an example. It's designed to be simple and self-explanatory. Read the script, it is heavily commented.

FileMaker Version: 7

Platform: Mac OS X Panther

EllenG.zip

[EllenG]

This solution was exactly what I needed!!

Thank you all for the help.

FileMaker Version: Dev 6

Platform: Windows XP