Use of Junction Tables in Lightswitch (Data in MS SQL Express) RRS feed

  • Question

  • Hi,

    I have an SQL Server Express database as the data source for a lightswitch application I am developing.

    I have a number of junction table relationships (http://en.wikipedia.org/wiki/Junction_table), between sets of three tables, which take the form:

    [Table 1] -- 1 to Many -- [Table 2] -- Many to 1 -- [Table 3]

    Effectively, Table 2 is made up of foreign keys (integers) from Tables 1 and 3, with some supporting data about the relationship being represented (such as the datetime it was recorded).

    An example is where Table 1 represents a Person, Table 3 represents a Company, and Table 2 is a list of employment records (with the two foreign keys and start/finish dates). Obviously there can be many records in Table 2 relating to any record in Tables 1 and 3.

    In lightswitch, I wish to be able to show the detail screen for Table 1, and include all of the related records from Table 2, but I don't want to display the foreign key, but rather the relevant fields from Table 3.

    I'd also like to be able to add new records, with the foreign key bound to whatever the screen context is (i.e. if I am in the screen for table 1, I only want to define the FK value for Table 3).

    I can generate the data I want by through a query in SSMS (see below), but I've got no idea how to use this in lightswitch.

    SELECT Person.PersonUID, Person.Fullname, EmploymentRecord.StartDate, EmploymentRecord.EndDate, Organisation.OrganisationName, EmploymentRecord.EmployerFK
    FROM Person
    LEFT JOIN  EmploymentRecord ON Person.PersonUID = EmploymentRecord.PersonFK
    LEFT JOIN Organisation ON EmploymentRecord.EmployerFK = Organisation.OrganisationUID 

    I have other similar situations where Table 3 is a lookup table of static values, and Table 2 provides a historical record of a value from Table 3, relating to Table 1 (rather than linking table 1 directly to table 3 and recording the value changes somewhere).

    I've probably bitten off more than I could chew for a first attempt at designing a "proper" database, but I will be the primary user and administrator, so have been trying to make it as robust and flexible as possible.

    All of my database knowledge is self-taught and ad-hoc, so please excuse any misuse of jargon!



    • Edited by EngIT Wednesday, February 13, 2013 7:07 AM Updated to clarify the title
    Tuesday, February 12, 2013 9:38 AM

All replies

  • What you have here is an opportunity to describe what LightSwitch does very well.

    First set up your relations:

    1. It sounds like this is an existing database, so my preference is to use the database diagrams in SQL server to represent the relationships.  Set the relationships in your database well, and the 1/2 the project is done when you use LightSwitch.
    2. In LightSwitch from the Data Sources' right click menu, select, Add Data Source
    3. If you have set your realationships in your database correctly, the relationships will be represented in LightSwitch already.  No additional work! (this is what I think is REALLY cool)

    Set up your screen

    1. Right Click on Screens and choose Choose "Add Screen"
    2. Choose your screen type.  For this example I will use the New Data Screen... no data added by default... but you could do whatever type.
    3. Click on Add Data Item.
    4. Choose Query and select your Parent Table (your Person Table)
    5. You will see your Query added in the left column.  Click on the Add your Link Table (your EmploymentRecord Table).
    6. That will add your Linking Table on the left.
    7. In your example, the automatic ACB (Auto Complete Box) will be there on employment to link to the organization table, so no need to add the Child Table for that purpose).
    8. For this example, I'll use all Grids: So in the designer click on ADD, then select your Parent Table, leaving the data in a grid.
    9. Do this again for your Linking table, adding it as a grid.
    10. Now DELETE any columns you don't want to show
    11. Run

    That should give you exactly the data and how it should be viewed AND allow you to EDIT any Parent or Linking Table fields.

    I know your post is old, so you may have it already done, but what you are trying to do, is what Lightswitch does out of the box with very little effort, ONCE you get into it.

    Any of you LightSwitch Veterans are welcome to pick-appart my example and give me and EngIT the benefit of your time-tested advice!


    <<-- Did I help you? Please oh please mark my comment as helpful: I finally have a few points (thanks Yann!) (BIG GRIN!) (JoeB)

    • Proposed as answer by JoeB_LS103 Monday, March 25, 2013 7:15 PM
    Tuesday, February 26, 2013 5:43 PM
  • http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/47/WCF-RIA-Service-Combining-Two-Tables.aspx
    Wednesday, February 27, 2013 10:58 PM
  • This post explains how to do what you want.

    Thursday, February 28, 2013 4:06 AM