Hello all --
I'm using FileMaker15 Pro and am looking for some assistance in creating a simple(?) relational database, which I just can't seem to get working. It's a simple contact management system.
For each company, there can be multiple addresses. For each company there can be multiple contacts. A contact can only work for one company at one location.
I'd like to be able to have a contact entry point which will let me select the company he works for with a drop down list, but also lets me create a new company if it's not already in the company table. Then, select the location from that company next, then have FileMaker automatically fill in the contact's address fields with the data from that location (also want the ability to add a new location on the fly as well.)
I think that means three tables: Company, Address and Contact. The company would just have one field, the company name. The Address table would have company,, addressID, street1, street2, city, st, zip, etc.. The Contact table would have company, addressID, street1, street2, city, zip, phone, etc. I'm not sure how to set up the relations so it all works.
Here's an example:
ABC company has two addresses: One, say ID "NYC", is at 456 Seventh Avenue, New York, NY 10000 and the other, say ID "LA" is at 777 Wilshire Blvd., Los Angeles, CA 90000.
American Eagle also has two addresses: One, say ID "Stamford", is at 200 Fifth Avenue, Stamford, CT 06900 and the other, ID "Boston" is at 444 Charles Street, Boston, MA 03800.
I want to add a new contact. I add First Name John, Last name Doe. Then, I want a drop down list for company which will give me a choice between ABC and American Eagle, or add a new company. Let's say I choose ABC.
Then I have another drop down list for address, where I can select "NYC" or "LA" or add a new location for ABC. FileMaker then populates the contact's address fields based on that address.
If John Doe switches companies, I can select, for example, American Eagle from the list, then the address choices drop down would change to "Stamford" or "Boston" or the ability to add a new location for American Eagle.
Any help, sample files, places to look for answers, etc., would be greatly appreciated.