Help - Conditional Relationship

[guytoronto]

I've been trying to figure this out every which way, but am getting nowhere fast.

[NOTE: I've attached a PDF with a simple illustration of what I am trying to do.]

I have a Main Record table. That table is holding, say, Recipes (for arguments sake).

Related to that Recipes table are a whole bunch of Contacts. Easy.

Each of those Contacts has a "relationship" with the main Recipe. Employee, accounting, legal, etc. These relationships are determined by a third table - Contact Relationship. Still very straight forward.

Within the Contact Relationship values, some of these relationships can be flagged as "Primary Contacts", in that we can set up that we consider Legal and Accounting to be two types of relationships we want to have a primary contact for.

Within the Contacts themselves, individual contacts can be flagged as the "Primary Contact". I've had no problem setting this all up, and properly scripting all this.

Here is my stumper. On my main layout, I want to have a list (portal or not, doesn't matter) of:

The Contact Relationships, in order of importance (we have set up a sorting field with Contact Relationships). That's easy, but...

I want to also show with those Contact Relationships, the name of the Primary Contact for that specific Recipe. And note, there may be no Primary Contact (say for Legal, no primary legal contact has been setup), but I still want to see the LABEL for Legal, and a blank spot where the name would be.

Right now, I have set this up in a very rudimentary fashion. I have 8 global fields within the Recipe table that represent the relationships of the primary contacts. When I updated my Contact Relationships table, the script automatically takes my top 8 priority flagged relationships, and puts their ID into those 8 global fields.

I then have 16 relations attached to that Recipe. One for each global to the Contact Relationship table to show me the "Name" of the Relationship (Legal, Administration, etc) , and one for each global to the actual Contact (those contacts have 3 conditions - relationship, primary flag, and related to the Recipe). It all works, but I was hoping for a more elegant solution that didn't involve 16 relation links.

Any thoughts?

filemaker_help.pdf