locked
Many-To-Many Checkbox List?

    Question

  • Hi,

    We have two Tables, joined by many-to-many relationship on the third. Ex:

     

    Table : Category

    Column : CategoryId

    Column : CategoryName

     

    Table : Project

    Column : ProjectId

    Column : ProjectName

     

    Table : ProjectsInCategories

    Column : ProjectId

    Column : CategoryId

    On my View, I have all the Projects listed on a datagrid.  Is there anyway I can show all my Categories on a group collection (Checked ListBox / Datagrid with Checkbox column) and show (checked/unchecked) which Categories are belong to a selected particular project?

     

    I also want to add/update and delete records in the same manner by check/uncheck them. I'm trying to implement this because adding records one by one on a datagrid would be tedious due to number of relationships involved. 

    Monday, March 28, 2011 9:07 PM

Answers

All replies

  • HI,

     

    the only solution i see is to

    1. add field bit (for checkbox) to table ProjectsInCategories

    2. insert all categories into ProjectsInCategories for each project

    3. check / uncheck your boxes

    4. run sql statement to remove all unchecked category entries

     

    If you have to do this only once it might be a solution

     

    Best, Joe

    Monday, March 28, 2011 10:33 PM
  • Joe

     

    Unfortunately, this is not the solution I'm looking for, bcas the data are dynamic and can't predict the combinations.  I have done this in asp.net with SQL and CheckBoxList.  Guess, I may have to work with Silverlight controls with custom code, I believe.

     

    Thx for your reply. :)

    Tuesday, March 29, 2011 12:33 AM
  • I was able to pull one solution myself.  Here is the link.

    http://powerbala.com/2011/04/10/many-to-many-control-for-microsoft-lightswitch/


    -Bala
    • Marked as answer by PowerBala Monday, April 11, 2011 6:45 AM
    Monday, April 11, 2011 6:44 AM
  • I just implemented a many-to-many checkbox list that's a little simpler if anybody is interested.

    Say I have Movies, and Genre's and I need to tie them together.

    I will have three tables:

    Movies, Genres, and MovieGenres. I tie them together using the method outlined here:

    http://blogs.msdn.com/b/lightswitch/archive/2010/12/16/how-to-create-a-many-to-many-relationship-andy-kung.aspx

    What I did was add a boolean field to Genres. Then, on a details screen pointed at Movies, I add a seperate query that pulls all of the Genre table in and puts it in a datagrid. The data grid should have the command bar empty, search and paging disabled. Some of this happens on the screen, adn some happens at the table level. What you will get is a list of Genres that have checkboxes next to them.

    I don't put any controls from the MovieGenre tablet onto the screen. Instead, I use the checked items in Genres to add or remove items from the link table in code. This goes in the _Saving method.

    //Clean out the existing links
    foreach (var item in this.MovieProperty.MoveGenres)
    {
    
     item.Delete();
    }
    
    
    //Add the new ones based on what is checked in the Genres table
    foreach (var item in Genres)
    {
    
     if ((bool)item.IsSelected)
     {
      var mg = this.MovieProperty.MovieGenres.AddNew();
      mg.Movie = this.MovieProperty;
      mg.Genre = item;
    
      //Make sure that no checked items get saved back to the Genres table
      item.IsSelected = false;
     }
    }
    
    Then do this in _InitializeDataWorkspace:
    foreach (var item in this.MovieProperty.MovieGenres)
    {
      Genres.Where(g => g == item.Genre).First().IsSelected = true;
    }
    It seems to be working so far.


    Thursday, April 28, 2011 4:24 PM
  • Hi Bala,

    Love your solution btw! Very clever!

    The thing that concerns me about *any* using of checkboxes to make multiple selections, is what happens if two users are doing the same thing at the same time. One user ticks a particular set of entities, & the other user ticks maybe a different set. It may not happen *every* time, but the potential exists for their selections to become "mingled", does it not?

    Yann

    Friday, April 29, 2011 12:05 AM
  • Thanks for your kind words Yann. :)

    But as usual you think to complicated compared to my level. :)  Thats a notable point but the problem may occur on other M2M solutions too, isn't it?  

     

    I mean, if the table is not user based (MovieId, GenreId, UserId), and two users access a table (MovieId, GenreId) at same time, then what you have mentioned above is always possible, right?  I hope I got the question right.

    [Couldn't be active like b4, as our server crashed last week and I'm still working on it.]


    -Bala
    Friday, April 29, 2011 5:50 AM
  • Bala,

    No, I meant that the grid should allow multiple selections.

    Yann

    Friday, April 29, 2011 7:07 AM
  • I think that would still be prone to getting messed up if more than one user was doing it at the same time.

    Multiple grid selection is the only clean answer.

    Yann

    Friday, April 29, 2011 7:09 AM