People Goal 1 - Part 9: Add your secondary data fields

Document created by Kedar on Jan 26, 2015Last modified by communitymanager on May 12, 2015
Version 23Show Document
  • View in full screen mode

 


Now that you’ve added your main list of data points to your main table, the next step is to go through a similar process for your secondary table.

First you need to add two ID fields: a
primary key and a foreign key.

Primary key field

You have already created a primary key for your main table. Since every table you create should have a primary key, you should create one in your secondary table as well. The process is exactly the same as in the main table: you’ll create an auto-enter serial number to ensure that your ID is unique.

Foreign key field

Like a primary key, a
foreign key is used for connecting tables together. It’s called a foreign key because its values originate as a primary key in another table (in this case, the main table). A matching value from a record in the main table is copied to a record in the secondary table whenever you want to establish a connection between the two.

While the values in a primary key field must be unique for each record, the values in a foreign key can repeat from record to record. Here’s why:

We’re going to assume that when you connect your main table to your secondary table, each record in your main table can be connected to more than one record in the secondary table. This is called a one-to-many relationship. Examples of these are:

  • One sales contact connected to many sales activities

  • One student connected to many enrollments

  • One donor connected to many donations


Imagine a specific record in your main table. It has a unique value in its primary key field — let’s say the value is 9. You want to connect three secondary table records to the one record in your main table. For each secondary table record that you want to connect, you put the value 9 in the secondary table’s foreign key field. 


FileMaker Pro compares the primary keys for every record in the main table, and the foreign keys for every record in the secondary table. When there’s a match, it creates a connection. In this case, it sees the matching values of “9” and establishes the connection between the one primary record and the three secondary records.

This is exactly the case in the example solution:

  • The Contacts table has a record for “Denala Gimpoor” where the primary key (“Contact ID”) is 9.

 

  • The Activities table has three records where the foreign key (also called “Contact ID” to emphasize that they match) is 9.


  • A connection is made between this one Contact record and these three Activities records.

In a later section, this is the sort of connection you will establish between your main table and your secondary table. Right now you just need to create the foreign key field.


 

Creation date field

You'll also add a "Creation Date" auto-enter field, to be used in Goal 5, "Automate with scripts". Auto-enter fields like this are indispensable for troubleshooting. The ones most commonly used for this purpose are Creation Timestamp, Creation Account, Modification Timestamp, and Modification Account.


Goal:

Add two ID fields to your secondary table, a creation date field, and then all your secondary data points.


Steps:

Create the primary key field

 

  1. Make sure you still have the Manage Database… dialog open.

  2. Select the Fields tab at the top of the dialog.
    pasted-image-7.png
  3. Make sure you are viewing your secondary table.

  4. Enter the value  [Secondary TableName] ID for the Field Name.

    In the example solution, this field is called “Activity ID”.

  5. Change the Type drop down to “Number”.
    pasted-image-2 copy.png
  6. Click the Create button.

  7. Click the Options button.

    An
    Options for Field… dialog will appear.
    Untitled 3.png
  8. Select the Auto-Enter tab.

  9. Select the Serial number checkbox.

    The serial number options will become available. The default settings are fine. 

  10. Click the OK button to save your changes.


Create the foreign key field

  1. For the Field Name, enter the same field name as the primary key in the main table.

    Don't worry if you are changing a value currently displayed in the Field Name.

    In the example solution, this field is called “Contact ID”.
    pasted-image-10.png
  2. Change the Type drop down to “Number”.

    Because the ID can repeat, we don’t want to set it up as an auto-enter field in this case. It will just be used to store whatever ID is needed to link to the appropriate Contact record.

    pasted-image-11.png

  3. Click the Create button.


Create the Creation Date field


  1. Enter the value Creation Date for the Field Name.

  2. Change the Type drop down to “Date”.

  3. Click the Create button.

  4. Click the Options button.


    An Options for Field… dialog will appear.

    The Auto-Enter tab will be selected. This is the one that you want.
    Untitled.png
  5. Select the Creation checkbox.

    The drop down is set to “Date” by default, due to the field type you entered. This is what you want.

  6. Click the OK button to save your changes.


Add your data fields

Now let’s create the data fields in your secondary table.  For each data point in your second list, do the following:

  1. Enter the Field Name.

  2. Select the Type.

  3. Click the Create button.



     

      

Attachments

    Outcomes