1 2 Previous Next 15 Replies Latest reply on Jun 9, 2015 12:20 PM by RidleyJones

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

    RidleyJones

      Title

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

      Post

      Hello,

      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!

       

       

       

       

        • 1. Re: Complex many-to-many:  painful issue with Table Occurrences
          SteveMartino

          Well I read it 3 times............and............huh?  What may help is to either show some screenshots of what you are trying to accomplish, and use actual words to describe what you are trying to accomplish-please be specific.  "5 lists, and all the A, B, C, D, E...and AB, BC...etc, is just too confusing to follow.

          If you just simple explain what you are trying to do, or would like to do, then people will help, or ask more question to help.

          • 2. Re: Complex many-to-many:  painful issue with Table Occurrences
            RidleyJones

            [Edited to fix one of the screen shots]

            Hi Steve,

            I apologize for any lack of clarity.  I guess I was trying to be too abstract.  So here are a couple screen shots for you.

            The first is simply a list of all the tables, including the join tables.  No relationships have yet been set.  Note that the first five are the main lists.

            The second shows the relationships up until the point things start to break.  The blue tables are the main tables, the green ones are the join tables where everything functions properly.  The gray rows are where, if I added the relationships I wanted to add, multiple table occurrences of the main tables would start to pop up.

            The third is a crude depiction of one of the potential layouts that the customer would see.  There would be such a layout for each of the five main lists.  In this case, it's a record in the "Courses" layout.  Note that there are related records displayed for all the other main lists.  If I were to uncheck any checkbox, it would remove that relationship in both places.  If I were to add a checkbox, it would add that relationship in both places.  I know it looks terrible, I was just trying to answer you quickly.

            Does my aim here make more sense now?

            And thanks for your patience!

            • 3. Re: Complex many-to-many:  painful issue with Table Occurrences
              philmodjunk

              Now, when I try to go into, say, the main layout for C to see/edit the related B records, ... I get "This operation cannot be performed because one or more required related records are not available and cannot be created"

              I don't think editing a B record is the problem. It sounds like you tried to add a new record to B via a portal to the same. When you have this relationship:

              C----<join>----B

              Adding a new record from C to B in that fashion requires that "allow creation of records via this relationship" be enabled for both the C to Join and Join to B relationships. (And you must do so for the correct table occurrences as specified by your relationship graph and your layout design.) Doing so, automatically adds the "connecting" join table record at the same time

              But adding a new record in this fashion may not be a good idea in the first place. It's often better to set up some global fields in a modal dialog opened by new window or inside a popover with a script that uses the fields to both create the new B record and also a linking join table record if you want that action to automatically link the new record in B to the current record in C.

               

               

              • 4. Re: Complex many-to-many:  painful issue with Table Occurrences
                RidleyJones

                Thanks for your response, Phil.  

                I tried all combinations of "allow creation of records" before posting--it certainly helped some things, but it didn't quite get me where I wanted to be.

                I was hoping not to have to do any scripting, but considering how many hours I've already spent fruitlessly trying to make my current structure work, maybe it's not a bad idea.  

                • 5. Re: Complex many-to-many:  painful issue with Table Occurrences
                  philmodjunk

                  I can assure you that the "allow creation..." method works, I've used it many times.

                  If your layout is based on "C" (This table occurrence is selected in Layout Setup|Show Records From) and the Portal is based on "B" (This table occurrence is specified in Portal Setup | Show Related Records From.), then double clicking the relationship line from C to the Join and selecting "allow creation..." on the join side of the dialog followed by double clicking the Join to B line and selecting this option on the B side of this dialog will make it possible to fill in data in the bottom blank "add row" of the portal to create new related records in the data source tables of both the Join and B tables.

                  Hmmm, there is one other detail. This assumes that the ID fields used as match fields in tables B and C are both defined to auto-enter a unique identifier--either an auto-entered serial number or a text field with Get ( UUID ) as it's auto-enter calculation. But if this were the missing detail, I'd expect you to be reporting a different error message that would be telling you that one of these ID fields need a value in order to create the related message.

                  My best guess is that you are getting the different table occurrence names that all refer to the same data source table confused with each other. The TO's specified in your layout and portal setups have to match your relationship graph and the relationships matching the specified TOs are what govern these functions.

                  • 6. Re: Complex many-to-many:  painful issue with Table Occurrences
                    MorkAfur

                    Just chiming in here to agree with Phil that the Allow Creation works every time for me.

                    I have many M:M relationships and they all work fine populating the keys based on the relationship permissions.

                    I might suggest that you invest, if you haven't already, a good database diagramming tool. On the Mac, there's an excellent tool called SQLEditor. It's less than $100. FileMaker's relationship diagram is really good as far as it goes, but it's not really up to a more professional ERD type tool.

                    And, it will export to FileMaker!  See the image below.

                    (also to Oracle, MySQL, and you can add other drivers...)

                    When things aren't working, I usually go for a walk and drop back and back away from the box with the blinking lights (aka, the computer) and really make sure I understand my design - hence the SQLEditor program suggestion.

                    HTH

                    - m

                     

                    • 7. Re: Complex many-to-many:  painful issue with Table Occurrences
                      RidleyJones

                      Thank you both so much, truly, but I'm still in the same quandary.  Argh! 

                      Let me break down exactly how and where the problem starts.

                      So let's say I set up Join_Skills_Courses (after setting up those four CoCurricular join tables, as illustrated in one of my screen shots).  It has three fields: one primary key, which is an auto-entered, serial, unique, always validated number.  In fact, all the primary keys for all the tables have these characteristics.  It has two other fields, both numerical, one of which corresponds to a "SkillID" and one of which corresponds to a "SI CourseID".  These two are supposed to be joined to the primary keys of the Skills table and the Courses table, respectively.  

                      First step is to set up the join between Join_Skills_Courses and Courses.  So I've done, as illustrated here.  Note the symmetrical "allow create" options.

                      Next step is to set up the join between Join_Skills_Courses and Skills.  When I try to do the same, I of course see:

                      So I allow it because, what choice do I have?

                      Now I go into the Skills layout.  I add a portal to Join_Skills_Courses, with its Courses field selected:

                      I want to make this a checkbox view, so I have to use a value list to populate it.

                      The value list is based on the Course ID field in the Courses table.  It gets this ID field but displays only the Course Number to the user:

                      Now when I've set that up and try to select a record in this portal, of course I see:

                      As I mentioned before and Steve reiterated, the problem arises because the connection in the join is between Skills2 and Courses, not between Skills and Courses.  So how do I overcome this without creating a separate layout for Skills2?  I've even tried setting up joins between Skills2 and Skills, of course that isn't allowed either.  Isn't there some kind of "portal within portal" or "get similar values and associate that way" kind of thing?

                      The sad thing is that I started all of this for my poor customer who's been waiting forever because I thought it would be faster and easier for her than using an actual database.  Makes me long for GROUP_CONCAT.

                      Mork, that database visualization tool looks really nice, but first I have a more fundamental problem of knowledge and competence to overcome.

                      • 8. Re: Complex many-to-many:  painful issue with Table Occurrences
                        MorkAfur

                        Wow, now that's what I call good screenshots! 

                        -----------

                        My point about the diagramming tool was really that ... implementation, the part you're doing now, should be the easy part (and the last part).

                        However, RAD tools like FMP, and I'm not picking on FMP, encourage us to Jump In and get started.

                        With complicated programming projects (writing part of a compiler, for example), trying to "start coding" without lots of analysis and understanding up front almost always leads to problems (restarts, hashing, etc.). Once we start "implementing", we're problem solving and we lose the big picture if it's not already understood and documented.

                        In situations like yours, I always drop back and make sure I understand my data model, first.  What may be a problem for filemaker, might or might not be a data modeling problem.

                        Another idea is to try to implement your database design in, say, MySQL, or other, database. MySQL is more powerful than FileMaker in several aspects, but for the current suggestion, you could just use it to validate what you have.

                        Once you have the database defined in a, say, SQL database like MySQL (which is free, by the way), make sure you can do the queries you need based on your design. If you can't query your data, then it's your model that needs to change.

                        Hope this helps.

                        - m

                        • 9. Re: Complex many-to-many:  painful issue with Table Occurrences
                          RidleyJones

                          That's a great point, Mork.  In fact it's the kind of thing I sometimes lecture other people about.  I guess I was suffering from shiny new toy syndrome.

                          • 11. Re: Complex many-to-many:  painful issue with Table Occurrences
                            MorkAfur

                            Pardon my verbosity, but I just wanted to emphasize that after you generate your data model, tools like SQLEditor (Toad, and others) will generate the database!  Create, refine and experiment with your queries, refine the data model and, if necessary, re-generate the database from the model. (These tools also create the DDL in case you want to create the model manually - using DDL or SQL)

                            You could then use a free program like "Randomite" (http://www.kmcode.com/) to generate a few rows of test data or up to 99,999,999 rows per table of just about any type of data you can think of. Very cool program. Free!!!!

                            Or, if you have data in FMP, you could export your FMP data as CSV data which is importable into just about any database.

                            Enjoy.

                            - m

                            • 12. Re: Complex many-to-many:  painful issue with Table Occurrences
                              philmodjunk

                              So I allow it because, what choice do I have?

                              Your choice is to duplicate a table occurrence BEFORE trying to define the relationship. This gives you control over which occurrence is duplicated.

                              Now I go into the Skills layout.  I add a portal to Join_Skills_Courses, with its Courses field selected:

                              But a layout based on Skills is NOT, by your description, directly linked to join_Skills_Courses. Skills 2 is linked to join_Skills_Courses and this is why you get the error message that you get here. It IS linked but in a round about path through many intervening TO's.

                              Two possible fixes to consider:

                              a) Base your layout on Skills 2.

                              b) Make a duplicate TO of join_Sklls_Courses and link this duplicate to Skills. Then, on your Skills based layout, link it to a portal based on this duplicate of the Join table.

                              You may find this thread on Table Occurrences helpful in better understanding this process: Tutorial: What are Table Occurrences?

                               

                              • 13. Re: Complex many-to-many:  painful issue with Table Occurrences
                                RidleyJones

                                Thanks so much again, guys.  I am out today but I think all of your suggestions will be very productive when I jump back into this on Monday. 

                                • 14. Re: Complex many-to-many:  painful issue with Table Occurrences
                                  DavidJondreau

                                  I don't know if this is related to your issue, but you shouldn't be using a checkbox here. A Pop up menu with the "show second field" only option checked is what you want. By using a checkbox, you're making a single one-to-many join rather than multiple one-to-one joins.

                                  And by using CourseID and CourseNumber as the value you're setting the join ID field to that rather than just the CourseID.

                                   

                                  1 2 Previous Next