2 Replies Latest reply on Jul 6, 2011 11:52 AM by vcdpp

    Getting primary field serial number to display in foreign key field in different table

    vcdpp

      Title

      Getting primary field serial number to display in foreign key field in different table

      Post

      Three days of FM Pro classes but this one is stumping me.

      My Company table has a primary key field that automatically assigns a unique serial number to each company.

      I want my contacts to reflect the company they work for, using that primary key as a foreign key, correct? When I put in a new contact I would like the company that contact works for to display in the contact field. I know I'm missing a key step -- how do I tell FM which company they work for?

      Here's a screen shot with my early start at relationships (bottom right), contact (left) and company (top right).

      Caroly works for CA Strawberry Assn. I manually put in the CA Strawberry Assn code but that seems wrong, and the Company Name (from Company table) doesn't populate.

      Help? Thanks!

       

      DPP_Database_question.png

        • 1. Re: Getting primary field serial number to display in foreign key field in different table
          philmodjunk

          First, remove __PK_Contact ID from this relationship. It should be defined as an auto-entered serial number and it should not be part of this relationship as it uniquely identifies each contact record and this has nothing to do with your link by Company ID to the Company table.

          Secondly, a new record in the Contacts database will not automatically receive a value in _Fk_Company ID unless you set the system up to enter it for you. There are several ways you can design your system to manage the link between Contacts and Company.

          1. Double click the relationship line between Company and Contacts. Enable "Allow creation of records via this relationship" for the contacts table. Now you can add a portal to contacts on the company layout. If you enter data in the bottom blank row of this portal, a new record will be created in Contacts and the current company record's __PK_Company ID will be copied into _Fk_Company ID.
          2. Format _FK_Company ID as either a drop down list or pop up menu of values from __PK_Company ID in the Company table. You can specify Company_Name as the field 2 value so that users can see the name of the company in the drop down list. Now, when you create a new record on your contacts layout, you can select a company from the company table and doing so links your contact record to the selected company.
          3. Use OnRecordLoad on the Company layout to run this script:

            Set Variable [$$CompanyID ; value: Company::__PK_Company ID]


            In Manage | Database | Fields, double click _FK_Company ID and give it this auto-enter calculation:  $$Company ID
            Now, if $$Company ID has a value, a new contact record will automatically enter that value.
            A similar script can be set in File Options to run when the file opens to assign a value to this variable when you first open the file.
          • 2. Re: Getting primary field serial number to display in foreign key field in different table
            vcdpp

            You ROCK!!!  I didn't understand or do the portal portion, but I did the reset and it works like a charm. Thanks so much!!!!!