none
LightSwitch 2013 not showing related data

    Question

  • I have set up a simple Visual Studio LightSwitch 2013 HTML project connected to an external datasource (SQL Server 2008 R2). For some odd reason, when I create a view details screen I can't see any related data to the parent record. This is with an HTML client

    For instance, I have Job, Job_Operation, and Material_Requirements table. Jobs have several Job_Operations and Material_Requirements. I have a View Job screen that is supposed to list the related Job_Operations and Material_Requirements in a list on separate tabs, but the app is telling me that there is "No Items" when I view the tabs. When I inspect my data in SQL Server Management Studio, there definitely are related entities.

    Also, I have setup the same small app in Visual Studio LightSwitch 2012 (using the exact same datasource, credentials, and tables) and it works just as expected showing the related entities.

    Any ideas on why this is hosed for me in Visual Studio 2013? Also, this is the first project that I have tried to make since I installed it last night.

    Sunday, February 23, 2014 5:17 PM

Answers

  • Sorry for all these trouble. This issue is fixed lately.

    For now, if you can't change the primary key, here's another work-around, which is a little bit hacky:
    Generally, we intend to change the primary key name in lightswitch model back to align with the field name in the database.

    Detailed steps:
    0. Backup your solution.
    1. Open the solution in Visual Studio, note down the primary key name for Job entity in entity designer - it is 'Job1' here.
    2. Change the entity name for your Job entity (Not the primary key name here). For example, change it to 'JobItem'.
    3. Save & Close the solution.
    4. Open the entity lsml file in notepad:
    <ProjectDir>\<ProjectName>.Server\DataSources\<DataSourceName>\JobItems.lsml.
    5. Repace all 'Job1' to 'Job' and save it.
    6. Open the solution in visual studio again.
    7. Fix the reference issue for screens according the error list in Visual Stuido. (Bascially, replace 'Job1' with 'Job').
    8. Close and reopen the solution again.
    9. F5 to debug.
    The issue should be fixed.


    • Edited by Saar Shen Thursday, February 27, 2014 8:11 PM
    • Marked as answer by cm.smith Friday, February 28, 2014 2:43 PM
    Thursday, February 27, 2014 8:11 PM

