10 Replies Latest reply on Aug 14, 2013 10:48 AM by CourtneyTran

    Creating Links Between Related Donors' Records

    CourtneyTran

      Title

      Creating Links Between Related Donors' Records

      Post

           I would like to be able to create a means of generating "links" between donor profiles for donors who have relationships. Not just marriage or family relationships. For example, if I have donor A and donor B who are married and if donor C happens to be the priest who wedded them, then I want to be able to go into say- donor A's profile- and see some sort of note indicating this relationship to donor C and B, with a link to donor C & B's profiles available. I would also want this relationship to be shown in donor B & C's profile as well. 

            I am not completely certain of the scope of Filemaker's ability to do this sort of thing, but ideally, I visualized something such as a diagramatic representation of these relationships, with bubbles and lines that indicate a relationship from one donor to the next. The links to these donors' profiles will be shown within these bubbles.

           I am largely at the brainstorming stage and this is an idea I had. I am about to start researching it but if there is anyone who can give me input on the feasibility of this idea or tips on how to make a similar thing happen, I'd really appreciate it!

           I have attached a photo of the sort of thing I have imagined.

      DiagramaticScreenshot.png

        • 1. Re: Creating Links Between Related Donors' Records
          philmodjunk

               This is basically just another form of many to many relationship where you create a record in the join table that documents both which two individuals are linked, but also the nature of that relationship. An added detail here is that it's a many to many "self join" relationship.

               Donors-----<Donor_Relationships>------RelatedDonors

               Where RelatedDonors is another occurrence of the Donors table.

               In your example where you document the fact that Donor A and Donor B were married by Donor C, you'd create two records in Donor_Relationships, one that links Donor A to Donor C and one that Links Donor B to Donor C. A third record would link Donors A and B to each other as spouses.

               A field in Donor_Relationship would then document the nature of the relationship, Priest, wife, husband, etc...

          • 2. Re: Creating Links Between Related Donors' Records
            CourtneyTran

                 What do you mean by creating a record that links donors A to donor B, etc.? I don't really understand.

                 How do I create a record that shows a link between two donors? Is this a visual link? I am having trouble visualizing what you mean.

                  

            • 3. Re: Creating Links Between Related Donors' Records
              philmodjunk

                   Donor A (_pkDonorID = 543 )  is the wife of Donor B ( _pkDOnorID = 678 )

                   To show that Donor A is the wife of Donor B Create this record in Donor_Relationships:

                   _fkDonorID: 543
                   _fkRelatedDonorID: 678
                   Relationship: Wife.

                   To show that Donor B is the husband of Donor A:

                   _fkDonorID: 678
                   _fkRelatedDonorID: 543
                   Relationship: Husband.

                   Many to many relationships typically are implemented with portals to either the join table (Donor_Relationships--which enables creating or removing links by creating or removing join table reocrds ) or to the other related table (Which just lists all related records ) in order to list all linked records and details about each of those linked records such as the type of relationship.

                   You may find this demo file on many to many relationships helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

              • 4. Re: Creating Links Between Related Donors' Records
                CourtneyTran

                     Oh this makes sense. Between your explanation and this web page I found: http://help.filemaker.com/app/answers/detail/a_id/9922/kw/records%20from%20another%20table

                     ... everything becomes clearer. Thank you.

                     How do I create the crow's feet for this one-to-many relationship? I created two tables and a join table. I made the three tables:

                     1. Donor

                     2. Donor_Relationships

                     3. Related Donors

                     When I draw the links between the tables, Filemaker automatically generates solid lines with no crows feet to indicate a many to many relationship. Why is this happening?

                     Screen shot attached.

                • 5. Re: Creating Links Between Related Donors' Records
                  philmodjunk

                       That should happen automatically. It is controlled by the field options you specify for the fields.

                       Donor ID should be an auto-entered serial number.

                       _fkDonorID and _fkRelatedDonorID should be number fields with no othe fields options specified. The single "connector" suggests that you have either defined them as auto-entered serial numbers or have specified the unique values validation. Both options are incorrect here and should be removed.

                  Donor ID_RelatedDon... is a field that should not exist. It should be the very same field from the very same data source table as Donors::Donor ID.

                       You create RelatedDonors by clicking Donors in Manage | Database | Relationships and then you click the duplicate button (two green plus signs) to make a copy of the Donors Tutorial: What are Table Occurrences? box. You can then double click this box to open a dialog where you can rename it.

                  • 6. Re: Creating Links Between Related Donors' Records
                    CourtneyTran

                         Hello, I thought I created the relationships properly so that the join table would work properly when I created a portal table.

                         I went ahead to test it and this is what happened:

                         I went into my "Donor Relationships" layout and created a record that indicated that donor A was married to donor B.

                         I expected then that, when I go to donor A's profile, the portal table for "Donor Relationships" would show that donor A was related to donor B... and it did.

                         I also expected that, when I go to donor B's profile, the portal table for "Donor Relationships" would show that donor B was related to donor A... but it showed nothing.

                         I have attached a depiction of how I drew the relationships.

                    • 7. Re: Creating Links Between Related Donors' Records
                      philmodjunk

                           Donor full name and related donor full name should be removed from the join table. you don't need them. The relationships linking a given join table record to two donor records by Donor ID are sufficient to be able to access and display the Donor and related donor names from the donor table.

                           You are correct that if you relate donor A to donor B that you will not see that relationship when accessing the donor B record. Please refer to my previous post where I show creating one join table record to show that B is the wife of A and another join table record to show that A is the husband of B.

                      • 8. Re: Creating Links Between Related Donors' Records
                        CourtneyTran

                             Is it possible to make it so that, I can click on the related donor's ID number and that will link me to opening that donor's record for viewing?

                              

                        • 9. Re: Creating Links Between Related Donors' Records
                          philmodjunk

                               Go to related records can be used for that purpose.

                          • 10. Re: Creating Links Between Related Donors' Records
                            CourtneyTran

                                 You're my HERO PhilModJunk!!!! HERO HERO HERO.