4 Replies Latest reply on Aug 31, 2016 10:40 AM by golife

    How to best implement a complex party role relationship?

    golife

      How to handle complex data relationships within one form?

       

      Party-Role-Relationship model

       

      I am starting with what everyone knows: Contact information. What is described here, of course, does not only have to do with contacts but appears in many other situations as well.

       

      Imagine a complex party-role relationship schema where contacts are not just a "flat file", but are modeled in a different way. A flat file contact table does not allow to store relationship information, or be very useful modeling more complex situations in business.

       

      So, I am using a Party-Role-Relationship model:

       

      "Party" is a table which can be either a "Person" or an "Organization". So, there is a mutually-exclusive relationship between "Party" as a person and "Party" as an organization. For each party record, there is either a corresponding person record or a corresponding organization record.


      This model allows many employees to work for one organization or an organization to be the employer of many employees. Also, it allows an employee to be employed with many organizations.

       

      Since parties form a relationship between each other, such as customer-supplier, mother-child, headquarter-affiliate, also these relationships are modeled in a many-to-many relationship table. There is a relationship type, and there is a table listing all relationships between parties with a from- and to- foreign key to the primary key of Party.

       

      Question


      What is the best approach to enter data and visualize data for complex relationships in FileMaker?

       

      Example: Take a simple business card. It has a company name, a person name working for that company and his position, contact data for the company itself and a mobile phone for the employee. So, you need to store information about the company in the Organization table, the information about the employee in the Person table, the relationship and role of each of these parties in the PartyRelationship table. The work phone and email do not really belong to the person but to the relationship, the main phone and URL of the company to the Phones table linked to the company, and so forth. All this can not just be done using a "One to Many" relationship.

       

      In such case, most users would want to approach data entry going to the Party table, entering the party information and then the information for the employee and the relationship. There may be more employees already entered and being visible on such Party record. At least in the context of this base table, there is only one Party. If approaching from the Relationship table, there are many potential instances of Party (organization or person) with another Party (organization or person) and the type of relationship,

       

      Since FileMaker can show either information of the Organization (hiding any related Person fields), or a Person (hiding any related Organization fields), it is possible on the user level to show either the person or organization in the Party records of the Party table.

       

      But what really happens?

       

      Entering a new organization in Party will create the corresponding record in Organization (One to One). Then entering a person as an employee will not automatically enter both: A party record in Party and a person record in Person. So, that would have to be scripted. And also the relationship should be visible between Organization and Person, adding entries in the Relationship table: One relationship record from Party as a person, and another relationship record from Organization to a party (two if both records should show up for either Person and Organization in the corresponding Portal).

       

      My current approach

       

      So far I have used one solution in defining global fields to enter Person information and then using a script to create records for Party and Person and relationships between Party and Organization.

       

      Another approach was using a temporary table for Person (when starting from Organization) where the user can also enter more than just one person, and such data is then used to create the new party record in Party, related person records in Person, and the relationship records. (Of course, also the system must first check if there is already a person existing to avoid double entries).

       

      I am not too happy with these approaches. The problem I see is that in Filemaker usually there always has to be a context to start with. The context of such situation is not just the Party table, nor is it the Relationship table.

       

      The problem I see is that in Filemaker usually there always has to be a context to start with. The context of such situation is not just the Party table, nor is it the Relationship table. The context is a complex one.

       

      For example, starting from the Organization table instead of the Party table, then a Party record must be created for a new organization using the primary key of the Party, and then related Person records together with their corresponding Party records, and then the Relationship records. It also ends up in a lot of scripting. It can not be avoided. There is no direct approach I can see.

       

      So, another approach would be an entry and edit mask where all related information is collected used in different tables and loaded temporarily. There is a certain overhead in execution time (basically, data is copied and pasted using variable to store data and move data around), and it also needs quite some effort to put it together, and it means a lot of scripting. So, a table with a temporary context would be using either using global fields or temporary records which then later may be deleted.

       

      And what if the user wants to edit or delete data of such more complex relationships? Loading data again into a temporary view, then edit, and then store using scripts?

       

      The focus here is on a user-friendly way of managing complex relationships within the context of one data entry/edit form.

       

      Is there any better approach? Am I missing something?

       

      Visual representation

       

      If a company has many related relationships (for example employees with their respective roles) and the user wants to see this and access data. Also, the company has many phone numbers, many email addresses, many URls, and lots of other related information.

       

      In my thought, a user should not flip to other records loosing context. He should be able to do the work using just one not cluttered detail view (or list view). All important information should be visible and possibly editable within the context.


      Which information should be stay visible all the time, which information could be hidden and opened in popovers or whatever?

       

      The UX design decisions are a challenge as well if such more complex situations have to be presented.

       

      Again using temporary views?

        • 1. Re: How to best implement a complex party role relationship?
          philmodjunk

          Sounds like you need these relationships:

           

          Party|Contacts----<Role>---Party|Company
                                          |

                                         ^

                                  Relationship

           

          ----< stands for a "one to many" relationship with < identifying the "many" side of the relationship. This is sometimes referred to a "star join" as you have a join table that links more than two other table occurrences.

           

          Typically, you put a portal to Role on layouts based on one of the other three occurrences with value lists used to select values that link a given record to records via the other two relationships.

           

          It's also possible to base the layout on Role and have three value list formatted fields for linking it to the other three.

          • 2. Re: How to best implement a complex party role relationship?
            DanielShanahan

            I have a contact management system called NLD Encounters that implements the party model.  The db allows:

             

            • People to relate to people (e.g., Crew chief-crew member, manager-direct report, friend-friend, spouse-spouse, etc.).

            • People to relate to organizations.

            • Organizations to relate to people.

            • Organizations to relate to organizations (e.g., Company-division, etc.)

             

            There is a free user's guide PDF that may be enough to help you with your own system.  Otherwise, the .fmp12 file itself is inexpensive and open.

             

            You can find those files here: New Leaf Data, LLC on Gumroad

             

            HTH

            • 4. Re: How to best implement a complex party role relationship?
              golife

              Possibly the questions here goes a bit further. A party can be either people or organizations, and with the Internet of Things, we also interact with devices. So, even a device could be a party and may have an address, can be reached, etc. These parties have a relationship and relationship type to each other. The user of the system also may have a relationship working in one or more companies (tenant role) and depending on which role for which tenant the user takes, he plays a certain role with a party whose role may be customer, vendor or whatever.

               

              These parties have a relationship and relationship type to each other. The user of the system also may have a relationship working in one or more companies (tenant role type) and depending on which role for which tenant the user takes, he plays a certain role with a party whose role may be the customer, vendor or whatever.

               

              The data model is more complex. The problem I see is how to create the best possible entry / edit forms to create and update all the many related tables, and how to filter such values depending on the purpose of such roles and relationships for all kinds of tasks users have to manage.