3 Replies Latest reply on Jul 11, 2011 10:35 AM by philmodjunk

    Problem with synching two tables

    OlagGuld

      Title

      Problem with synching two tables

      Post

      Hello, all!

      I am absolutely frustrated at the moment. I would kindly appreciate any help possible!

      I have a problem trying to give a certain input that automatically creates a new record. There are 2 Tables. One is displaying the projects, whilst the other ones is displaying my contacts. The project table contains the contacts. 

      What I'm trying to do is to create the project in the project table and to input the contact information into this "project record". This should automatically lead to a new contact record in the "contact table" being created.

      Step two would have a 'related projects' field in the new contact record displaying the project whose new contact information just spawned that record.

      I tried to roughely visualize this here:

      http://i.imgur.com/SEQQT.png

      Basically, a new record in one table leads to a new record being created in the other table.

      Thank you in advance for any help!

        • 1. Re: Problem with synching two tables
          philmodjunk

          " The project table contains the contacts. "

          How's that again? The project table should contain the projects and the contacts table should contain the contacts. Perhaps that's a typo on your part?

          First thing to figure out is what kind of relationship exists between Contacts and Projects. Presumably, one contact can be linked to many projects, but is the reverse also true? Can one project list many contacts?

          You'll need to answer that question before you can work out the nuts and bolts of a relationship.

          Speaking about FileMaker databases and relationships in general, it is very rarely necessary to automatically create a related record in a second table the instant you create a new record in the first. It is usually cleaner and simpler to add the related records on an as needed basis through simple date entry operations that incidentally create the new related records for you as a result of your entering data into a portal row or by clicking a button.

          • 2. Re: Problem with synching two tables
            OlagGuld

            I want the project table to contain contacts because there are multiple contacts responsible for one project. I would still like to have a contact table however, as this would give me an overview about the projects a specific contact is involved in, because a contact can have multiple projects.

            The project table primarily contains the project data, of course.

            I will take a further look into portal rows as far as the related contacts are concerned.

            Thanks for helping out!

            • 3. Re: Problem with synching two tables
              philmodjunk

              You still should not put any contacts into the projects table. Instead, you need to link project records to contact records. Put the contacts data in contacts and the projects data in projects. You can then use the relationships between the two tables to display the contacts for a given project on the project layout so that you see both project and contact data combined in the same layout.

              This sounds like you have what we call a "many to many" relationship. Any given contact can be linked to any number of projects and any given project can be linked to a number of diffferent contacts.

              If this is so, you need a third table to manage the link between contacts and projects.

              Contacts----<Contact_Project>-----Projects

              Contacts::ContactID = Contact_Project::ContactID
              Projects::ProjectID = Contact_Project::ProjectID

              Contacts::ContactID and Projects::ProjectID would be auto-entered serial number fields.

              By placing a portal to Contact_Project on your contacts layout, you can produce a list of all projects for that contact. By placing a portal to Contact_Project on your Projects layout, you can produce a list of all contacts for that project.

              If you are unfamiliar with portals, you'll need to learn more about them first. Then you may want to examine this demo file that matches "companies" to "contracts" in the same fashion I am recomending you use for linking contacts and projects.

              http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html