Many-to-many relationships are a common need in data-based applications. For example, I am putting together a simple issue tracking application that logs customer issues and organizes them by product. In this application, I want to be able to look at an issue and see a list of customers who have reported it and I also want to be able to look at a customer and see a list of issues that customer has reported. This scenario is an example of a many-to-many relationship. A customer can report many issues and an issue can be reported by many customers.
The bad news is that many-to-many relationships are not supported in LightSwitch. The good news is that there is an easy solution. Just use a junction table. The better news is that LightSwitch makes the wiring very easy.
In my scenario, there are three tables:
- Customers: List of people who have reported issues.
- Issues: List of issues that have been reported.
- CustomerIssueMaps: This is the junction table that stores the relationships between Customers and Issues. It has three columns: ID (primary key), IssueName (foreign key from the Issues table), and CustomerName (foreign key from the Customers table). Each row will match a customer with an issue. If there are multiple issues reported by a customer, there will be a separate row for each issue that customer reports.
To illustrate this, let’s say Jen Wilson reports three issues with a product called TimeManager. This is basically what the tables will look like.
If Scott were to report the same Time Zones issue that Jen did, then there would be a new row in CustomerIssueMaps with these values: Sott Olsen, Time Zones. In my application, I want to be able to select the time zones issue and see that Jen and Scott have reported it. I also want to be able to select Jen and see that she has reported the three issues.
How to Do it in LightSwitch
LightSwitch makes this very easy. Here is a tutorial starting with a blank project called IssueTracker.
Start by creating the three data entities described above.
Add a table, name it Issue, and add these properties:
- IssueName, string, required.
- IssueDescription, string, not required. Since descriptions are often relatively large blocks of text, we need to clear the Maximum Length value (it is set to 255 by default). To do this, clear the Maximum Length field in the Validation group of the Properties Pane.
Add another table, name it Customer, and add these properties:
- CustomerName, string, required.
- CustomerEmail, Email Address, not required.
Add the last table, name it CustomerIssueMap leave it blank. This table will be populated by creating relationships to the Issue and Customer tables, so the next step will be to create the relationships.
We will create a one-to-many relationship between the Issue and CustomerIssueMap tables and another one between the Customer and CustomerIssueMap table.
With the CustomerIsseMap table selected, click the Relationship button and the Add New Relationship window will open. The CustomerIssueMap table is selected by default in the From column. Select Customer in the Name: field in the To column and click OK.
You have just created the first relationship. Repeat the steps for the Issue table.
Now your tables and relationships are set up. We just need to create the screens that add and view data. We will add two editable grids to enter data and the two list and details screens to view that data.
Add an Editable Grid screen and select Customers for the screen data.
Repeat this for the Issues table.
Add a List and Details screen, select Customers for screen data, and check Customer CustomerIssueMaps box for additional data.
Add another Lists and Details screen, select Issues for screen data, and check Issue CustomerIssueMaps box for additional data.
The basic application is now modeled. The next step is to run it, enter data, and test.
Click the Start Debugging button (or press F5) to run the application.
The first thing to do is enter some data into the Customer and Issue tables. Your application should open with the Editable Customers Grid tab open. If it doesn’t, select it from the Tasks menu.
Enter a few records (as shown above) and make sure you click the Save button to commit your changes to the database.
Select the Editable Issues Grid from the Tasks menu and enter a few records. Again, don’t forget to click the Save button.
Now that we have some data, we can test the relationships.
Select Customers List Detail from the Tasks menu. You will see your list of customers on the with details (Customer Issue Maps) on the right.
You can now relate specific issues to customers. With Jen Wilson selected, click the Issue field in the Customer Issue Maps table and a drop down with your issues will appear. Select an issue and repeat for two more.
Select Scott Olsen and assign the Alerts Don’t Work issue to his record as well.
Now, select Issues List Detail from the Tasks menu and click on the issue records to see the customers you assigned them to.
This is a very simple example, but you can see how easy LightSwitch makes relationship building.