Many-To-How-Many - HTML Edition (Heinrich Wendel)

Many-To-How-Many - HTML Edition (Heinrich Wendel)

Rate This
  • Comments 10

The original idea of this blog post was to simply provide you with a tutorial on how to create a many-to-many control for your HTML app, using the built-in controls and some glue code to hook up the correct logic. Andy wrote a similar post for the Silverlight client and we later shipped a control as an add-on which you can find on the MSDN code samples site.

While digging deeper into the topic I realized that the complexity of it was actually higher than originally anticipated. I realized that there are different types of many-to-many relations and one solution doesn't fit all scenarios. Therefor I think I should start with taking one step back, actually writing the spec before starting the implementation. I'll try to define the different kinds of many-to-many relations and show you how to implement one of those later in the post. 

The experience you want to provide to your users for creating many-to-many relations depends on multiple factors. First, how do you expect people to use the app, primarily using a real keyboard and mouse or touch and the on-screen keyboard. Second, how big is the list of items you expect people to choose from and is the list pretty much static or does the list change during the lifetime of your app. Third, how many items do you expect people to select, is it just a matter of two or three or rather a larger number of entries? And finally, is selecting the items an action they do once when creating a new item (on an "Edit" screen) or are they continuously adding new relations to it (from a "Browse" screen)?

Defining many-to-many

Let's start with an example, Movies and Actors. A movie has multiple actors (exceptions prove the rule) and even actors that we only recognize from one time hits played in less popular roles in other movies. Seems like a good candidate for a many-to-many. There is more to this seemingly simple example though. An actor plays a certain role in the movie, so the role is an additional field which is part of the mapping table. Whenever your mapping table contains additional fields, like the role in this case, it will get more complicated. For this blog post I'll focus on the simpler examples that do not include those additional fields.

Based on this example let me try to define many-to-many as follows:

A many-to-many relation can be identified by a mapping table which consists of two foreign keys combined as the primary key for the table. Complex many-to-many relations can contain additional properties in the mapping table.

There are (at least) two more special cases for many-to-many: tags and people. Tagging has become very popular as a method to categorize your data. You tag blog posts, you tag forum posts, at last you can even tag TFS bugs. People is the core primitive of any social app. People are assigned to tasks, people are invited to events, people post messages, people are tagged on images. For all those action controls follow patterns which are very similar to what I'll describe later. There is a key difference to real many-to-manys though. Tags and people are usually not backed by a database table, they live in another service. Office 365, for example, offers a rich set of services around both identify and managed metadata. In that sense tags and people are more like data types than traditional relations.

I'll roughly categorize those relations into two buckets: static lists of items and dynamic lists of items. In this context the words static and dynamic have to be interpreted in a loose sense. By static I mean mostly static, i.e. every now and then an item could be added or deleted. Dynamic means that items are added over time while using the application on a daily basis.

Static lists of items

Let's first talk about many-to-many relations where the number of items is a static list. The normal users of the app does not have permissions to extend the list of items in this case. Administrators might add additional items to the list over time when the app grows and additional requirements are added. Examples for those are selecting addition topping when ordering a pizza, categorizing movies into genres or selecting from the list of all countries in the world. Users do not have the right to add items to those lists. Over time though additional toppings might be added, a new genre emerges or a revolution leads to the formation of two countries out of a single one.

I will further split up this list of items into three categories, separated by the number of items which determines the appropriate visualization. In the pizza topping case you might have a couple of items in which case a simple group of checkboxes is an appropriate visualization. Netflix lists about twenty different genres, when adding a new movie to the database you do not want to clutter the edit form with that many checkboxes, you would rather choose a multi-select drop down. The number of countries in the world is about 250, adding a client-side filter box helps with quickly selecting the right ones. 

