AnsweredAssumed Answered

Complex many-to-many:  painful issue with Table Occurrences

Question asked by RidleyJones on Jun 4, 2015
Latest reply on Jun 9, 2015 by RidleyJones


Complex many-to-many:  painful issue with Table Occurrences



I am at the end of my rope here, couldn't find an answer online anywhere.  This is quite long and annoying, but I will be IMMENSELY GRATEFUL to anyone who can end my suffering by giving me some closure on this.

I'm making a FMP13 database for an internal customer.  She has five different lists (let's call them A,B,C,D, and E.  I set up serial pks for each list, but the main data object for each record is just short text).  Each list has a many-to-many relationship with each other list.  I need to set up the tables such that I can create one layout for each of the five lists.  Each such layout will show related records from each of the four other lists (in a portal of course), and each portal will allow for adding and removing related records, preferably with checkboxes.  This must symmetrically and effortlessly flow from one list to another.  So for example, if in record 1 in the layout representing A, I have all the list items from B.  Five are already selected as being related.  If I select two more (records 2 and 3, say), then if I go to B's layout, records 2 and 3 will both show record 1 in the A portal as being checked.  And so on and so forth, for any possible combination.

Now, I did find a partial help for this in an excellent online tutorial, showing many-to-many via a join table--basically, in the layout for A, you'd show a portal to Join_AB and choose the B-related field to display.  So I made one join table for each of the ten possible combinations.  This worked splendidly for the first "layer" of join tables, let's call them Join_AB, Join_AC, Join_AD, and Join_AE.  The problem arose when I reached the second layer: Join_BC, Join_BD, and Join_BE.  Now, of course, when I try to relate B and C, the relationship becomes circular so a second Table Occurrence for some of the primary lists inevitably pops up.  So, because I had no choice, I allowed the "second layer" and beyond join tables to relate to the secondary table occurrences.

Now, when I try to go into, say, the main layout for C to see/edit the related B records, I get the heartbreaking "This operation cannot be performed because one or more required related records are not available and cannot be created" record.  This is because the C layout is based on the main C table, but Join_BC was forced to relate to the second TO of C (call it C_2), not the primary TO.  The relation information doesn't flow up.  In fact, just to try things out (I've tried many many MANY things out, but this post is already too long), in the C portal, I made a portal to C_2.  It was blank.  Making a layout for C_2 of course made the join information for B visible, editable, whatever; but breaking it out like this is not acceptable for user experience.  All of the related records for ALL other lists must be visible on each list's layout.  Is there something I'm missing here?  Some way of making changes to either table occurrence flow up to the parent table so that changes are symmetrically visible?  If it's not possible, I really don't know what to do.

Thank you so very much for reading and--I do hope--saving the day!