People Goal 6 - Part 4: Set foreign key for secondary table

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

 


Now that your data has been imported, it’s time to make the manual connection between the main table records and secondary table records that was discussed in Part 2.

 

Here is an outline of what’s involved:

 

  • Create a new temporary table occurrence for your main table
.

  • Join it to your secondary table occurrence though a relationship using the spreadsheet field(s) you identified in Part 2,  “Prepare for Relationships”.


  • Use this relationship to copy the primary ID field from the main table into the foreign key field in the secondary table, associating your imported secondary table records with the correct primary table records.


  • Double-check that the process really worked as expected.



This process is complex enough that you may want to try following the instructions using the example solution first, just to make sure you understand all the concepts. We’ve provided example data files for this purpose in the “Test Import” folder of your example files.

 

 

 

Goal:

 

Populate the foreign key in your secondary table by copying values from a new table occurrence of your main table, connected by a temporary relationship that uses data from your spreadsheets.

 

 

Steps:


Set up the temporary relationship


  1. Choose the File menu > Manage > Database…  ⇧⌘D

  2. Select the “Relationships” tab at the top of the window.
    pasted-image-9.png

    You should see a table occurrence for each table you created in your file, joined together via one relationship. Here is the example solution:
    pasted-image-10.png

  3. Add another table occurrence for the your main table and name it: [Main Tablename]_temp

    In the example solution, it's named “Contacts_temp”.
    pasted-image-11.png

  4. Join your “[main table]_temp” occurrence to your secondary table occurrence using the field(s) you determined would create a good match.

    For the example scenario, we are matching by email address and are assuming the work email is consistent and unique.
    pasted-image-12.png

  5. If you need to change the match fields for the relationship, click on the “=“ box.

    An Edit Relationship dialog window will appear.

  6. Make any changes necessary to the relationship criteria.

    You don’t need to change any of the other relationship options.

  7. Click the OK button to dismiss the Edit Relationship dialog window.

  8. Click the OK button to dismiss the Manage Database… dialog window.


Populate the foreign key in your secondary table

Now you need to populate your foreign key field making use of this relationship. You’ll use the Replace Field Contents command for this.

  1. View your original secondary table layout in Layout mode.

    This is the layout that was created automatically by FileMaker Pro when you added your secondary table. In the example solution it’s called “Activities”. Consider viewing this layout in Table View or List View so that you can see more than one record at once.

    Your found set should still include only those records that you just imported.

  2. Add two new fields to your temporary layout:

       - the ID field for your secondary table

       - the ID field for your main table, viewed through your new temporary relationship

    In the example solution, these two fields are:

       - Activities::Contact ID

       - Contacts_temp::Contact ID

  3. Give these two fields different fill colors to make them easier to recognize in Table View.
    pasted-image.png


  4. Switch to Browse mode.

  5. Switch the layout to Table View.

  6. Place the cursor in the ID field for your secondary table.

    This is “Activities::Contact ID” in the example. You can see the ID field outlined in blue below.


    pasted-image-2.png

  7. Choose the Records menu > Replace Field Contents…  ⌘= or Ctrl =

    A Replace Field Contents dialog window appears.

    replace field contents.png

  8. Click the Specify button next to the Replace with calculated result option.

    A Specify Calculation dialog window appears.

  9. In the large empty box, enter an expression for the ID field in your main table as viewed through the temporary relationship you just made.

    In other words:    [table name]_temp::[table name] ID

    Or in the example solution:    Contacts_temp::Contact ID

  10. Click the OK button to dismiss the Specify Calculation dialog window.

  11. Click the Replace button in the lower right of the Replace Field Contents dialog window.

    The Replace action occurs. You should see ID values appear in your foreign key field, at least where you have a match to your main table by way of your temporary relationship.

    pasted-image-3.png
  12. Switch to your main detail layout.

  13. Confirm whether you are seeing the expected related records appear in the portal on this layout.

    If you are, you’re ready to test things more thoroughly in Part 5.

    If you aren’t, you may want to try a different temporary relationship, or at least explore why your temporary relationship doesn’t seem to be giving the results you expect.



      

      

Attachments

    Outcomes