FileMaker Repeating Fields To Portals Rows ( 2 )

From Dwayne Wright PMP
Certified FileMaker Developer

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

I’m thinking that I might have to introduce some of you to the concept of repeating fields. 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.

Repeating fields were the predecessor to portals in the FileMaker Pro 2.0 and earlier days. As a FileMaker developer for hire, I run into solutions (created by someone within the company or a previous developer for hire) that use repeating fields instead of portals. Although I would say that I see it less and less every year and can tell that some day that will be a rare occurrence.

A repeating field is a special field storage option that can hold multiple values. Sort of like a spreadsheet column for a particular field. This was the standard way of entering multiple values of a field before FileMaker 3. and portals came around. Today it is used mostly in older legacy databases and for special unique purposes.

If you are converting an older 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.

I’ll include an example of how to do this but basically you want to export the repeating field data (along with the primary key data) into another file. Make sure you have the setting check to make the field repetitions separate records. Then you can import that data into a new table, setup your relationship, create your portal and you are done!

FYI... I’ve written in other publications about the rules of normalization in relational databases. For a database to comply with first normal form standards, it must only contain one value. As you can see, repeating fields are a slap in the face of the first normal form rule. Portals showing multiple related records on the screen, most times comply with all the primary rules of normalization.

This is a pre-FileMaker 7 way to handling this action but it still works just fine. I will update this example at some time using variables.

This example is to show you one method of moving the information from repeating fields to a linked child file with each previous repetition appears in a separate portal row. There are many ways to perform this operation. This one uses a script that looks to see if there is any data in the repetition and creates portal records for the repeating data it finds.

The relationship between the two files does have the option of creating new related records. So there is an empty portal row at the bottom that will create a related record as data is placed in it's fields. So we designed a script to do just that.

First we should mention an important function we will be using.

GETREPETITION FUNCTION
GetRepetition(name of repeating field, # of repetition)
Used to pull the value that is within a specified repetition of a repeating field. The syntax includes the name of the field and then the repetition of the field you want to pull.

This is the heart of the script. We repeat the script steps each time but modify the specified repetition for each fields data we want to acquire.

If ( IsEmpty(GetRepetition(Name, 1)) and IsEmpty(GetRepetition(Phone, 1)) )
Else
Go to Portal Row [ Select, Last ]
Set Field [ repeatPortC: Name, GetRepetition(Name, 1)
Set Field [ repeatPortC: Phone, GetRepetition(Phone, 1)
End If

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 - 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.