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.
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.]
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 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.
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.
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
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.
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!
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.
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.
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?
File Maker Related Interview Questions
|Management Information systems Interview Questions||Software Engineering Interview Questions|
|File net Interview Questions||Data analyst Interview Questions|
|Hadoop Distributed File System (HDFS) Interview Questions||Linux File Systems Interview Questions|
|Excel Data Analysis Interview Questions||Asp Dot Net Database Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.