Milan Kundera once wrote that “Happiness is the longing for repetition.” In a FileMaker solution, you sometimes find repeating fields as data storage in the legacy database and need to convert them to non-repeating fields. Migrating these fields into new solutions usually requires creative thinking and extra work.
Repeating fields in FileMaker seemed like a good idea at the time. It allowed developers to store similar information economically. Instead of Phone1, Phone2, etc. one single phone number field with repetitions allowed for similar data stored together. There limits to the functionality of repeating fields. Reporting is problematic, the number of options have artificial limits, and the extra space in the form of unused fields or repetitions is both inelegant and extraneous. Once developers could create related tables, many of these issues disappeared.
I looked for an approach that could work with any number of fields, to avoid re-writing the process for fields with different numbers of repetitions.
When exporting a repeating field into a tab or comma-delimited file, FileMaker adds an invisible Group Separator character between each repetition. Regardless of the number of repetitions, this character separates those repetitions into individual segments. This character appears as an upside-down question mark in the exported file opened in a text editor (see image 1)
Repeating fields with group separator as interpreted by text editor application.
The ASCII character 29 or Char(29) identifies this Group Separator character. FileMaker’s Char function now can take this group separator and turn it into any other character, such as a tab. Once you have a tab delimited file you can drop this onto the FileMaker application icon. FileMaker will create a new file for you, with your data parsed into separate fields. The fields are numbers f1, f2, etc., but the work of creating a file with n number of fields where n matches the number of repetitions is taken care of without significant labor.
To convert the group separator into tabs and export the file, I created a simple utility database available for download below. This database consists of two required fields. The first field is a global container field called “tempFile.” The second field is a text field that I called “importedRecords.” Then I created one script, called “Convert Repeating Field into Multiple Fields.” This script is attached as a script trigger to the global container field. Once I export the data from a repeating field into a CSV or tab-delimited file, I drag the file onto the container field. The script then takes over.
First, this script sets a variable for a temporary file path and name, in this case, “Repeated.csv,” in FileMaker’s temporary path folder. Then, using the “Insert from URL” script step on the new file, the contents of the file is loaded into the text field. “Insert from URL” doesn’t just mean web addresses, but natively lets you import content from external files on your computer.
The group separator from image one is converted to a tab character — Char(9) — as the file contents are set to a variable in the script. Any leading and trailing double quotes are removed (again, see image 1). Also, this action converts commas to a tab character, which lets you export an ID field along with the repeating field in the final export step. When FileMaker creates the new file, it retains the original ID along with the new fields so I can merge other fields into the same table.
Finally, the new tab-delimited file is exported from the FileMaker file. There’s a great feature in FileMaker 14 that lets the user select the directory for their export location — watch the video. In the script, we check for the version of FileMaker. If this is 14, the user views a dialog where to save the file. For older FileMaker versions the file is saved to the desktop.
With this utility file, I can take any repeating field, regardless of the number of repetitions, and create a new database with those repeating fields broken up into individual fields.
Download Sample File
- Repeating Field Parser (.zip file)