All replies

  • Well, I completely started a new project with the same intention of pulling in related entities after I updated to VS 2013 Updated 1. I still have the same issue; no related entities are showing in their tabs in the LightSwitch HTML client. They are even showing that they are related in the table designer as well as the interface designer. 
    Monday, February 24, 2014 1:49 AM
  • Even after upgrading the working project from VS LightSwitch 2012 the same issue of not showing related data happens. Seems like a serious issue for me and the database that I'm working with.

    Any ideas out there are much appreciated.

    Monday, February 24, 2014 1:52 AM
  • I have also verified that the SilverLight client works as expected. Any related data (Jobs to Job_Operations and Material_Requirements) are shown as expected. This appears to be the way that LightSwitch handles the HTML client portion of the app.
    Monday, February 24, 2014 6:20 PM
  • Sorry you're having trouble Chris.

    If you create a new project and create two tables in a one-to-many relationship using the internal database, does it work? I'm trying to figure out if it's something with the SQL 2008 database. What data types are your PK/FK in the SQL 2008 db?

    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Tuesday, February 25, 2014 5:40 PM
  • Do you mean using the onsite ERP DB that I'm trying to get to work? Or create my own ApplicationData?
    Wednesday, February 26, 2014 12:41 AM
  • Below is the Job table. For every job there are multiple Job_Operations:

    dbo.Job:

    Job (PK, Key10(varchar(10)), not null)

    Sales_Rep (FK, Key6(varchar(6)), null)

    Customer (FK, Key10(varchar(6)), null)

    Ship_To (FK, Key10(varchar10)), null)

    User_Values (FK, KeyNum(int)), null)

    And the Job_Operation table:

    dbo.Job_Operation

    Job_OperationKey (PK, KeyNum(int), not null)

    Vendor (FK, Key10(varchar10)), null)

    Work_Center (FK, Key10(varchar(10)), null)

    Job (FK, Key10(varchar10)), null)

    I add these two tables to my 2013 LightSwitch app and then create a BrowsJobs screen and a ViewJob (and make it the Default view screen and also add Job_Operations as related entities) screen. On the Tap method on the browse list, it goes to the ViewJob screen. The Job data loads with no issue, but when you tap on the Job Operations tab, you get the dreaded "No items".

    I have verified this to be the case on my development machine as well as the server, Chrome and IE both do the same, and it doesn't work on iOS or Android. I have also verified that there is in fact related data both through SQL Manager and also but creating desktop SilverLight screens in the same project. The SilverLight screens show the related data without issue. 

    Another thing I have tried is instead of using the built in related data facilities in LightSwitch (adding the related data in the designer by clicking on the the link in the Job entity for Job_Operations), I have tried to create my own queries that use another field in the Job and Job_Operations tables. The Job table has a GUID field called Holder_OID and it corresponds to another GUID field in the Job_Operation table called Job_OID. If I create a query to filter Job_Operations by Job_OID and pass the Holder_OID from the Job to the Job_Operations table through a parameter, it still shows "No Items" in the HTML client. The SilverLight client works just fine.

    Also, this all works perfectly in Visual Studio 2012.

    As you can see, I've been down a lot of roads and can't make this work for anything. 

    Wednesday, February 26, 2014 12:53 PM
  • That definitely sounds like a bug. I have a tester on the team looking into it. Sounds like a problem with varchar keys. We'll keep you posted.

    Can you verify that creating a new project, adding a new parent and child table to the internal database (just "add table" through the data designer), and relating those together does work in your case?

    Thanks,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Wednesday, February 26, 2014 3:50 PM
  • I added Parent and Child tables. One-to-many relationship. Created a browse, create, and view screens. I created the children inside of the interface as well as adding it directly in the dev DB through SQL Manager. The related entities are showing as expected.
    Wednesday, February 26, 2014 4:25 PM
  • That definitely sounds like a bug. I have a tester on the team looking into it. Sounds like a problem with varchar keys. We'll keep you posted.

    Can you verify that creating a new project, adding a new parent and child table to the internal database (just "add table" through the data designer), and relating those together does work in your case?

    Thanks,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com


    Thanks, Beth!
    Wednesday, February 26, 2014 5:26 PM
  • Below is the Job table. For every job there are multiple Job_Operations:

    dbo.Job:

    Job (PK, Key10(varchar(10)), not null)

    Sales_Rep (FK, Key6(varchar(6)), null)

    Customer (FK, Key10(varchar(6)), null)

    Ship_To (FK, Key10(varchar10)), null)

    User_Values (FK, KeyNum(int)), null)

    And the Job_Operation table:

    dbo.Job_Operation

    Job_OperationKey (PK, KeyNum(int), not null)

    Vendor (FK, Key10(varchar10)), null)

    Work_Center (FK, Key10(varchar(10)), null)

    Job (FK, Key10(varchar10)), null)

    Hi,

    Could you please change the PK of 'Job' table from 'Job' to 'JobID', upgrade the data source in LightSwitch and try again?

    Thanks,
    Saar


    • Edited by Saar Shen Wednesday, February 26, 2014 10:25 PM Highlight the code.
    Wednesday, February 26, 2014 10:21 PM
  • Not sure if his will be helpful or not, but I was using a view in LightSwitch that was stuck using a varchar PK.  Any screens based on the default table would do all sorts of strange things.  Sometimes, no items, sometimes items but far less than the 45 records per page, etc.  All problems were solved by building a simple query testing the PK field for null.  Screens built on that query run great.
    Wednesday, February 26, 2014 10:54 PM
  • This is an external data source. I can't change it because it is being used by our production ERP system. I suppose I could make a copy of the DB and modify it, but I won't be able to change this in the production DB if it works. I'll update in a little while.
    Thursday, February 27, 2014 12:24 AM
  • Also, if I just update the Job table with JobID, won't it break the relationship to Job_Operations?
    Thursday, February 27, 2014 12:25 AM
  • Sorry for all these trouble. This issue is fixed lately.

    For now, if you can't change the primary key, here's another work-around, which is a little bit hacky:
    Generally, we intend to change the primary key name in lightswitch model back to align with the field name in the database.

    Detailed steps:
    0. Backup your solution.
    1. Open the solution in Visual Studio, note down the primary key name for Job entity in entity designer - it is 'Job1' here.
    2. Change the entity name for your Job entity (Not the primary key name here). For example, change it to 'JobItem'.
    3. Save & Close the solution.
    4. Open the entity lsml file in notepad:
    <ProjectDir>\<ProjectName>.Server\DataSources\<DataSourceName>\JobItems.lsml.
    5. Repace all 'Job1' to 'Job' and save it.
    6. Open the solution in visual studio again.
    7. Fix the reference issue for screens according the error list in Visual Stuido. (Bascially, replace 'Job1' with 'Job').
    8. Close and reopen the solution again.
    9. F5 to debug.
    The issue should be fixed.


    • Edited by Saar Shen Thursday, February 27, 2014 8:11 PM
    • Marked as answer by cm.smith Friday, February 28, 2014 2:43 PM
    Thursday, February 27, 2014 8:11 PM
  • Saar,

    Changing the Job field to JobID does in fact make this work. I can't change this though as it is our ERP system DB and will break the ERP system. 

    So, is this a bug? Is LightSwitch working with some convention of key name + ID? Any idea on a workaround for this?

    Friday, February 28, 2014 2:01 AM
  • Hey Saar,

    Yep, it's a tad bit hacky, but it works fine this way. 

    Will there be an update to fix this?

    Friday, February 28, 2014 2:42 PM
  • Sorry again for the trouble and glad to hear this works. There will be an update. I don't know the schedule. Beth may know better there. Thanks, Saar
    Friday, February 28, 2014 3:56 PM