2 Replies Latest reply on Jan 24, 2011 9:19 AM by AmyHuras

    How to make a link between records in the same database

    AmyHuras

      Title

      How to make a link between records in the same database

      Post

      I am new to Filemaker Pro and I am trying to make a database to organize my research notes for my thesis (I am working with version 11). So far my database has 4 tables containing records for different types of information - like notes from archival sources, secondary sources, printed primary sources etc. I have similar categories in most of these tables so I've created relationships between them so that I can search/display records on similar topics. So far that has worked fine.

      But this project is really big and I'd like to be able to link some of my records directly to other records (both in the same table and in different tables) because they go together as a sort of case study. So I am hoping to be able to put a hyperlink on any given record that I could click and it would take me to a related record of my choosing. I would want to be able to manually choose which records to link to each other. I am not sure how to do this. I've tried to read up on it, and so far I think I need to make sure that each record in my database has a unique auto-entered Serial ID field - so I've set that up. But I don't know what to do next.

      Thank you kindly in advance for your help.

        • 1. Re: How to make a link between records in the same database
          philmodjunk

          To relate your main table to a record in a different table:

          In Manage | Database | Relationships, Drag from your serial ID field in your main table to a matching ID field (not set up as a auto-entered serial number) in the other table. This should create a line linking the two. You can now place a portal on a layout based on one table and use it to display related records from the other table.

          To relate your main table to a record in the same table.

          In addition to your serial number field, define another number field named "RelatedID". Go to Manage | Database | Relationships and click on the table occurrence "box" for your main table. Click the button with two green plus signs near bottom left of this window. This makes a new table occurrence of your main table. Drag from your serial number field in the first box to the RelatedID field in the second. Now you can add a portal to this new table occurrence just like you would to show related records from any other table.

          "So I am hoping to be able to put a hyperlink on any given record that I could click and it would take me to a related record of my choosing"

          You can place a button on your layout or format a field on a layout to perform this script to find a related record in the same table:
          Set Variable [$ID ; value:  YourTable::RelatedID ] 
          Enter Find Mode[]
          Set Field [YourTable::YourSerialIDField ; $ID ]
          Set Error Capture [on]
          Perform Find []

          If the record is in a different table:

          Set Variable [$ID ; value:  YourTable::YourSerialIDField ] 
          Go To Layout [//select layout based on related table]
          Enter Find Mode[]
          Set Field [RelatedTable::YourIDField ; $ID ]
          Set Error Capture [on]
          Perform Find []

          These are just a few out of many possibilities and works for a one to one relationship in the same table or a one to one or one to many relationship in a related table.

          For linking to many records in the same table or a many to many relationship with a related table, more changes would need to be made to your database structure.

          • 2. Re: How to make a link between records in the same database
            AmyHuras

            Thanks, you've been really helpful. I have changed my database around a little bit so that now all of my records are in the same main table - it seems to have simplified things for me. I followed your instructions on how to relate records from the same table and I've displayed the related records using a portal. So far it seems to be working and it lets me relate multiple records. Many thanks!