I have a Filemaker table imported from an XL spreadsheet. Each record has a unique identifier and a few fields that each contain multiple entries separated by commas. For example, the "color" field in one record might have:"red, white, blue, green, yellow) and in another record "red, blue, green". I want to generate a single record for each color in the field. Therefore, for each unique record ID, I am looking for a straightforward way to generate the number of records necessary to have only one color in the "Color" field. Thus, Record 123 with "color" = "red, blue, green" would end up as three records:
Record 123 red
Record 123 blue
Record 123 green
I would greatly appreciate any suggestions.