10 Replies Latest reply on Nov 18, 2014 7:35 PM by wellman_jill

    relationship graph for directory application


      The app is directory for a small community.


      One or more people (TOC) live in a unit (TOC) . Both tables containe a unitID which is the key field for the unit table

      and a foreign key for the people who live in the unit.

      For a unit layout, the people who live there are shown in a portal.


      A Listing TOC should show resident information alphatized by last name

      if one or more people with the same last name live in a unt, then they have one listing, e.g.,

      Doe, Jane and Robert

      If two or more people with different last names live together in one unit, they require two listings, e.g.,

      Smith, Jane and Jone, Bill

      and also

      Jones Bill and Smith Jane


      Thus any resident can be looked up by his or her last name.


      A unique key for Listings is unidID&lastName



      I can't figure out how to create the relationship(s) between Listings, people, and units.



      I hope someone may have a solution or pointers.


      Thanks, Jill

        • 1. Re: relationship graph for directory application

          Hello, Jill. Interesting problem!


          Let me restate to make sure we're on the same page. You have these tables:


          People (each record represents a person)

          Units (each record represents a living unit - apartment, condo, house, whatever)

          Listing (each record represents a line item in a directory)


          Assuming that's correct, let's divorce ourselves from the concept of relationships for a moment and think about the actual problem. If I have one record per person in People, how do I get FileMaker to show one record per group of people who all live together in the same Unit? You can't do that with a direct relationship between Unit and People, because I'll get one portal row per person - not what I want. So somehow, I have to get the Listing table populated with one record per listing - per group of colocated people. And, to add interest, I actually need more than one Listing record in those cases where the last names don't match - I need one Listing record per last name.


          Therefore, our problem isn't one so much of how to build a relationship, but how to populate the Listing table. (The relational joins come later.) I need to group people by last name and create one record per last name per unit. There are a couple of ways to approach the problem:


          1) I can use a unitID field in the Listing table and join it to the unitID field in the People table. Then, by using the List ( ) function and a concatenated Name field (LastName & ", " & FirstName), I can process through the list of all the people who live in that unit. That list might look something like this:


          Doe, Jane

          Doe, Robert


          I can then tell FileMaker to loop over the list and determine where the last names are the same (or different) and create my needed name calculations, inserting them into new Listing records as needed.


          2) I can use the ExecuteSQL ( ) function to extract first and last names from the People table where the unitID field matches any unitID I want. (Or, alternatively, just fetch them all and loop over them.) That might look something like this:


          Let ( [

          sqlQuery = "SELECT p.lastName, p.firstName, p.unitID FROM People p ORDER BY p.unitID, p.lastName"

          ] ;


          ExecuteSQL ( sqlQuery ; ", " ; "¶" )




          If I run that calculation, it would give me a text result that would look something like this:


          Doe, Jane, 1

          Doe, Robert, 1

          Jones, Bill, 2

          Smith, Jane, 2


          And again, you could loop over the result to give you the necessary breakpoints.


          But the operation basically boils down to a scripted process. You'd need to run a script whenever you created or deleted (or updated) a Person record to have FileMaker update the corresponding Listing record(s). This can be done either through interface conventions (i.e., buttons) or through Script Triggers.


          Once the Listings table is populated, it's a relatively simple matter to create a relationship from unitID in that table to unitID in the Units table and display the corresponding listings.


          I hope that makes sense. If not, please squeal.   



          1 of 1 people found this helpful
          • 2. Re: relationship graph for directory application

            Mike_Mitchell wrote:


            Hello, Jill. Interesting problem!


            Here's another way to do that – though I wonder what is supposed to happen when there are more than 2 people in one unit where some, but not all, have the same last name  …

            1 of 1 people found this helpful
            • 3. Re: relationship graph for directory application

              erolst raises a valid point. In today's world you will need to consider children of a previous marriage with a hyphenated name, etc. The point is that you can never accommodate all of the possibilities.

              You might consider the data entry to include spouse first and spouse last fields. Then a calc field in that record can look something like this:

              Case(IsEmpty( b_Spouse First); b_First & " " & b_Last;


              b_Last = b_Spouse Last; b_First & " & " & b_Spouse First & " " & b_Last;


              b_Last <> b_Spouse Last; b_First & " " & b_Last & "  &  " &   b_Spouse First & " " & b_Spouse Last )


              This ignores kids, aunts or any name beyond the two principles. If you want to search for every living sole in a unit then you would do it differently. A self unitID relationship would populate a dedicated search field with all the last names attached to a unit. There are tons of methods to present the user with the search field.

              • 4. Re: relationship graph for directory application

                Thanks Mike  You understand the problem (challenge) perfectly.  I had thought of a script solution, but then thought that surely there's an elegant solution through the Relationship graph.  Youve headed me back to the script side of things plus a couple of stragegies for doing it.  AND I note a zip file I'll look at when i'm back at my computer.


                here's another thought I have had

                  In the people  table, makebfield concatenating the unidID and last name

                  Set a global variable to the list of this field.  Don't know if that could be in the Listings table, or

                    would need to be a global variable.

                  I believe the list function returns a list of the unique values of the field,

                     which would correspond to the listings.                                                                                                                                          

                • 5. Re: relationship graph for directory application

                  As erolst points out, this does not solve your problem, as you have multiple people with different last names living in the same unit. So unitID + last name does not form a unique key.

                  • 6. Re: relationship graph for directory application


                    " His sisters and his cousins whom he reckons by the dozens ...  and his aunts"

                             ---  HMS Pinifore


                    Yes you're right that this can (and has) arisen.  I've currently kludged this by making children (different last names if necessary) attributes of the household which is one-one with the unit. Better, children should be people of type "child" who live in a unit.  I think that's doable when I have a solution for the basic listing problem. 


                    Another scaling up issue is the number of attributes for a persoon:  email, workphone,cellphoone,email2,website .....

                    The plan for these is to normalize them into an attributes table with a many-one relationship to people.  Each record has the _fkey to people, a label (e.g. home email) and a value (jillwellman@mac.com)


                    Thanks for the mind-building comments

                    • 7. Re: relationship graph for directory application

                      Before getting too far down the slippery slope of modeling familial relations, it's important to ask what the goal is of this directory. Is there some legal requirement? Some HOA requirement? Is it simply for the convenience of residents? Or for staff at a property management office? This will influence the design decision.


                      For example, if it's public, you probably aren't going to be publishing the names of children. And if you're not, then why model the children in the system?

                      • 8. Re: relationship graph for directory application

                        To add my two bob's worth, I think you need also to prepare for the possibility that you have two people living at the same unit with no connecting relationship at all but who happen to share the same surname.

                        • 9. Re: relationship graph for directory application

                          Especially since the community is small,  this is not a foreseeable problem.

                          Only question might be scalability or more generalizability.


                          If I were dealing with a family service organization I would start over, and work first on understanding the multiple complex extended roles in the "families"


                          Interesting challenge. I wonder if anyone has tackled this.



                          • 10. Re: relationship graph for directory application

                            using List function -- you are right.


                            I had mistakenly recalled that list returned a list of uniique field values.  It does not.

                            Brian Dunning a customfunction uniquevaluesof (   )   But this is getting convoluted.


                            When I can get back to this I'm going with Mike's first post -- using scripts


                            Will report back