How To Build A Left Right Choose Box

Introduction

A many to many relationship is where one person can belong to more than one group and a group can contain more than one person. In a database schema, you cannot have a foreign key in the person table that references the group table because it points to only one row where the requirement is that a person can belong to more than one. So what you have to do is add a table called PersonGroup with a multi part key of PersonId plus GroupId. This is a fairly common basic concept of database design. And yet, when I search for a graphical dialog tool for this feature, I find little.

Background

Perhaps I need a better name for it. I call it a left-right choose box but I must be missing a common description. As shown in the banner image, a choose box consists of two columns with list items and a middle column with four arrow icons. From top down, the buttons represent Add one, Add all, Remove one, and Remove All. The add and remove all options may be applicable in some cases but in general, are dangerous and should be disabled. The functionality required includes methods to load the list boxes and to add and remove entries in the many to many tables, in this case, UserRoles.

The Code

Here is the full extent of the HTML. Inside a container are three rows; divChooseAvailable, divChooseButttons, and divChooseAssigned. The rest is handled in JavaScript. The example here being used is assigning roles to users. The database handling of this is a special case. I will show in a different article that the data table being affected here is AspUserRoles which is unique to the ASP OAuth Owin authentication system, in this case, the authorization system actually. It is a nightmare to try to manipulate these tables programmatically because of cookies or something. It is possible to bugger with the AspUserRoles table in SQL Management Studio, but they contain little more than guid keys. The proper way to manipulate the roles table is to set up an ApplicationRoleManager class in a special class called Startup decorated with the OwinStartup attribute. I have written a special controller class that exposes RoleManager methods. I will add a link to that article soon.

<div class="crudContainer AssignRolesContainer">
    <div class="crudContainerTitle">Assign Roles</div>
    <div class="flexContainer">
        <div><select class="crudDropDown" id="ddUsers"></select></div>
    </div>
    <div class="chooseBoxContainer" id="divRolesChoose">
        <div class="chooseBox floatLeft" id="divChooseAvailable"> 
        <div class="chooseBtnsContainer floatLeft" id="divChooseButtons">
            <img class="chooseBtn" 
            id="imgAdd" src="Images/IntelDsgn/sglright.jpg" /> 
            <img class="chooseBtn disenabled" 
            id="imgAddAll" src="Images/IntelDsgn/dblright.jpg" />
            <img class="chooseBtn" 
            id="imgRemove" src="/Images/IntelDsgn/sglleft.jpg" /> 
            <img class="chooseBtn disenabled" 
            id="imgRemoveAll" src="/Images/IntelDsgn/dblleft.jpg" />
        </div>
        <div class="chooseBox floatLeft" id="divChooseAssigned"</div>
    </div>
</div>

Conceptually, when an item in one list is selected to be moved to the other list, an add or remove action is performed at the database level and at the display level one list is shortened by one item and the item is added to the other list. Simple. Right? I will show how that is done, but first some other things have to happen.

The dialog initial state shows no user selected and all the roles are in the left hand list box; the Available items list. Before you do any adding or removing, first a User has to be selected. You will notice on top of this dialog, there is a <select> dropdown where you choose the pivot element of the many-to-many relationship. An onChange() event on the select drop down remembers the selected user in globally scoped (in this page's js script block) variable. A warning message will insist you select a user before you try to add roles.

Before we look at some functionality, let me ask you a question. Where would you put the code for this onchange event? It seems like a mundane point but it reveals an important principle of JavaScript programming that of course all of you who are smarter than me already know, but is worth pointing out. Where do you put the onclick event for the items in a list box?

The Difference Between a Compiler and an Interperter

When you press F6 in Visual Studio to "Build" your code, all the files in the OBJ folder and in the BIN folder are created. Normally, only the files that have been touched since the last compile are rebuilt but you can totally blow away these folders and they will be completely rebuilt the next time you build. Sometimes, this is a good idea. That's why there's a Clean option on the Build menu. A compiler steps through all your code and it pulls in libraries and references it needs when it needs them to get everything to work. You can put pieces of your code wherever you want and the compiler will try to find them and make sure everything works. An Interpreter works differently.

An interpreter takes one line of code, performs it and moves on to the next line. Obviously, this is a lot simpler. Scripting languages like JavaScript are interpreted code. Visual Studio only can compile your C# server side code. This is why frameworks like Angular are all about setting includes and references in a base module to overcome the step-by-step, top/down behavior of client side script. Understanding how an interpreter works is important if only for better awareness of where to put things.

The answer to my question is that you have to place the code for event handlers such as onchange or onclick AFTER the list items has been created. If you have an Ajax call that returns the elements for your list and it has a success or callback function, first you populate your list or in the case of a select tag, you append the list items as option tags, in a loop. Only then do you put the code for the event handler. The items you expect to respond to the event handler have to exist first before they can be told to attach the event. As my great uncle Olaf always used to say; Pillage an THEN burn. Sequence is important. How can you pillage if you've already burnt everything down? Notice that embedded in the onSuccess callback is the onClick code.

function loadAllItems() {
    try{
    // $('#divLoadingGif').show();
    $.ajax({
      type: "GET",
      dataType: "Json",
      url: "/Admin/GetAllRoles",
      success: function (result) {
        $('#divChooseAvailable').html("");
        $.each(result, function (idx, obj) {
            $('#divChooseAvailable').append
            ("div class="availableListBoxItem chooseBoxListItem" 
            id=""+ obj.Id + "">"+obj.Name+"/div>;);
        }); 
        if (!isNullorUndefined(selectedUserId)) {
            getRolesAssignedToUser(selectedUserId)
        }
        $('.availableListBoxItem').click(function () {
            $('.chooBoxListItem').removeClass("highlightItem"); 
            if (isNullorUndefined(selectedName)) { 
                $(this).addClass("highlightItem"); 
                selectedRoleType = "Available"
                selectedRoleName = $(this).text(); 
            }
            else { 
                if (selectedRoleName === $(this).text();) { 
                    selectedRoleId = "" } 
                else { 
                    $(this).addClass("highlightItem"); 
                    selectedRoleType = "Available");
                    selectedRoleName = $(this).text(); 
                } 
            } 
        }); 
    }, 
    error: function (jqXHR, exception) {
        alert("error: " + getXHRErrorDetails(jqXHR, exception)); 
    }, 
    }); 
    } catch (e) { 
        //displayStatusMessage("error", "catch ERROR: " +e); 
        alert("catch: " + e); 
    }     
}

