AnsweredAssumed Answered

Relationships for multiple table occurrences

Question asked by tunesmith on Mar 29, 2014
Latest reply on Mar 29, 2014 by tunesmith


Relationships for multiple table occurrences


     What are your favored techniques for displaying relationships from multiple table occurrences?

     For instance (simplified example again), I have Customers, Widgets, and Parts.  They are all many-to-many relationships:

  •           Customers can have many Widgets; Widgets can have many Customers
  •           Widgets can have many Parts; Parts can have many Parts
  •           Customers can have many Parts; Parts can have many Customers

     When I draw the relationship graph using a simple approach of three separate joining tables, one of the tables requires a separate Table Occurrence.  In my example, let's say it is Parts, so you end up with a Parts1 and a Parts2.

     Now, the problem is that for all three tables, I want to see both its relationships.  So for Parts, I want to have a Parts form where I can see both relationships:

  •           A Portal of all its Customers
  •           A Portal of all its Widgets

     This is not possible in cases where you have multiple table occurrences.  Has anyone come up with a snazzy way to work around this?  Here's what I have tried:

The "False Tabs" Approach:

     Create a layout for Parts that reads from the "Parts1" table.  Create a tab view with one tab on the left.  In that tab, put the Portal for the relationship it has access to (e.g. Customers).  In the space where the other tab would go, put a Button.  Duplicate the layout.  In the button for the first form, give it an action to switch to the other layout.  In the other layout, make it read from "Parts2".  Move the button to where the left tab was and change its action to switch back to the first layout.  Make the tab have a right tab, and change its Portal to the relationship that form has access to (e.g. Widgets).  So it *seems* like it is the same form.  I don't know how to make the button pass the "current record id" to the new layout, but I imagine that is possible.  The downside is that if you have to make a change to either form, then it is very tedious to make the other form look identical without re-duplicating and redoing the tab/button.

The "Links Table" Approach:

     Rather than have three separate joining tables, have one joining table with multiple columns.  In this way, the layouts for each table can see each both sets of relationships.  However, I could not make this work quite right.  If I just gave it three columns:

  1.           customer_id
  3.           widget_id
  5.           part_id

     then I ran into problems depending on how I structured the join rows.  Say that each of the three tables had two records (ids 1 and 2) and all were related.  If I made the rows look like this:

1 1 1
1 1 2
1 2 1
1 2 2
2 1 1
2 1 2
2 2 1
2 2 2

     Then the relationships would have duplicated rows in the portals - four rows instead of two.  If I opened up the rows:

1 1
1   1
1 2
1   2
2 1
2   1
2 2
2   2
  1 1
  1 2
  2 1
  2 2
Then the relationships would still have four rows instead of two, but two of them would be blank.

     I know I could instead make the links table have the following structure:
  1.           pk_customer
  3.           fk_customer
  5.           pk_part
  7.           fk_part
  9.           pk_widget
  11.           fk_widget

     But the problem there comes in maintaining the relationship integrity.  For instance, say you don't want customer 2 to be associated with widget 2 anymore.  If you remove that association from the customer side, then you won't see that widget 2 from Customer 1's widget portal anymore, but, if you go to widget 2's layout, you'll still see Customer 1 in its customer portal.

     I am curious if anyone has come up with an easier way to "seemingly" combine table occurrences within one layout?