UI for Many-To-Many relationships

Discussion created by garyshell on Feb 27, 2012
Latest reply on Mar 1, 2012 by DavidJondreau

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?