5 Replies Latest reply on Mar 1, 2012 9:26 AM by DavidJondreau

    UI for Many-To-Many relationships

    garyshell

      I have a two tables which I'll call in this example, AsIsItem and ToBeItem. They each have a Key field and a Description field. I have a third table called Recommendations. It has in it two fields AsIsKey and ToBeKey.

       

      In FileMaker I am struggling with how to build the user interface. Ideally, I want three grids. The top left window would display a scrollable (and filterable) list of the AsIsItems with Key and Descriptions.The top right would show a similar list of ToBeItems. Centered below that would be entries from the Recommendations table.

       

      When an AsIsItem is selected in the top left grid, the Recomendations grid would show the list of all of the ToBeItems associated with that AsIsItem. Then the user would be able to select one of those Recommendations and delete it. In addition the user would be able to select one (or ideally more) of the ToBeItems and click a button or drag and drop them into the Recommendations grid to add that ToBeItemKey (and the currently selected AsIsItemKey) to the Recommendations table.

       

      I can see how I can display a single AsIsItem in a layout and a Recommendations grid using a Portal to display them. But I don't see how I can display items from the ToBeItems table to allow the user to scroll through it or filter it to select items to be added. I also don't understand how I could make the layout display a grid of the AsIsItems rather than a single one. It is very important for this application that both of these parent tables in this many-to-many relationship be displayed as scrollable, filterable lists. Dropdown lists aren't really workable for the workflow the user has.

       

      Any suggestions?

       

      Gary

        • 1. Re: UI for Many-To-Many relationships
          DavidJondreau

          Sounds like you're looking for two things...how to present related records in a grid and how to create a selection function for join records.

           

          For the first, you can set portals to start on the 3rd or 4th (or whateverth) related record. Put a few side by side with alternating starts, make them as tall as they are wide, and you've got a grid.

           

          For the second...well, you don't say what table your layout is based on, but I'd say for your records in the portal on the right, that portal will draw from available To Be records filtered through a new relationship/TO. To that To Be portal, add a scripted button to create new Recommendations records. On the Recommendations portal, add a delete portal row button.

           

          You can also control what shows up in the To Be list using portal filtering (like removing already recommended To Be items from the To Be list).

           

          Hope that helps,

           

          DJ

          • 2. Re: UI for Many-To-Many relationships
            garyshell

            Let me take another stab at the description. I think I should have used the word list instead of grid.  (My Access background is showing!)

             

            The list on the left is a scrollable and user filterable list of ALL of the AsIsItems records.  The list on the right is a scrollable and user filterable list of ALL of the ToBeItems.  When you select an item in the AsIsItems list then then Recommended list then shows the items in the Many-to-Many relationship that joins the AsIsItems and ToBeItems.

             

            The AsIsItems list would be two columns wide, the first column showing the AiIsItem key and the second column would be be the Description.  So it seems that a simple portal would suffice.  (I do understand your idea about creating the grid, though and I will remember that trick should it every be needed).  The problem I am struggling with is that I want the AsIsItems and ToBeItems lists to show ALL the records in the tables. Portals, as I understand them don't allow for that  I am under the impression that portals are only for the display of related records, i.e. child records in a parent child relationship.  The Recommended list, would be an example of a portal as I understand their use because it would show the related records from whatever item is selected in the AsIsItems list.

             

            Does that make any more sense?

             

            Thanks,

            Gary

            • 3. Re: UI for Many-To-Many relationships
              DavidJondreau

              There is a relationship type called a Cartesian join. It can be used to link a record to all other records.

              DJ

              • 4. Re: UI for Many-To-Many relationships
                garyshell

                I am familiar with a Cartesian join, but not how one would specify it in FileMaker.  And what would the consequences of doing this type of join on two tables with upwards of 1000 rows in each table?  Would this bring a FilemakerGo app to it's knees speed wise? 

                 

                Gary

                • 5. Re: UI for Many-To-Many relationships
                  DavidJondreau

                  Speedwise, it might be ok. FM only loads 25 records at a time. If the user has to scroll a whole lot to get where they want to go, it could be a problem. Only the child side of the relationship really matters here. A 1,000 row portal though isn't good user interface. You'd probably want some sort of filtering at the relationship side (two relationship criteria, one the Cartesian, the other between a parent global that the user enter filtering criteria and the other a match field in the child table).