Splitting FileMaker Repeating Fields – FileMaker Today
Milan Kundera once wrote that “Happiness is the longing for repetition.” In a FileMaker solution, repeating fields as data storage are sometimes found in legacy database, and need to be converted 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 in an economical way. 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, artificial limits are placed on the number of options, 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)
This Group Separator character is identified by the ASCII character 29, or Char(29). 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 and 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.
In order to convert the group separator into tabs and export the file I created a simple utility database that is available for download below. This database consists of two essential 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 1 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, and if this is 14 the user is presented with 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