The function of loading all items in the left side Available list and the function of loading of the Assigned items in the right side list are two distinct processes. After debugging and refactoring, as understood from my discussion about the importance of the proper workflow sequence, the most reliable solution is to merge these two functions and ensure that the one function occurs after the other. Notice that in addition to click event, also embedded in the onSuccess callback of the function that loads all available items is the call to the next function that loads the available roles assigned to a selected user in the other column.

When you come back from the first database, the first thing you do is loop through the result set and append the Role Names into the Available list. This essentially brings them into existence, creates the elements. The onClick() function can now be instantiated. I put the call to the load available function next because its Ajax call executes asynchronously so it falls right through but it takes a millionth of a microsecond which is plenty of time in computer time for the available list item to firm up and complete whatever finish up processes might be involved.

The Visual Interface

Another thing that has to happen before an item can be added or removed is that it has to be selected. Selecting this option is the entire purpose of the UI. A left-right choose box is not the only interface possible for handling many-to-many relationships. Cascading dropdown boxes are another popular solution. I hate to say it but I remember before HTML came out with the select tag in 1995. (We used to build them by hand.) The ASP combo box control introduced the multiselect feature. But nothing better visually represents the many-to-many process than a choose box. I have searched the web and you're not going to find a better implementation.

When a user clicks on an item, a visual clue has to be provided. The click event applies to the class; all items of class .avaliableListBoxItem. I like to use different class names for (most) classes which are used only for styling and class names that are used programmatically. The $(this) tag allows us to specify an individual item of the class without having to know it at build time, the particular one clicked. .addClass() gives it the highlight style. Notice that the first line of the function removes the highlight class on the entire class so whichever item was previously selected is already unhighhlighted. The this tag highlights just one.

One more thing. An interesting challenge when dealing with a many-to-many tables is that you often don't have a unique primary key to work with. The AspUserRoles table consists of only two columns; the UserId and the RoleId. So you have to deal with composite keys. This is fine but the way Microsoft OWIN handles it is to rely on the RoleName so I'm sure this must be the right way. There is a probably unique constraint on the Name column in the AspNetRoles table. This article is not about OWIN or authorization roles, but just to point out that managing many-to-many tables can bring up this specific challenge.

Functional Behavior

There are three states tested when a list item is clicked. If we are just starting or no items are selected, the isNullorUndefined() option sets the selected item. isNullorUndefined() is in my global.js file and its function is obvious. SelectedRoleType is used to prevent adding when an assigned item from the right column selected it or prevents deleting if an item from the left side available column is the selected item. These special case bug trapping features often take up more code and verbose explanation than the working code.
The second if is a special case where the currently already selected item is clicked again. Since the class level removeClass command has already restyled everything to default style, nothing more really has to be done but this visual cue makes the interface more intuitive since this seems like the expected behavior. The last case is the default. It sets the clicked on list item to selected and highlights it.

$(".chooseBtn").click(function () {
       if (isNullorUndefined(selectedUserId)) {
           displayStatusMessage("warning", "please select a user");
       }
       else {
           if (isNullorUndefined(selectedRoleName)) {
               alert("Please select a Role.");
           }
           else {
               switch ($(this).attr("Id")) {
                   case "imgAdd":
                       if (selectedRoleType == "Available")
                           addUserRole(selectedUserId, selectedRoleName);
                       else
                           displayStatusMessage("warning", "no available item selected")
                       break;
                   case "imgAddAll":  //  disabled
                       //$('#divChooseAssigned').html("");
                       //$('#divChooseAssigned').append($('#divChooseAvailable').html());
                       //$('#divChooseAvailable').html("");
                       //perfom special role function
                       break;
                   case "imgRemove":
                       if (selectedRoleType == "Assigned")
                           RemoveUserRole(selectedUserId, selectedRoleName);
                       else
                           displayStatusMessage("warning", "no assigned item selected")
                       break;
                   case "imgRemoveAll":  //  disabled
                       //$('#divChooseAvailable').append($('#divChooseAssigned').html());
                       //$('#divChooseAssigned').html("");
                       //perfom special role function
                       break;
                   default:
                       alert("$(this).Id :" + $(this).attr("Id"))
               }
           }
       }
   });

Here, we have a case statement that handles the buttons in the center column. This article doesn't show a complete plug in ready to use. It is an example of a design pattern that can be customized. The database calls will be specific to an implementation. But, it is not an especially large or complicated form.