How to handle complex data relationships within one form?
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.
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?
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?