The three mockups above visualize those three different patterns. Depending on your device the visualization of the control might change slightly, but the usage pattern remains the same. On a phone the multi select drop down looks more like a popup. The popup is opened by clicking a button which summarizes the current list of choices in its text. If the number of items is larger you might use the complete screen for the list instead of just a smaller popup. Those controls are placed on "Edit" screens since you usually define those properties when initially creating the entity or making larger edits to it.

Dynamic lists of items

The second bucket is the dynamic list of items. The normal users of the app extend the items that show up in this list while performing their day to day work. Over time the list can grow to a contain a very large number of items. CRM systems are good examples for this pattern. They maintain databases of accounts, contacts, or competitors. With every new lead or opportunity, every contact a sales person establishes, this lists potentially grows to include a new entry. Since the number of items in those lists are large, server-side search is a necessity. The primary input experience depends on whether you are using a touch device or a keyboard device.

On touch devices, phones or tables, users would add items one by one. A button positioned in a command bar or close to the list itself opens a popup. The popups shows the list of available items and a text input for performing server-side search. Selecting one of the items will add it to the actual list and close the popup. Multiple items are added by repeating those gestures. Items can be removed by selecting individual items, typically done using a swipe gesture, and selecting the delete command from the command bar or on the item itself. Adding contacts is an additive gesture and is commonly placed on a "Browse" screen. This means selecting the item from the popup will assign it to the entry and also save the changes in the background.

On desktop and keyboard oriented devices a typical pattern to use would be a text field with auto-complete functionality, separating entries using semicolons. After typing 3 letters the server-side search starts to fill out a combo box with suggestions. Selecting one of the suggestions will add the item to the list. Clicking the small (x) after each item removes it from the list with a single click. In addition to that, if an item cannot be found, the combo box offers the possibility to create a new item of that type right away from the same screen. This nested flow to create the new item would show up in a dialog on top of the current screen. Typically this control would be placed on an "Edit" screen. On a "Browse" screen you would follow a pattern which combines the one by one adding of items with the auto-complete text box for single selection.

Putting it into action

Enough theory again, let's put it into action. Out of the 5 patterns I have shown you, I'll explain how to implement the fourth one. Although this one is really optimized for a dynamic list of items on a touch device, it is general enough to be applied to all mentioned many-to-many scenarios. Let's start with the data structure. Setup your project to contain the following three tables: Competitors, Opportunities and OpportunityCompetitorMapping. For simplicity Competitors just have a Name property and Opportunities have a Title property, both of type String.

Next, create the default set of screens for adding/editing and viewing of Competitors and Opportunities based on the screen templates. In addition to that we'll create a Home screen which contains two tabs, one for Competitors and one for Opportunities. On each tab we add a command to the command bar which launches the appropriate screen to create a new entity. Also hook up the ItemTap action of each list to show the details of the associated entity. After doing that your Home screen should look similar to the following screenshot.

Now, run the application and add a few competitors to the list. Also add a new opportunity. On the details screen for this opportunity you can now see the opportunity's title showing up and a second tab named "Opportunity Competitor Mappings". We'll modify this tab to provide the user experience around creating the many-to-many relations. 

Open the ViewOpportunity screen in the screen designer and follow these steps:

  1. Rename the "Opportunity Competitor Mappings" tab to "Competitors".
  2. Change the row template of the "Opportunity Competitor Mapping" to simply show the name of the competitor by changing it from Summaryto Rows Layouts.
  3. Add a second query to the screen listing all competitors by clicking "Add Data Item…" -> "Query" -> "Competitors"
  4. Create a new popup on the screen and drag the query into it
  5. Add an optional parameter to the Competitors query which filters it by items containing the parameter in the Name field
  6. Drag the parameter into the popup above the competitor list and set its Placeholder property to "Search"
  7. Add a new button to the command bar named "Add Competitor" and hook it up to open the popup

After all those steps your screen should look like this (changes highlighted in red):

