4 Replies Latest reply on Jan 26, 2011 12:17 PM by philmodjunk

    Relationship problem? or how to autopopulate a field with info from another table's field?

    AnitaWoods

      Title

      Relationship problem? or how to autopopulate a field with info from another table's field?

      Post

      I am new to Filemaker, coming over from MS Access, and have a Contacts table and a Services table.  In Access, I had these tables related by a Contacts:ContactID related one-to-many to the Services:ContactID table, automatically populating the Services:ContactID field with the identical autonumber from Contacts:ContactID whenever a new Service record is created.  The Services table has a Name field that uses a Lookup to Contacts:Fullname (a concatenation of Contacts:Firstname & Lastname), and a field with the date the service was performed, Services:ServiceDate.  

      I have a References report that has the following fields: Services:Name, Services:ServiceDate, Contacts:Phone1, and Contacts:Email.  It works perfectly for records originally created in Access.  But new Service records do not automatically populate the Services:ContactID field, so the Phone1 and Email fields do not appear.  I've tried to relate them through the Contact:FullName and Services:Name fields, but when I do that all the names & phone numbers and emails are incorrect.  Is this a relationship problem?  Can someone help me with what I am doing wrong?  I think I could get this to work if I could get Services:ContactID to automatically store the Contacts:ContactID from the Services:Name field, but how do I do that in FM?  Thanks for your help.

        • 1. Re: Relationship problem? or how to autopopulate a field with info from another table's field?
          philmodjunk

          You need this relationship:

          Contacts::ContactID = Services::ContactID 

          where Contacts::ContactID is the primary key for your Contacts table and is set up as an auto-entered serial number.

          "Allow creation of records via this relationship" should probably be enabled for Services in this relationship.

          If you do, you can add a portal (similar to an Access sub form or sub report) to Services on your Contacts layout and add new related services records simply by inputting data on the bottom blank row of your portal. FileMaker will automatically copy the contct ID number from the current contacts record into the contactID field of this related Services record.

          You can also set up a value list of Contact ID's on a services layout for selecting a contact for a given contact if you want to create new records directly on such a layout.

          There are other ways to link services and contacts records, but these are the basic approaches you'll use most of the time.

          Oh yes, and you don't actually need that name field in the services field set to look up the full name field from contacts. On your Services layouts, you can simply add the related full name field from the contacts table.

          • 2. Re: Relationship problem? or how to autopopulate a field with info from another table's field?
            AnitaWoods

            OK, I changed all the relationships that use ContactID to Contacts:ContactID = Services:ContactID (I also have two other tables, one Mileage tracking and another Accounting, all that use the ContactID field with an = relationship).  I also made Contacts:ContactID an auto serial number, strict data type numeric only, and unique value.  (Is there a place to call it "primary key"?)  I gave each table an autonumber field the same way.  

            PhilModJunk said "add a portal... to Services on your Contacts layout and add new related services records simply by inputting data on the bottom blank row of your portal."

            Yes, this is exactly what I had done.  The References Report is now functioning correctly after doing everything you stated as well as correcting the empty Services:ContactID (and the other related tables) and making it autonumbered.  But the Services:ContactID will not automatically store the related Contacts:ContactID.  Without doing that, new records will have a broken relationship and it doesn't work.  Any other ideas?

            Also, using the related field name from the Contacts table is not editable, so new records cannot be created by choosing Contacts:Fullname in the Services table.

            • 3. Re: Relationship problem? or how to autopopulate a field with info from another table's field?
              AnitaWoods

              I should also add that not every Contact has a record in the Services table, and some Contacts have more than one record in Services.  IOW, some Contacts were potential clients that never hired me, and some Contacts have hired me more than once.

              • 4. Re: Relationship problem? or how to autopopulate a field with info from another table's field?
                philmodjunk

                But the Services:ContactID will not automatically store the related Contacts:ContactID.  Without doing that, new records will have a broken relationship and it doesn't work.  Any other ideas?

                This does indeed work, been using this feature for years. Check to make sure that the relationship between contacts and services is enabled for "allow creation of records via this relationship" for services. That's the most likely omission. Of course, this assumes thta Services::ContactID is linked to Contacts::ContactID and that both contactID fields are the same data type.

                Also, using the related field name from the Contacts table is not editable, so new records cannot be created by choosing Contacts:Fullname in the Services table.

                Actually, it IS editable, but you must first create the related services record so that you have a valid relationship back to that contacts record. You'd create your portal's new related record by entering data in a field that is defined in Services. You wouldn't want to edit a name field that is defined in Services and that looks up its value from contacts. Any changes you made would not appear in Contacts, nor would they appear in any additional records you created in services.