11 Replies Latest reply on Mar 19, 2012 10:02 AM by Stephen Huston

    Self-Join to pull Common Data between Family Members

    tshramek

      I am developing a Health Records database for my Optometry Practice, and would like to address the following issue:

       

      It is very common to see multiple members of the same family. It is simple enough to create and duplicate records initially to ensure a match on the address (and other data in common). However, as families move, or insurance changes, I would like to have a easy way to update everyones' common info...without doing a search for all people in that family, and manually updating a single field and then copying it to that found set. While this method works adequately well it is open to user error (especially if they copy a field across a incorrect found set).

       

      Ideally, I would like to have a "Acct Responsible" field that is simply a self-join using the primary key as a foreign key. Then once that is populated, have it pull the data from the individual that is referenced as the "Acct Responsible" and place that persons address, phone, etc in a second set of fields.

       

      This would allow me to potentially have a different address for children as they go off to college, but still have the parent info on their file; or to just create a copy/paste script to change all the data to match the “Acct Responsible” info on the patient record.

       

      Any suggestions would be greatly appreciated. If there is a more functional or elegant way to address this issue I am VERY open to suggestion.

       

      Thanks,

       

      Troy

       

      P.S. Have attached a file that hopefully makes it more clear as to what I am trying to accomplish. It works fine if you relate back to the same record, but can't get it to work when trying to relate to some other record.

        • 1. Re: Self-Join to pull Common Data between Family Members
          comment

          First, make sure your matchfields are of the same type (in this case, if you want to use leading zeros, they need to be Text). Then turn indexing off and back on again, to re-index the fields. After that, it should work as expected.

           

          BTW, why not use a parent Families table instead of a self-join?

          1 of 1 people found this helpful
          • 2. Re: Self-Join to pull Common Data between Family Members
            bumper

            I think you have your relationship backwards. Try using the primary key from the Acct Responsible table to the foreign key in Demographics. It's a one to many, but if you look at your Practice file Relationships the one is on the demographics side and the crow's feet are on the acct resp side which is the opposite of what you are trying to accomplish. 

            • 3. Re: Self-Join to pull Common Data between Family Members
              Stephen Huston

              This sounds like a different schema would work better than you described.

               

              Parent Record: Family (common address, even if only 1 family member)

              Child Records: Individuals (every individual would be linked to a single Family record, even if no other individuals/members. No address or "common" data fields go into the Individuals table.

               

              Then there is only one place to store any family address, and all individuals have a family record which they may or may not share with other individuals. You use real primary/foreign Key fields (code, not meaningful data) to link these, not names or addresses.

              1 of 1 people found this helpful
              • 4. Re: Self-Join to pull Common Data between Family Members
                tshramek

                Thanks, I made sure both matchfields were the same (i.e., text due to leading 0's); and re-indexed.  While this did not resolve the exact issue I was having (came to learn I had the relationship backwards as bumper indicated), I am sure it prevented other unforeseen issues down the road.

                 

                Thanks!

                • 5. Re: Self-Join to pull Common Data between Family Members
                  tshramek

                  This solved my problem.

                   

                  Thanks!

                  • 6. Re: Self-Join to pull Common Data between Family Members
                    tshramek

                    Your schema was my original intent. 

                     

                    Then I saw a video on Lynda.com regarding self-joins and wanted to experiment and push myself a little. 

                     

                    I appreciate your suggestions and input, and if you have time maybe you could elaborate as to the advantage/disadvantages with your schema vs a self-join as I have it now in the attached file.

                     

                    Thanks!

                    • 7. Re: Self-Join to pull Common Data between Family Members
                      bumper

                      If you are wanting to see the children records listed in a portal, then create a layout from the context of the Acct Responsible, and create a portal of Demographic records, you can then create a Go to related records button in the portal to take you to the desired child's layout in Demographics. You can script a find to only show the Acct Responsible, which reduces the number records from that side of the equation.

                       

                      Note that under a more traditional form of related databases, where one was planning for multiple addresses (or phone numbers, etc) you might create separate tables for addresses and phone numbers, so that you could "share" the common address between the family members, which can be important if they all move to a new house, so now staff only has to change the one address and it is changed in all the family records, but still include Dad's work address and only link it to his record. But then you can run into a tad more difficulty with managing the records.

                       

                      The really nice thing about FMP is you can start small and build it out as you go.

                      • 8. Re: Self-Join to pull Common Data between Family Members
                        Stephen Huston

                        Your sample file was clearly stripped down quite a bit from what you would have in a real soution.

                         

                        The advantage is that when you put back in all of the various fields youa re going to need, you will probabaly have many dozens of fields, bordering on a "wide table". This means that caching the records across a network increases network latency significantly, and can visisbly slow system performance.

                         

                        The narrower a table (fewer fields) the less data must be cached, and the faster network speed. Shared data, if cahced at all, will cache faster than the same dat replicated in every record.

                         

                        It's a matter of optimizing your system for network performance. It may not seem significant at the beginning with a few hundred records, but any decent system needs a long-term life-expentency. Our in-house system has some tables with nearly a half-million records, and having only 20 fields makes a huge difference in list view refreshes compared to 100 fields.

                        • 9. Re: Self-Join to pull Common Data between Family Members
                          tshramek

                          Thanks for the various ideas.  My biggest concern is laying a solid foundation, so that I can change things over time...which as you indicate is a strength of FMP.

                          • 10. Re: Self-Join to pull Common Data between Family Members
                            tshramek

                            While I don't think I will ever have more than 20-30k records, I still want to design in a manner that would be most efficent.  I had not considered the benefits of a 'narrow table' as you described.  Thanks!

                            • 11. Re: Self-Join to pull Common Data between Family Members
                              Stephen Huston

                              Getting the optimal data structure at the beginning makes life easier all the way along.

                               

                              Changing a data structure after everything's live can be one of those PITA things. Plan early for best results later.