9 Replies Latest reply on Jan 5, 2017 7:56 AM by erolst

    self multi-relationship

    gilo05

      Hi

       

      Unfortunately I do not know how to call the relation I need, so I "invented" self multi-relationship.

       

       

      My db contains clients. For these clients I want to create relationships with other clients.

       

      For example:

       

      client1 is the father of client2

      client3 is the firm where client 1 works

      client4 is the brother of client 2

       

       

      How do I get this done?

      I need that one client can have multiple relations with different other clients

      I need that if client1 is related with client2, that FB is also showing that client2 is related with client1.

      The hardest where I do not see a solution is, how to delete a relation.

       

      Maybe someone has experience with this case and could help me how to solve this problem in the most neat and effective way

       

       

      thanks in advance

        • 1. Re: self multi-relationship
          planteg

          Hi gilo05,

           

          Happy New Year to you ! What an interesting question you have.

           

          Haven't tested it but my first thought would be to create a table that would hold the relations. Now what's tricky, is that the type of relation between two clients depends on which way you look at it:

           

          client1 is is the father of client2, but that means that client2 is the son of client1.

           

          client4 is the brother of client2, but that also mean that client 2 is the brother of client 4 AND client1 could be the father of client 4 AND client4 could be the son of client1. The last two are deducted from the first one.

           

          A question: deducted relation would be created but the user or automatically 'foud' by the app ?

          • 2. Re: self multi-relationship
            golife

            There are actually two relationships maintained in a separate table 'relationship', for example, 'father to child' and the other 'son of father'. The 'relationship type' is another third table or a value list. Any type and number of relationships are possible then. Seen from the 'party' table, the father will see the son, and the son will see the father using a portal view.

             

            To delete, a script is possible to delete both records at once. Or both records are linked using a 'relationship instance' table with a record of each instance (for example of type 'family' for a family relationship with different members and roles) and setting the cascading delete for the two or more records of 'relationship'  to true if such relationship instance is deleted.

             

            'Party' here is an abstraction of 'person' and 'party' and person/organization are  mutually exclusive, be either 'person' or 'organization'. 'Party' is just an id number in a 'party' table allowing us to relate any person/organization through any relationship even when they have quite different attributes. So, there is also a 'person' table (title, first name, birthday, ...) and an 'organization' table (name, legal form, size, ...).

             

            The relationship, for example between any two parties having roles such as 'customer' or 'vendor' can then be maintained for any party. Even users can be users for any tenant, or employees can be employees to any employer. And a person may have quite different roles and relationships with friends or various employers.

             

            For a multi-tenant system this is an important concept. Customer-Relationship-Management means just this on an abstracted level: being able to enter, change, maintain, and delete roles and relationships.

             

            For certain relationships additional tables are needed, for example we do not only want to know the type of relationships, but also the order history of a 'customer', or the work effort of an 'employee'.

             

            And creating a Filemaker table with fields means to find a suitable way of storing such related information without any duplication (search for database normalization rules).

             

            We could also talk about 'party',  'role' and 'relationship'. Parties (people, organizations) play different roles in relationships depending on the point of view.

             

            What might help are SQL queries in combination with scripts to build such more complex system.

            1 of 1 people found this helpful
            • 3. Re: self multi-relationship
              golife

              If you reduce complexity for now and start from a 'client' table, then build a 'relationship' table. For example, at a bare minimum, two clients are brothers. There are two new records in 'relationship':  First record with 'id_from' (client4), 'id_to' (client2) of role type "brother", and second record with 'id_from' (client2), 'id_to' (client4) and also of role type "brother". The relationship is:  client1-<relationship>-client2. Using two records, both clients see each-other using a portal. Otherwise, with only one record, only one side of the relationship direction would be visible. Then Mark would be brother of Tom, but Tom not be brother of Mark. Now, with this, any number and type of relationships are possible. To delete both without scripting, the portal roles in client pointing to relationship can be deleted, but you must go to Tom to delete Mark as a brother in the portal, and to Mark to delete Tom as a brother. A script could simplify this double delete.

              1 of 1 people found this helpful
              • 4. Re: self multi-relationship
                gilo05

                Thank you guys for your help and happy new year

                 

                The third post sounds like I can put in place. The only question remaining is if I'm looking at Mark's record, I add the relationship with Tom as his brother, how could I manage that FM is adding Tom as Mark's brother?

                 

                What would the script look like?!?!?!

                I'm giving it a try

                 

                 

                thank you guys

                • 5. Re: self multi-relationship
                  erolst

                  Here's another approach that I am using:

                   

                  - add a Joiner table where one record serves as an anchor

                  - add a JoinedPeople table, where every record describes a person and her/his relationship to the other people with the same anchor

                   

                  To find the other person(s), use

                   

                  JoinedPeople::id_joiner = JoinedPeople_self_others::id_joiner

                  JoinedPeople::id_person <> JoinedPeople_self_others::id_person

                   

                  Now if you have "seven sisters and seven brothers", you only need one Joiner record and 14 JoinedPeople records: no matter what JoinedPeople record you look at, you will see the other 13, each with their respective "role" (provided you added it, of course ...)

                   

                  The way I'm using this is to find a person, have a script create a new Joiner and a new related JoinedPerson record, then select the other(s) via a type-ahead portal.

                  • 6. Re: self multi-relationship
                    BowdenData

                    Erolst,

                     

                    Your two table approach sounds interesting, but I can't quite wrap my head around it. In the case of brothers and sisters, what would the Joiner table record represent? It wouldn't be a specific brother or sister, would it? Would it be more like this record would be an arbitrary relationship record?

                     

                    By chance would have have a demo file showing this or perhaps a picture of a relationship graph showing the tables and relationships?

                     

                    Doug

                    • 7. Re: self multi-relationship
                      golife

                      To Planteg: If I find the time, I will try to create a demo for both or even more possible solutions t, but it may take some time to do. It is not trivial if you go deeply enough and it should be simple enough for demonstration purposes of this concept. I am not promising that I will be able to do it shortly as other tasks are pressing. But I think something like this should be done.

                       

                      ===

                       

                      I am giving some more thoughts - and also thinking about the Pros and Cons of solutions:

                       

                      Example: Mary is a sister to Mark and Mark is a brother to Mary. What is actually "joined" are the roles, not the people directly, as far as I can see. But different models are possible.

                       

                      In my understanding, this is one (a bit complex) way of solving it but would model all kinds of situations. And it needs to even be expanded. Yes, there are other possible ways of doing it as well.

                       

                      PARTY (Mary) --< ROLE (Sister) --< RELATIONSHIP >-- ROLE (Brother) >-- PARTY (Mark)

                      ROLE >-- ROLETYPE

                      RELATIONSHIP >-- RELALATIONSHIPTYPE

                       

                      I will try to come back to it. It needs more time... The best is always to try it in practice after analyzing what one really needs and what world around us to model - and in which degree of complexity.

                      • 8. Re: self multi-relationship
                        philmodjunk

                        The third post sounds like I can put in place. The only question remaining is if I'm looking at Mark's record, I add the relationship with Tom as his brother, how could I manage that FM is adding Tom as Mark's brother?

                        In rough outline, a script can take the initial record in the relationship (Join) table and create a second record where the values in the two match fields are swapped:

                         

                        Set variable [$ID1 ; Relationships::MatchFIeld1 ]
                        Set variable [$ID2 ; Relationships::matchField2 ]

                        New /Record request --> if on a layout based on relationships

                        Set FIeld [ Relationships::MatchField2 ; $ID1 ]
                        Set Field [ Relationships::MatchField1 ; $ID2 ]

                         

                        If you are doing this from the context of a related table, there are several methods that can be used to create the additional record in your relationships table but the swapped match field value part of this process would be essentially the same.

                        • 9. Re: self multi-relationship
                          erolst

                          BowdenData wrote:

                          Your two table approach sounds interesting, but I can't quite wrap my head around it. In the case of brothers and sisters, what would the Joiner table record represent?

                          In that case not necessarily anything, except that it gives me a neutral context from which to see all joined records.

                           

                          But if you think about other sorts of relationships - eg spouses - than that record would be a place to add date information, comments, quality of information, what-have-you - eg data about that relationship itself.

                           

                          I use this within a movie/media database, so recently I added 'sued' / 'was sued by' ... :-) and you could basically define any sort of connection.

                           

                          BowdenData wrote:

                          By chance would have have a demo file showing this or perhaps a picture of a relationship graph showing the tables and relationships?

                           

                          Doug

                          Not ATM (got a nasty cold), but for displaying a defined relationship, it's simply

                           

                          People --< JoinedPeople --< OtherJoinedPeople --< Joiner

                           

                          meaning that the JoinerRecord is not necessary to display the related people itself, but can be inspected to retrieve any data describing the relationship.