Filemaker 14 - Related tables

Hi there,

In an apartment building, a one key is related to only one apartment.

However, an apartment may have several keys.

I have two tables: Keys and Apartments

How can I link both tables so I can:
1) In the Keys layout:
a-Select what apartment a key belongs to
b-See what apartment a key belongs to

2) In the Apartments layout
a-I need to be able to select or create a new key that belongs to it.
b-List the keys that belongs to it.
c-Update the keys that belongs to it.

Any ideas?

Thanks for you help,
LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
You have what is called a one-to-many relationship from Apartment to Keys. To start, create a Number field called "ApartmentID" and have it Auto-Enter serial numbers (1, 2, 3, 4...) for each Apartment. This gives you a unique ID for each apartment that is not dependent on the number, address or description of the apartment and will not be affected if anything about the apartment's other fields is changed.

If you don't have and ID field already, you can add the field, and then use the Replace... Command under the Records menu to populate the existing records. For the purposes of creating relationships, it's actually better if the ID is simply an assigned serial and not in any way related to the descriptor fields that might be edited. After you have it populated, set it to "Prohibit Modification of value during data entry"

In your Key table add an identically named ApartmentID field but without the Auto-Enter. This is the "foreign" or "child" key which you relate to the "primary" or "parent" key in the Apartment table. Though you may not need it now, it's also wise to have a KeyID serial number field with a unique serial number for each key. They can also be numbered simply 1, 2, 3, 4...

The rest of your questions related to creating appropriate value lists and a portal in the Apartments table for viewing a list of all related keys.

List of apartments in the Key table. Create a value list that contains two fields, the ApartmentID and the Apartment number, address or other descriptor. Set it to show All Values and to sort by the second field. Set it to show ONLY the second value.

In a layout based on the Key table, assign this value list as  "Popup Menu" to the ApartmentID field in Keys, Keys::ApartmentID   When you click on it you should see a list of all apartments and by selecting the correct one you will be entering the Apartment ID of the selected one into the ApartmentID field on Keys, thereby creating a relationship between that apartment and that key based on the Apartment ID.

To see keys related to Apartments, go to a layout based on the Apartment table and add a portal using the relationship to Keys. Add the KeyID and KeyDescription (or KeyNumber or whatever you use) to the portal. When editing the relationship, click Allow the creation of new records on the key side of the relationship. This will allow you to go to the last row in the portal and enter a new Key using the KeyDescription field. that key will be linked to that apartment. (The KeyID field is an auto-enter serial number so there is no reason to enter or edit it. It's there for visual purposes only and you can turn off "Browse Mode" access in the Inspector while in layout mode.

That should get you started...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ReneGeAuthor Commented:
Hi Will,

I'm very impressed!!

I still have not implemented all of you solution but I understand its meaning.

If I have new questions in its regard, I'll create a new question.

Thanks for all you help :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.