Running the application at this point will already show the final user interface. The only thing we are missing now is the code to actually create the many-to-many relation when selecting an item in the competitors list. Select the ItemTap action of the list and create a new method called "AddCompetitor" . In its execute method add the following code:

myapp.ViewOpportunity.Competitors_ItemTap_execute = function (screen) {
   // Write code here.
   var mapping = myapp.activeDataWorkspace.ApplicationData.OpportunityCompetitorMappings.addNew();
   mapping.setOpportunity(screen.Opportunity);
   mapping.setCompetitor(screen.Competitors.selectedItem);
   screen.closePopup();
   return myapp.applyChanges();
};

This code will create a new entry in the mapping table, set both ends of the many-to-many relation, close the popup and save the changes to the database. Run the application and try it yourself, the following screenshots show the final flow. 

Wrapping it up

There are some tweaks that you could add to the experience, deleting items or checking duplicates, but I'll leave it up to you to add those for now. I showed you five different UX patterns for creating many-to-many relations. In which category does your scenario fall? Since I am not claiming to be an UX expert I could also have missed something. Let me what you think, either here in the comments or in our forums.

- Heinrich Wendel, Program Manager LightSwitch Team

Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post
  • I need many to many mostly to include users as participants in projects, issues, KPIs, etc. A Project can have many participants and a user can participate in many projects. Ideally as you include the participant its name should disapear from the user list so that you don't include it twice.

  • Thanks for sharing the scenario Marcelo, this seems to align with my CRM example. Usually the lists of users and projects can grow pretty large. Removing a few users from the list wouldn't help with the usability of those lists. The actual goal, as you mention, is to not include a user in a project twice. "Forgiving" business logic which will just ignore this case and silently do nothing would be the best solution in my opinion.

  • Outstanding!!!

  • Please rectify the code as follows

    myapp.ViewOppertunity.Competitors_ItemTap_execute = function (screen) {

       // Write code here.

       var mapping = myapp.activeDataWorkspace.ApplicationData.OppertunityCompetitorMappings.addNew();

       mapping.setOppertunity(screen.details.properties.Oppertunity.value);

       mapping.setCompetitor(screen.details.properties.Competitors.value.selectedItem);

       screen.closePopup();

       return myapp.applyChanges();

    };

  • Very useful post!

  • DK Apps,

    I changed the signature of the method to match yours, which is also the default method name we create. As for the rest of the changes there exist multiple ways to write the same code. Both, your version and mine are correct, it is just a matter of personal preference.

    Heinrich

  • Thanks for Sharing.

    Outstanding..

  • For those who desire to prevent duplicates - I have figured out a way:

    1) First - go to the Competitor's table and create a new query called "UnusedCompetitorsByOpportunity".

    The only thing you put in this query is

    Parameter OpportunityID of type Integer.

    2) Go to the preprocess query and write this:

           partial void UnusedCompetitorsByOpportunity_PreprocessQuery(int? OpportunityID, ref IQueryable<Competitor> query)

           {

               query = from competitor in query

                       where !competitor.OpportunityCompetitorMapping.Any(c => c.Opportunity.Id == OpportunityID)

                       select competitor;

           }

    3) Go to your screen - get rid of the Competitors data item.

    Add a New Data Item...    your UnusedCompetitorsByOpportunity query.

    Use that dataset to create your popup.  Viola!

  • oops - misspelled  - left off an 's'.  Here's the corrected version:

    query = from competitor in query

                      where !competitor.OpportunityCompetitorMappings.Any(c => c.Opportunity.Id == OpportunityID)

                      select competitor;

    And you also need to databind OpportunityID  to Opportunity.ID in the screen.

    Only issue - I'm not able to refresh the query without leaving the opportunity, going to another opportunity and coming back.  There must be a way to refresh/reload the query after choosing an opportunity so that when you click on the button the 2nd time, the one you just chose is already gone.

  • Did anybody find a solution to refresh the list of choices after choosing one item?

Page 1 of 1 (10 items)