From the Field: Advanced Portal Techniques - File Maker

Now that you know the basics about relationships and portals, here are some field-tested tips and tricks you can implement on your systems. Each technique has an explanation (some long, some short, depending on the technique at hand) and a set of sample files on the CD-ROM for you to dissect and explore.

You intermediate to advanced developers might find these useful.

Many-to-Many Relationship
On the CD-ROM: In the Many to Many folder.
Primary Database: Pets.fp5.
The Problem: The following dialog has been exchanged countless times between FileMaker Pro developers and their users:

[Developer sits comfortably in an Aeron chair in front of a 17" PowerBook G4, carefully monitoring a fictional client’s FileMaker Pro Server’s cache hits via Timbuktu Software. Enter JOHN DOE USER, Developer’s client contact and novice FileMaker Pro user.]
JOHN: “Say, Developer, I’ve got this database called Pets.fp5 and another database called Supplies.fp5. Now I know how to create a portal in Pets.fp5 so that I can see all of the supplies linked to a pet. But how in the heck can I create a portal to link many different pets to many different supplies?”
DEVELOPER: What you need, my friend, is a many-to-many relationship.
JOHN: My many relationships are going just fine, thank you very much.
[Developer sighs a tired sigh.]

Rising Action
So John’s got a Pet data entry layout where he sees basic information about one of the pets he sells, an albino gerbil, and a portal where all the supplies that he needs to sell with the gerbil, the “gerbil set,” if you will, are listed. Now, if he creates a new record in Pets.fp5 for the Syrian hamster he also sells and tries to enter in the portal the supplies associated with the hamster via a drop-down list populated by the Supplies.fp5 database (many of which are similar to those in the gerbil set) everything seems to work fine from this point of view.

But the next morning, when John goes over to the Supplies.fp5 database because he needs to change the selling price and description of the carrotshaped chew sticks supply record, there seem to be duplicate records all over the place! O! O! O!

What went wrong? How did John manage to muck up his system and add a bunch of duplicate supplies to his Supplies database? In a nutshell, John is trying to work as if he has the functionality of a many-to-many relationship when in reality he has only a one-to-many relationship. John wants to be able to link many of his supplies to many of his pets and vice versa. But before we tackle that, let’s define our terms.

The One-to-Many Relationship
Before John ever comes to Developer for help, he has a simple database structure that looks like this:

The forked line between Pets and Supplies means that there are many supplies linked to every one pet, but each supply has at most one link to a pet. This is a one-to-many relationship. Another example is how John’s wallet has many colorful credit cards in it, but each credit card lives only in one wallet (John’s).

The Solution
The solution is a many-to-many Relationship. What John actually want is a structure that looks like this:

Here, there is Pets.fp5 database on the left, a Supplies.fp5 database on the right, and a database called PetSupplies.fp5 in the middle. What’s it doing in there? And how did it get in there? Basically, PetSupplies.fp5 is, as many computer science major database specialists will call it, a transaction database (or a junction database): a database that keeps track of all the links between Pets.fp5 and Supplies.fp5. No relationship between Pets.fp5 and Supplies.fp5 exists directly, but the PetSupplies.fp5 database breaks down this “many-to-many relationship” into two manageable one-to-many relationships so that many pets can have many supplies. FileMaker developers call this a join database, and it is a truly magical thing. Are you still with us? (John is.)

Setting Up a Join Database
Let’s sit down with John and create this pet store system for him from scratch.

1. First, create a Pets.fp5 database with the following fields:



2. Next, create a Supplies.fp5 database with these fields:



3. Create a PetSupplies.fp5 database with the following few fields:



4. Now go back to your Pets.fp5 database and create a basic data entry screen, then go into Define Relationships and define a new relationship to the PetSupplies.fp5 database with Pet ID highlighted on both the left and right.
(Make sure to check “Allow Creation of Related Records” and “When Deleting a Record in This Database, Also Delete Related Records.”)
Click OK.

5. Add a portal to the data entry layout using the relationship just created and put the Supply ID field into it, then define a value list in Pets.fp5 that looks like this:



Next, attach it to the Supply ID field in the portal to PetSupplies.fp5.

Now is a good time to add a few unstored fields to PetSupplies.fp5 that will make using this system more intuitive and much easier to use for John and his clerks.

1. irst, create two relationships in PetSupplies.fp5, one called Pets by Pet ID (relating Pet ID to Pet ID) and another called Supplies by Supply ID (relating Supply ID to Supply ID).
2. Next, create two calculated fields, as such:

c Pet Name, an unstored text field, defined as: Pets by Pet ID::Pet Name
c Supply Name, an unstored text field, defined as: Supplies by Supply
ID::Supply Name

3. Go back to Pets.fp5 and add c Supply Name to the PetSupplies.fp5 portal, to the right of Supply ID. You should now have a simple layout that looks something like this:

The Climax
Now, if we’ve done everything correctly, you should be able to go into browse mode, click in the Pet ID field in the PetSupplies.fp5 portal, and select different supplies to associate with any pet! Half of the many-to-many relationship is complete ready to go. Now we need to set up a similar layout with similar functionality for Supplies.fp5.

1. Go into Supplies.fp5 and create a similar basic data entry screen (use a different color background, too).
2. Next, go into Define Relationships and define a new relationship to the PetSupplies.fp5 database with Supply ID highlighted on both the left and right. (Make sure to check “Allow Creation of Related Records” and “When Deleting a Record in This Database, Also Delete Related Records.”)
Click OK.

3. Add a portal to the data-entry layout using the relationship just created. Add the Pet ID field into it, and add the c Pet Name field you created earlier to the right of Pet ID.
4. Now define a value list in Supplies.fp5 that looks like the following and attach it to the Pet ID field in the portal to PetSupplies .fp5:

   

You should now have a simple layout in Supplies.fp5 that looks something like this:

Again, if it’s set up correctly, you should be able to go into browse mode, click in the Supply ID field in the PetSupplies.fp5 portal, and select different pets to associate with any supply you fancy!

Dénouement
Now that you’ve set up a basic many-to-many relationship, here’s one more handy technique that will help you make John’s or any client’s life much easier and make this solution much more powerful and easy to use.

Navigation
Wouldn’t it be nice if, say, you were sitting on the Albino Gerbil record and you wanted to quickly jump to the Chew Sticks records to edit it and then jump back to the Albino Gerbil record again? You can! Here’s how.

1. Go into PetSupplies.fp5 and set up two scripts.
2. Hop to Supply Part 2, defined as:
Go to Related Record ["Supplies by Supply ID"]

3. Hop to Pet Part 2, defined as:
Go to Related Record ["Pets by Pet ID"]

4. Now go into Pets.fp5 and create a script called Hop to Supply Part 1, defined as:
Go to Related Record ["PetSupplies by Pet ID"]
Perform Script [Sub-scripts, External: "PetSupplies.fp5"] (Select the Hop to Supply Part 2 script)

5. Last, go into Supplies.fp5 and create a script called Hop to Pet Part 1, defined as:
Go to Related Record ["PetSupplies by Supply ID"]

Perform Script [Sub-scripts, External: "PetSupplies.fp5"] (Select the Hop to Pet Part 2 script). Now all you have to do is go to the portal in both the Pet and Supply databases and add an icon or button to the right of the portal (as shown at left) and link the appropriate script to it. Now you can click back and forth between pets and supplies with ease.

Epilogue (Summing Up)
John, the fictional client, has asked us to sum up by expounding upon the tremendous advantages a many-to-many relationship gives to his database. Well, we can tell you that with this type of relationship, the possibilities are almost endless.

First of all, it is sound database design that won’t leave you duplicating records in a way that will drive you nuts. And you’ll be able to link many contacts to many projects, many products to many parts, many students to many classes, or many everythings to many everything elses. You can hop back and forth between all of these records with ease and in a way that is perfectly seamless to the user.

To add icing to the cake, you can do quite a bit of sophisticated reporting in PetSupplies.fp5 now that all the transactions that join the two original databases together are in one place. And you didn’t think wonderful client relationships were possible, did you?