FileMaker Repeating Field Data To Portal Data Via Scripted Import

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

FileMaker has always been a solid performer and this is one of the reasons people don’t rush out to get each upgrade. Back in the day, (and I’m talking way back), FileMaker didn’t have portals. Portals were introduced along with FileMaker 3 in 1995. Right now, there are probably more developers that never spent quality time with repeating fields, than us long time veterans that remember when the repeating field was cutting edge FileMaker design.

The repeating field is one of those “unique and flexible” storage types. In fact, it is so unique, you probably won’t find anything like it in most other database systems. A repeating field is a special field storage option that can hold multiple values with the field. Sort of like a spreadsheet column for a particular field. Today repeating fields are used in older legacy databases that haven’t been updated or for special workaround techniques.


To make a field a repeating field, you select the field, click the options buttons, click the storage tab and in the storage options area, you define how many repetitions you want the field to have. There is one additional step you need to take when you put a repeating field on a layout. You have to tell FileMaker how many of the repetitions you want to show on the layout. This is done in layout mode ( of course ), you click on the field and choose the field options selection from under the Format menu (pre FileMaker 11). FileMaker 11 and higher users will want to use the new Inspector feature shown below.


If you are  converting an  ancient  FileMaker  solution  to  a revamped  solution,  one  of the  first tasks you may have to address is changing repeating fields into relational portal data. Many times,  you don’t  have  to  convert all  your repeating  fields  into  portals,  just  the ones  that would  benefit  from  going  relational.  A portal will  give you the ability to have  more values, better search results, better reporting and best adaptable to change. 

This example is to show you one method of moving the information from repeating fields to a linked child file with each previous repetition appearing in a separate portal row. There are many ways to perform this operation. This one uses a scripted import command. When you import a repeating field from one file to another, FileMaker will ask you if you want to break up the data in the repeating fields into individual records. This is perfect for setting up a portal based upon repeating field data.

To set this up, simply do the import one time, making sure that the record id field is imported as well. Next you will want to create the script with the import script step. FileMaker will remember the import field order and options selected.

Normally, this is an action you would perform once and then delete the repeating fields. I've seen solutions though where they still use repeating fields on a regular basis and import them into portal records on a regular basis as well.

NOTE: Be careful about letting regular users perform this action. FileMaker will import the found set of records. It is possible that they could import the same records again and again. Then you will find your database files with multiple sets of duplicate records.

An example file can be downloaded by clicking (here).
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - 2010 Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.