11 Replies Latest reply on Aug 23, 2010 10:18 AM by philmodjunk

    Linking Records

    Mitch

      Title

      Linking Records

      Post

      I have a database that has records of a large number of people.  I would like to link two people (two records) on the layout.  A visual link on one records layout to a related records layout and means of switching between the two.

      Mainly because they are relatives, or some other relationship.  

      My first attempt was to create a link field (LinkID), with a drop down list (lookup) where all Surnames where listed along with the automatically generated ID for the record, RecordID. Where I selcted the related record.

      Then create a script that uses a variable to open the related record:

      Set Variable [$LinkID ; Value:Main_Page::LinkID]

      Got to Related Record [Lookup (Main_Page::RecordID ; $LinkID)]

      However, its does not function. The script selects the correct variable but wants to open a record where the $LinkID and the Record Number (@@) are the same.... 

      One answer would to replace RecordID with Record Number.  But record numbers changes with every deletion and vary and the Link will change to an unrelated record.  Automatically generate ID do not.

      I am obviously on the wrong track....

      Does anyone have some ideas, I am sure it's been done before...

        • 1. Re: Linking Records
          philmodjunk

          And record ID's change arbitrarily when you import the records into a different file--say after a file has been damaged or when you deploy an updated version of the file.

          You need a field setup as an auto-entered serial number field to serve as the primary key identifying each individual.

          You then need to establish a relationship in Manage | Database | Relationships if you plan to use Go To Related Records to find the other, related record.

          How you set up this relationship depends on what you are trying to do. If you need to link any number of people to any number of other people in the table, then you'll need an additional table to manage the "lnks" between your individual records. If you just need to link 1 person to 1 other person you won't need the extra table.

          For the second option, linking to 1 other person only, do this:

          Define your serial number field--I'll call it PersonID. If you have existing records, Use Replace Field Contents and its serial number option to assign serial numbers to your existing records.

          Define a second number field, RelatedPersonsID.

          Open Manage | Database | Relationships and drag from PersonID to outside the box and then back to RelatedPersonID before releasing the mouse button. This will create a second table occurrence of your table that's needed to define this relationship.

          To link a person record to another person record, you'd format the RelatedPersonID field much as you described in your original post but set it up to enter the PersonID value of the other record to establish the link.

          You can place fields from your second table occurrence on the same layout as where you have your drop down and the data will automatically appear for a give person when you select their ID. You can also use Go To Related Records to bring up this person as long as you specify the parameters correctly.

          For more on Table Occurrences see this thread:  Tutorial: What are Table Occurrences?

          For more about Go To Related Records, see this thread:  The Complete Go To Related Record

           

           

          • 2. Re: Linking Records
            Mitch

            I have named both the new serial number field (PersonID) and the relation link (RelatedPersonID) as you have suggested.  Set up the relationship and it all works.  As you have also suggested, I have add to GTRR function using the PersonID and RelatedPersonID link. Again, works well.  It's now a simple task to make a link to another record, go to the linked record and make the return link.   Using the GTRR merely swaps between the two related records.

            Thanks very much.

            This lead me to the last function I would like to include here....

            The ability to delete a given record, then if linked, automatically delete the deleted records PersonID in the related records RelatedPersonID's field... Eliminating the two way link.

            My first thoughts were to establish a new table, with a single global field 'gLinkID' and appropriate relationship.  Writing a script which first checks if the RelatedPersonID in not empty.  Then, prior to delete, the deleted files PersonID is copied to the gLinkID.  The record is deleted, then Find/Replace searches for the gLink value in RelatedPersonID field for all records, then deletes it or sets it to a null value. 

            I have not got it working so far...

            • 3. Re: Linking Records
              philmodjunk

              Write a script like this:

              Show Custom Dialog ["Do you really want to delete record for " & MainPage::Name & "?"]
              If [Get (LastMessageChoice) = 1 /* OK button was clicked */ ]
                     Freeze Window
                     Set Variable [$RelatedID ; Value: MainPage::PersonID ]
                     Enter Find Mode []
                     Set Field [MainPage::RelatedPersonID ; $RelatedID ]
                     Set Error Capture [On]
                     Perform Find []
                     If [ Get ( FoundCount ) > 0 ]
                             Replace Field Contents [No Dialog ; MainPage::RelatedPersonID ; "" ]
                     End If
                     Enter Find Mode[]
                     Set Field [MainPage::PersonID ; $RelatedID]
                     Perform Find []
              End If

              You can place a delete button on your layout that runs this script and if you have filemaker advanced, you can even create a custom menu for your layout that substitutes this script for the normal delete record action when delete record is selected from the Records menu.

              • 4. Re: Linking Records
                Mitch

                That all worked.  Really well.  Thanks.   I am going to add a few more functions to the whole 'linking records' capability.  Perhaps when you establish the first link, it automatically establishes the return link.

                I take it that to also delete the record (Person) plus both the link ID's, it would just require a delete record somewhere in the script above...? Or will that prevent the remainder of the script from working?

                Mitch

                • 5. Re: Linking Records
                  philmodjunk

                  Oops, yeah you'd include a delete record as the last step inside the If block.

                    Perform Find[]
                    Delete Record [no dialog]
                  End If

                  • 6. Re: Linking Records
                    Mitch

                    Is there the ability to also delete the two $PersonIDs in the two records, but also a second field in both records that you can details the reason or type of relationship between the two records...

                    Such as :  MainPage::Relationship that has a drop down list with values:  Parent, Spouse, Sibling....

                    • 7. Re: Linking Records
                      philmodjunk

                      Certainly, you'd need to structure your script to perform a series of Finds followed by a replace fields contents step to clear the fields. I've used Replace Field contents so that you can clear the fields of all the found records in one step.

                      Note that this script should not be used if the file is shared over the network and there is any chance that another user might be editing a record that is also being modified by the replace field contents step. If that happens, you'll get an error message that one of the records could not be modified, but the error message won't tell you which one wasn't changed.

                      • 8. Re: Linking Records
                        Mitch

                        Just a question, the script:

                         If [ Get ( FoundCount ) > 0 ]
                                       Replace Field Contents [No Dialog ; MainPage::RelatedPersonID ; "" ]

                        Obvious finds all record that have an RelatedPersonID value of greater than 0, then replaces the value with Null.  Therefore this replaces all RelatedPersonID values with Null in all records.

                        What if, I wanted to only replace the RelatedPersonID that is the same as the $PersonID.   Therefore only replacing one record with a Null value leave all other records untouched...?

                        • 9. Re: Linking Records
                          philmodjunk

                          It only affects those records found, which would be all records with the PersonID from the original record entered in the RelatedPersonID.

                          That allows the script to work with 1, 2 or however many records had this value in that field.

                          If you just want to change the value of the current record, you can use Set Field [ MainPage::RelatedPersonID ; "" ] instead.

                          • 10. Re: Linking Records
                            Mitch

                            I could not get that to work..... and then whilst lying on the couch watching an old western (Colt .45 with Randolph Scott to be exact) it came too me.... then scritp was in the wrong spot!

                            • 11. Re: Linking Records
                              philmodjunk

                              Yeah,

                              One of the things that can trip you up is that every script in FileMaker is inherently layout sensitive. You have to keep track of what record, foundset, layout and sort order is "current" at any given point in your script as this might affect the results you get.

                              This is where the added cost of Filemaker Advanced becomes worth every penny as you can run a problem script with the debugger and data-viewer enabled and watch every step of the script execute while also being able to track the "context" current at each point.