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)
"one¿two¿three¿four¿five¿¿seven¿¿nine¿"
"alpha¿bravo¿¿¿delta¿¿¿¿¿"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)
Anders, it’s easier to copy the repeating fields along with the primary key from the original table and then create a new table and paste the fields. Add a primary key to the new table and then establish a relationship between the primary key that is in both tables. Now exit Define Relationships, make sure that all records are found in main table, go to layout for new table and import, using matching names and splitting repeating fields into separate records. Use auto-enter options to populate the primary key {Get(UUID)}. As soon as the import is completed, the repeating fields can be deleted from the primary table and you have individual records for every used repetition. No need to use the separate file.
Thanks for the comment. That might be one option, though possibly there’s more to it that just pasting the fields? With blank records you don’t have the option to import using matching records. I like the option of not creating a separate file, which shows there are usually various options in FileMaker that accomplish the same goals.
Also, the goal here was to split the repeating fields into separate fields, not necessarily records that could relate back to the original record.
The only time you would need to split the records is if you are getting records out of a repeating field in one table and want to normalize the database by having those ‘rows’ as related records. If there are no records to import, then the exercise is unnecessary. And it really is just a case of copying the fields from one table and pasting them into another. You can also easily see which fields need to be copied by looking for the ones that have repetitions. (Whenever I have done this, I’ve added a portal next to the repeating field so that I can quickly check to see that the import worked properly and then the original fields are deleted).
Repeating fields I’ve never used. What role can they play when creating modern day solutions?
I wouldn’t use them these to store data; they might take on the role of utility fields. One example is to store resources like icons, images, etc. in global repeating fields so that you don’t need individual fields for each image.
Anders is right, they are perfect for storing lots of global values in a single field and they were used for storing data in a flat file (non relational database). Nowadays the values that would have been in a repeating field are stored in separate records in a related table, i.e., invoices and invoice items.