Separating a field of un-predictable comma-separated values into individual records and repeating remaining field values in each record for each of the new, separated records?
Posted on 2011-02-10
I have a Filemaker dB table named "Property". Each record in the table describes an individual owner's property as such:
- Township (Twp)
- Range (Rng)
- Section (Sec)
- Legal Description (Legal)
Twp, Rng and Sec fields all have single, non-comma delimited values per record, but the Legal field contains multiple, comma-separated values, as follows:
Record1 Twp1 Rng1 Sec1 Legal1, Legal2, Legal3
Record2 Twp2 Rng2 Sec2 Legal4, Legal5, Legal6
and so on...
Ultimately I want to have an end-product w/ the values in the Legal field in their own individual record while duplicating the Twp, Rng & Sec fields for the original record into the new records, i.e.
Record1 Twp1 Rng1 Sec1 Legal1
Record1 Twp1 Rng1 Sec1 Legal2
Record1 Twp1 Rng1 Sec1 Legal3
Record1 Twp2 Rng2 Sec2 Legal4
Record1 Twp2 Rng2 Sec2 Legal5
Record1 Twp2 Rng2 Sec2 Legal6
I am unsure of how to do this w/in FM11 solely and directly and I am even willing to export into Excel, manipulate the data and then import into a new table in FM11, if necessary.
This will not be a repetitive task, as this is simply cleanup on a legacy dB that I inherited from a previous employee and therefore accuracy is my primary focus, not repetition.
Thank You in Advance for any and all help you can offer!!!