locked
2 Tables on 1 DataGridRow RRS feed

  • Question

  • I have a SQL Server DB with two tables the Table #1 (Movies) has a PK of movieID which is a foreign key with a 1 to Many multiplicity relationship with MoviesTitles because MovieTitles contains movie title in various localized strings (French, English, Spanish).  So MovieTitles has 3 fields (movieID, cultureID, localizedTitle).

    Is there a way to display the DataGridRow with the moveID from Movies and a single (e.g. English is always cultureID == 0) localized title in a DataGridRow?

    Why??  Because there are actually MANY more tables than this and the FK relationships in the DB all relate back to the Movies table so for linking to work to get to the details pages of Movies (e.g. Actors, Directors, Stage Hands, etc.) I believe I need the "Screen Data" for the initial Search screen to be Movies.

    Can someone give me a pointer on a way to accomplish this?

    Thanks in advance.


    - Clay Ver Valen
    Friday, December 3, 2010 4:46 AM

Answers

  • Yea the problem is LightSwitch can only look at the table through its point of view. Sounds wacky but when you think about it, it makes sense. If you ask LS to query from [MovieTitles] it will have no problem identifying the movie associated with each record because MovieTitle.MovieId = Movies.MovieID. Using this you can use a summary component to add all the columns you need.

    But looking from the Movie table there are no Columns that would help LS locate the associated records. Even looking at the former scenario LS doesn't process the request in a logical way, because it retrieves the initial batch of data that it can gather from 1 table and then fills in the remaining columns one by one. I actually got a response from the dev team on this issue, and they actually informed me it has already been addressed and will be in the next release. You can check it out here: http://social.msdn.microsoft.com/Forums/en-US/lightswitchgeneral/thread/0c33dadb-4d1f-4be6-8d2f-bcd4ab3f97b5 

    I'm sure future version of LS will be able to make some assumptions on the data that would make this kind of processes easier. At the moment, however, I think the only way to get information in the manner you're looking for quickly would be to create a view that would select everything that you could possibly need and have LS just display what you need at a particular screen. 

    Hope this helps.

    Sunday, December 5, 2010 9:35 PM

All replies

  • Well the way I would do it would be to create a view in your database that would select all the movies and their respective titles and then add this view in LightSwitch. Then on one of the screens I would modify the query for the view to filter according to a given language using a parameter.

    To illustrate:

    Step 1) Create view - Something like this:

    SELECT dbo.Movies.*, dbo.tbTitles.Title, dbo.tbTitles.cultureID
    FROM dbo.Movies INNER JOIN dbo.tbTitles ON dbo.Movies.id = dbo.tbTitles.MovieID

    Step 2) Add the view to your LightSwitch project the same way you added the original tables

    Step 3) create a screen on that view

    Step 4) Modify the query to filter - "where cultureID = [a paramater that you set to 0, 1, 2]

     

    I do it this way because it's easier for me to manage things from the database and speeds up retrieving the information because beta 1 has a problem retrieving data that is dependent on some relationship. Hope I made sense!

    (Apologies for double posting but for some reason the website wasn't formating my message on the previous reply)

    Friday, December 3, 2010 3:11 PM
  • Wessel,

    Thanks for the suggestion, but our DB is littered with these relationships where the string displayed to represent the data is treated as metadata and kept in a separate table.  I'd have to do a pretty siginificant amount of "selling" creating all of these views to support a prouct that is so far only in beta.

    Any other suggestions? Anyone?

    - Clay


    - Clay Ver Valen
    Sunday, December 5, 2010 7:40 PM
  • Yea the problem is LightSwitch can only look at the table through its point of view. Sounds wacky but when you think about it, it makes sense. If you ask LS to query from [MovieTitles] it will have no problem identifying the movie associated with each record because MovieTitle.MovieId = Movies.MovieID. Using this you can use a summary component to add all the columns you need.

    But looking from the Movie table there are no Columns that would help LS locate the associated records. Even looking at the former scenario LS doesn't process the request in a logical way, because it retrieves the initial batch of data that it can gather from 1 table and then fills in the remaining columns one by one. I actually got a response from the dev team on this issue, and they actually informed me it has already been addressed and will be in the next release. You can check it out here: http://social.msdn.microsoft.com/Forums/en-US/lightswitchgeneral/thread/0c33dadb-4d1f-4be6-8d2f-bcd4ab3f97b5 

    I'm sure future version of LS will be able to make some assumptions on the data that would make this kind of processes easier. At the moment, however, I think the only way to get information in the manner you're looking for quickly would be to create a view that would select everything that you could possibly need and have LS just display what you need at a particular screen. 

    Hope this helps.

    Sunday, December 5, 2010 9:35 PM