FileMaker Example: Each Field Is Now A Record

There may come an occasion when you need to take data that is in a field and make it a related record. Let's say that you have a half dozen or so fields setup for phone numbers. Now you want to move all the phone numbers into a related table. In this example, we have two tables called Parent Table and Child Table. Our goal is to take the 6 data fields in a Parent Table record and make an individual related record for each in the Child Table.

This is done via a combination of techniques including concatenation, parsing, create new record, status function and a looping script. (here is the download link)

SETTING OUR BIG TEXT FIELD
Our first step is to set one text field equal to all the contents of the fields in the tab order and separate each value with a carriage return.

Here at the script steps for this process with a brief description of what we are doing.

Set Field [ Big Text , “”]

This is to initialize the text field for our script by setting it to empty.

Go To Field [ Field A ]

This is our first field in the tab order.

Set Field [ Big Text, Status(CurrentFieldContents ]

This sets the big text field equal to the first field that our cursor is within.

Set Field [ Counter, 1 ]

The counter is a global number field. We will be counting each field we enter into. This is used to compare how many fields we have been in ... to how many fields we want to be in. When they match, we exit the script.

Loop

The process between the Loop and End Loop will repeat until we achieve the requirements of the Exit Loop If script step.

Set Field [ Big Text & "¶" & Status(CurrentFieldContents)]

Here we are setting the Big Text field equal to itself plus a carriage return ( for the new value) and the contents of the current field.

Set Field [ Counter, Counter + 1 ]

This is used to set the counter equal to itself plus one. This is how we count each field that we do in the loop.

Exit Loop If [ Counter = 6 ]

In this example, we want to do 6 fields in the tab order. If you wanted to do 148, you would set it to 148. FYI... make sure your tab order is set correctly before running the script.

End Loop

Each Loop script step requires an End Loop script step as a matter of proper syntax.

SETTING OUR RELATED RECORDS
Now that we have our big text field ready, we need to create our related values. This is done by setting up a related portal with the Auto Create Related Records option checked. This means the last row in the portal will always be empty, waiting for you to create a related record.

Our script to creating the related records will include the following steps.

Loop

The process between the Loop and End Loop will repeat until we achieve the requirements of the Exit Loop If script step.

Go To Portal Row [ Last ]

This takes us to the last portal row, which is ready for us to create a related record within.

Set Field [ Info, Case( PatternCount(Big Text, "¶") ≥ 1, Left(Big Text, Position(Big Text, "¶", 1, 1)),Big Text)]

Here we are setting the field in the newly created related record equal to the top row of the Big Text field. We do this by looking for the first carriage return and setting the related field equal to everything left of the carriage return. If there is no carriage return left ( which will be the case on the last piece of information ), we set the field equal to the big text field.

Set Field [ Big Text, Case( PatternCount(Big Text, "¶") ≥ 1, Right(Big Text, Length(Big Text) - Position(Big Text, "¶", 1, 1)), "")

Here we are slicing the top row off of the Big Text field because we have already placed it into a related record. We do this by seeing where the first carriage return occurs and setting the field equal to everything to the right of it. If there is no carriage return left ( which will be the case on the last piece of information ), we set the field equal to empty.

Exit Loop If [ IsEmpty( Big Text ) ]

When we have emptied the field, we want to loop to stop.

End Loop

Each Loop script step requires an End Loop script step as a matter of proper syntax.

=

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

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