FileMaker FUNdamentals: A Meaningful FileMaker Relationship
by Geoff Wells <geoff@filemakermagazine.com>
RATING: Intermediate
PLATFORM: Macintosh & Windows
VERSION: FileMaker 5
TECHNIQUE FILES: INVOICE.FP5, INVITEMS.FP5
This month's article is not about how to setup the fields used in a relationship but rather how to create the records that are part of a relationship. It's not enough to define the fields that, if equal, will link two files together; you must devise ways to ensure that the records you want to link do, in fact, contain matching data.
There are several ways to do this and the requirements of the project will most likely dictate the preferred way. Whatever the method, the object is to put the same value contained in the "Primary Key" field into the "Foreign Key" field. A few possible ways are:
1) A script in the primary file sets a global field to the value of the primary key field and calls a script in the foreign file. The foreign file script uses a relationship to retrieve the value of the global and set the contents of the foreign key field in a newly created record.
2) A script in the primary file uses a relationship to set a global in the foreign file then triggers a foreign file script. The second script creates a new record and sets the foreign key field to the global.
3) You could do much the same thing as in the previous two examples but use the clipboard to cut and paste the key instead of using a global. You could, but I hope you wouldn't. Altering the contents of the user's clipboard is a very bad practice.
4) If the design calls for records to be created in a portal, checking the box "Allow creation of related records" on the edit relationship dialog will add a blank record to the bottom of the record list in the portal.
Author's Note: I always avoid this method as I think it gives an undesirable look to the user interface. Also if this is the only interaction the user has with the child record all the data fields from the file must be displayed in the portal.
5) My preferred way however, is to use FileMaker's built in record creation mechanism to create line items with just a single script in the primary file.
This method can be used any time you need to create a child record from a parent. We'll use an invoice/line items pair to demonstrate the technique but it is just as valid in any situation.
Line item files are commonly used to store, as the name implies, the details of each individual line. One record for each line on the invoice. A relationship is established between the main invoice file and the line item file. The main invoice file stores the invoice number and the customer details. It is best not to use the invoice number as the relationship key. The user will almost certainly want to use their own numbering scheme which may possibly contain a combination of numbers and letters, They may also require the ability to renumber an invoice or to skip numbers in a sequence.
The programmer should maintain their own unique relationship numbering scheme behind the scenes, which should never be accessible to the user.
The Invoice File
We'll start by setting up the invoice file. Create INVOICE.FP5 and add the following fields:
InvoiceNumber Text
Contact Text
Company Text
Street Text
City Text
State Text
Zip Text
Phone Text
Record{CreationTime} Time Auto-enter the: "Creation Time"
Record{CreationDate} Date Auto-enter the: "Creation Date"
UniqueKEY{Primary} NumberSerial Number with Current Value: "1" Increment: "1"
UniqueKEY{Calculated} Calculation (Text) = "INVC" &
Right("00" & Seconds(Record{CreationTime}), 2) &
Right("00" & Day(Record{CreationDate}), 2) &
Right("00" & Month(Record{CreationDate}), 2) &
Right("00" & Year(Record{CreationDate}), 2) &
Upper(Left(DayName(Record{CreationDate}), 2)) &
Right(("0000" & UniqueKEY{Primary}), 4) &
Upper(Left(MonthName(Record{CreationDate}), 2))
You're probably wondering what on earth the UniqueKEY{Calculated} field is for. This is one of those fields I put in all my solutions - it's the record serial number for the database. The "behind the scenes" number you use to identify each record. I would like to claim it as my idea but it's actually something I saw on one of the FileMaker talk lists a few years ago. I forget who first proposed it but it's an excellent way to avoid one of FileMaker's major shortcomings. Sending upgrades to customers has always been problematic because of serial number fields. It's difficult to synchronize the "next value" with the customers existing data.
The usual solution involves an update script, which requires the customer to approve a replace operation which sets the "next value" field to a higher number than the current maximum serial number. Not a very dependable method. If you examine the formula in "UniqueKEY{Calculated}" you will see it always produces a unique number regardless of the value of the internal serial number. There is no need to update the customer's "next value". The first four characters indicate the name of the database. The "Right" and "Left" functions in the balance of the terms insure that the resulting key is always twenty characters long - which is the maximum number of characters FileMaker will index without a space between them.
An invoice represents the data as it was at a certain moment in time. If a customer moves or changes their phone number this should not effect the data on the invoice. If a relationship were used to display live data from a "Clients" file any changes to the client data would be updated on the invoice. The invoice data must be "cast in stone" so you would normally use a lookup for fields like name, address, phone etc. rather than displaying a relationship.
The Line Items File
The line items file is set up so that each record stores the details for one line on the invoice. A one to many relationship between the invoice and the line items file is used to display each line in a portal on the invoice layout.
Only a few fields are needed for the line items file and don't worry about layout design in the demo because the fields will only be visible through the portal.
Create a new database called LineItem.FP5 with the following fields:
InvoiceKey Text Indexed
Qty Number
Description Text
Each Number
LineTotal Calculation (Number) = Qty * Each
TempKey Text Indexed
The "InvoiceKey" is the field that links the invoice to the line items file. It is the right side or foreign key in the relationship that is used to display the line items portal on the invoice. The question is, "how does the key get in the field".
The Temporary Technique
This is where the "TempKey" comes in.
We are actually going to create two relationships between INVOICE.FP5 and LineItems.FP5. The first one is called "InvItems" and uses the UniqueKey{Calculated} field as the left primary key and InvoiceKey as the right foreign key.
The second relationship also uses UniqueKey{Calculated} as the primary key but it uses TempKey as the foreign key. Be sure you check the box that says "Allow creation of related records" on the edit relationship dialog.
The next step is the "CreateChildRecord" script, which you create in "INVOICE.FP5"
Script:CreateChildRecord
Enter Browse Mode
Set Field [ Temp::TempKey, UniqueKEY{Calculated} ]
Set Field [ Temp::InvoiceKey, UniqueKEY{Calculated} ]
Set Field [ Temp::TempKey, "" ]
Go to Field [ InvItems::Qty ]
Go to Portal Row [ Last, Select entire contents ]
Go to Field [ InvItems::Qty ]
This is the minimum script you need to create a new record. The first "Set Field" forces FileMaker to create a new record and put the value of UniqueKEY{Calculated} into TempKey. It will always create a new record because there will never be an existing matching record. You make sure of that with the last "Set Field" which sets TempKey back to Null.
The middle "Set Field" sets up the relationship you wish to use. In this example we are setting up the InvItems relationship which uses UniqueKEY{Calculated} but you could use the same basic script to set up any relationship you wish.
In addition you can use the same script to populate the child record with any default values. Just include additional "Set Field" script steps before the last "Set Field", which is the line that breaks the Temp relationship.
If you have any ideas for topics you would like me to cover in future articles, I would love to hear from you.
Happy FileMaking!
Geoff Wells is the author of the new shareware program MIXOLOGY available from <http://fmfiles.com/newfiles>. Search the database of close to 1500 drink recipes by name, liquor or mix. Geoff is a Senior Editor at ISO FileMaker Magazine. Email him at geoff@filemakermagazine.com.