Relating and Editing Data from Multiple Data Sources on the Same Screen

In my last article I showed how to connect LightSwitch to SharePoint data in order to pull users’ Task Lists into your LightSwitch applications. If you missed it:

Using SharePoint Data in your LightSwitch Applications

There we created a screen that pulled up the logged in users’ tasks from SharePoint so they could modify them directly. We created a new screen to do this which presented just the data from SharePoint. In this post I want to show you how you can relate SharePoint data to data stored in the database and then present that on a single screen. There are a couple lines of code you need to write when you want to edit data from multiple data sources regardless if that data is coming from SharePoint or another data source like an external database. For a video demonstration of this technique please see: How Do I: Save Data from Multiple Data Sources on the Same Screen?

Relating Entities Across Data Sources

One of the most compelling features in LightSwitch is its ability to relate entities (tables or lists) across disparate data sources. For instance you could have a product catalog database which only stores product details and relate that to another table in a completely separate order management database. Or you could relate a list of issues stored in SharePoint to a customer table in a database. In this example I’m going to do just that, I want to display Customer Issues stored in SharePoint on the same screen as my Customer details stored in my database.

So I’ve set up a list called Customer Issues based on the Issues template in SharePoint. I’ve modified the list to also include an additional required text field called Customer ID.

We’ll use this to relate the Customer record in our database. For this example, we’ll auto-generate the CustomerID in LightSwitch to be a GUID (global unique identifier) but you could choose to use any field shared across the data sources.  So I’ve created a Customer table in my LightSwitch application with the following properties:

Notice in the properties window that I’ve also set the CustomerID property to be included in the Unique Index and unchecked Display by Default since we are going to auto generate this value. Drop down the Write Code button at the top of the designer and select the Customer_Created method and write this code:

 Private Sub Customer_Created()
    Me.CustomerID = Guid.NewGuid.ToString
End Sub

Next we need to connect to SharePoint and relate our CustomerIssue list to the Customer. As I showed in the last post you can connect to SharePoint by right-clicking on the Data Sources node in the Solution Explorer (or click the “Attach to External Data Source” button at the top of the Data Designer), then select SharePoint as the data source type. Click Next and specify your SharePoint site address. LightSwitch will present all the Lists defined in the site. I’ll select CustomerIssues and that will automatically pull in the UserInformationList since this is related to CustomerIssues.

Click Finish and this will display the CustomerIssue entity in the Data Designer. Next we need to set up the relation to our Customer table. Click the “Relationship” button at the top of the designer and in the To column select Customer. Then you will need to select the Foreign and Primary keys below. This is the CustomerID field we added which is the “Primary” or unique key on the Customer.

Notice that a dotted line is drawn between related entities across data sources. This means that LightSwitch cannot enforce the referential integrity of the data but it can relate the data together. In other words, we cannot prohibit the deletion of a Customer if there are still Issues in SharePoint but we can relate the data together if it exists.

Creating Screens with Multiple Data Sources

Creating a screen that displays data across multiple data sources is a no brainer – it’s the same steps you would use for any other data in the system. Click the “Screen” button at the top of the Data Designer or right-click on the Screens node in the Solution Explorer to add a new screen. For this example I’ll use the List and Details Screen. Select the Customer as the screen data and include the CustomerIssues.

At this point let’s hit F5 to build and run the application and see what we get so far. Create some new customers in the system. Notice however that the CustomerIssues grid is read only. At this point we can’t edit any of the SharePoint data, notice how all the Add, Edit, Delete buttons are disabled.

Why is this happening? The reason is because LightSwitch needs some more information from us in order to save the data properly. Specifically, it needs to know the order in which to save the data. LightSwitch can’t arbitrarily pick an order because in most systems the order is very important. In our case I want to make sure the Customer data is saved successfully before we attempt to save any issues to SharePoint.

Enabling Editing of Data from Multiple Data Sources

What we need to do is write a couple lines of code. Close the application and go back to the CustomerListDetail screen we created. Drop down the Write Code button and select the CustomerListDetail_InitializeDataWorkspace and CustomerListDetail_Saving methods to create the stubs. In the InitializeDataWorkspace method you should notice a parameter saveChangesTo. This parameter is used to tell the screen to enable editing of the data, we just need to add the data sources we want to work with to the list. Then in the Saving method we need to specify the order in which we want to save the changes. To access the data sources you use the DataWorkspace object.

 Public Class CustomersListDetail

     Private Sub CustomersListDetail_InitializeDataWorkspace(
            saveChangesTo As System.Collections.Generic.List(Of Microsoft.LightSwitch.IDataService))

        saveChangesTo.Add(Me.DataWorkspace.ApplicationData)<br>        saveChangesTo.Add(Me.DataWorkspace.Team_SiteData) 
    End Sub

    Private Sub CustomersListDetail_Saving(ByRef handled As Boolean)
        Me.DataWorkspace.ApplicationData.SaveChanges()        Me.DataWorkspace.Team_SiteData.SaveChanges()<br>        handled =  
True
    End Sub
End Class

Now run the application again and this time you will be able to add, edit and delete Customer Issues and save them to SharePoint. And if you look in SharePoint you will see that LightSwitch automatically set the CustomerID for us as specified by the relationship.

By the way, this technique is not specific to SharePoint. You can relate data from other external data sources like databases as well and the same code would need to be added in order to support saving across them on a single screen. LightSwitch does almost all of the heavy lifting for us but it’s important in this case to be explicit about how our data is updated so that it ends up being saved correctly in any scenario.

Enjoy!

Comments

• Anonymous
  July 11, 2011
  Beth - what is going on with the web layout of your blog that it cuts off the leftmost letter when I try to print one of your posts?

• Anonymous
  July 12, 2011
  @Duke - Sorry, I just picked a basic template that the MSDN blog platform provides. I'll see if I can modify it.

• Anonymous
  June 28, 2012
  Thanks for the article Beth. I tried doing this and it works great, but I'm seeing a side effect that the <Screen>_Saved() method is no longer getting called. Is this expected? Should I be calling this method at the end of my <>_Saving method implementation?

• Anonymous
  May 15, 2013
  @Beth Thank you!!! I was struggling with the fact that I was unable to edit data... Regards

• Anonymous
  November 27, 2013
  hello Beth! can i implement editing Data from Multiple Data Sources on the Same Screen in the Lightswitch HTML? I have successfully realized  Adding Data to Multiple Data Sources, but i can't realize editing data from same screen. Can you help me?

• Anonymous
  February 12, 2014
  The comment has been removed

• Anonymous
  February 13, 2014
  The comment has been removed

• Anonymous
  January 06, 2015
  HI Beth, I am using two entity. First is sql database entity in Lightswitch second is RIA service Entity. I am able to edit both tables in a same screen but when I click on Save button, System call to [Query(IsDefault = true)] public IEnumerable<EquipmentJob> SelectFakeJobs()   method instead of calling [Query] public IEnumerable<EquipmentJob> SelectJobs(long? ID). What may be the reason.

• Anonymous
  April 08, 2015
  The comment has been removed