Saving Data Across One-to-One Relationships

I’ve had a couple questions recently on how you could add data to tables that have a one-to-zero-or-one relationship in Visual Studio LightSwitch. Depending on whether the related record is mandatory (required) or not you have a couple different options when inserting data. So in this post I’ll show you a couple different options for doing that in both the desktop and mobile clients.

Our Data Model

Let’s take a simple data model of Customer and a related table CustomerNote. A customer can have zero or one note as defined by the relationship.

When we create our Customer screens, LightSwitch will automatically bring the CustomerNote fields in as well. For edit screens this is exactly what we want because this allows the user to edit fields across both tables on one screen. However, on AddNew screens, LightSwitch will not automatically add a new record to the CustomerNote table – we need to write code for that depending on if the note is mandatory or not.

Adding Code to the Silverlight Client

In order to support this in the Silverlight desktop client we need to write some VB (or C#) code.

Mandatory: Always add a related record

If you want to always add a related record, use the _Created event. Open the data designer to the parent record (in my case Customer), select the DesktopClient perspective and then write code in the _Created event to add the related record every time the parent is inserted.

Write the code in bold.

VB:

 Public Class Customer
    Private Sub Customer_Created()
Me.CustomerNote = New CustomerNote () 
    End Sub
End Class

C#:

 public partial class Customer
{
    partial void Customer_Created()
    {
this.CustomerNote = new CustomerNote (); 
    }
}

Now when we run the desktop client and add a new customer, the note fields are enabled.

Optional: Allow the user to decide

If the related record is optional and you want to have the user decide, add a gesture (like a button) to your screen. Open the screen designer and add a button, select “Write my own method” and call it AddNote, then edit the CanExecute and Execute methods.

VB:

 Private Sub AddNote_CanExecute(ByRef result As Boolean)
    'Only enable the button if there is no note
    result = (Me.CustomerProperty.CustomerNote Is Nothing) 
End Sub

Private Sub AddNote_Execute()
    ' Add the note
Me.CustomerProperty.CustomerNote = New CustomerNote() 
End Sub

C#:

 partial void AddNote_CanExecute(ref bool result)
{
    // Only enable the button if there is no note
result = (this.CustomerProperty.CustomerNote == null ); 
}

partial void AddNote_Execute()
{
    // Add the note
    this.CustomerProperty.CustomerNote = new CustomerNote (); 
}

Adding Code to the HTML Client

In order to support this in the html client we need to write some JavaScript code.

Mandatory: Always add a related record

Open the data designer to the parent record, but this time select the HTMLClient perspective. Then write JavaScript code in the _Created event to add the related record every time the parent is inserted.

 myapp.Customer.created = function (entity) {

    entity.CustomerNote =  new myapp.CustomerNote(); 
};

Notice the use of the myapp object here. You can use the myapp object in a variety of ways in order to access data and screens in the HTML client. Check out some other uses here- How to: Modify an HTML Screen by Using Code

Now when we run the HTML client and add a new customer, the note fields are enabled.

Optional: Allow the user to decide

Open the screen designer and add a button, select “Write my own method” and call it AddNote, then edit the CanExecute and Execute methods. Then write this code:

 myapp.AddEditCustomer.AddNote_canExecute = function (screen) {
    // enables the button (returns true) if the note doesn't exist
return (screen.Customer.CustomerNote == null ); 

};
myapp.AddEditCustomer.AddNote_execute = function (screen) {
    //Add a new note
screen.Customer.CustomerNote = new myapp.CustomerNote(); 
};

Wrap Up

So those are a couple different ways you can manage data participating in one-to-zero-or-one relationships. Keep in mind that these tables are in the same data source, but they don’t have to be, you can set up virtual relationships this way as well. If you do have separate data sources, however, you’ll also need to tell LightSwitch the order in which you want to update them. For more information on that see:Customize the Save command to save to multiple data sources

Enjoy!

Comments

• Anonymous
  May 15, 2013
  Hi Beth,Thanks for the article, it was much needed.Just a little tip in case anyone gets the same problem as I did - make sure the "child" (notes) table isn't in a "something to one" relationship with any other tables. Any downstream relationships must be 0...1 or many or the notes record won't save. :S

• Anonymous
  May 17, 2013
  Hi again,I seem to be having real issues with this, in the HTML client.Do you know why the child record would be un-editable after adding it?And in a different case does the child record does not show up on the parent screen, even when it exists, and is shown on its own browse screen, but in the parent screen its fields are grey out.Any help would be much appreciated, I've been pulling my hair out for days on this..

• Anonymous
  May 18, 2013
  thanks

• Anonymous
  May 20, 2013
  Hi Stuart,Are your tables in the same data source or separate data sources? What is different about your data model than the one described above?

• Anonymous
  August 06, 2013
  I tried to use this with a 1-to-many-relationship but only wanting to initially create one optional record.  Eact time I tried to use this similiar code, "Me.CustomerProperty.CustomerNote = New CustomerNote()"in a Desktop Client app to create the new record, I get an error stating that my property is readonly.  I've actually created an editable screen to be a document library.  I need to connect the parent record called Resolution with child(ren)  document records called FileInformation but am unable to connect the relationship.  When I go to "SAVE" the document in the screen , it fails on a error that states I don't have the "Resolution".  I thought perhaps if I could create the child record and fill it then I maybe able to "SAVE" it.  Ultimately,  I need to have all my documents attached to its parent but be able to perform "Upload", "Download" and Open File"  functions.  Any help would be appreciated.

• Anonymous
  January 19, 2014
  Thanks a lot for this article ..it saved my day

• Anonymous
  February 12, 2014
  Hi Beth,Firstly, Thank you soo much for all the amazing work you are doing.I am developing an application in lightswitch html client (VS2013) for a business and am struck.Here is the Scenario : have two tables Product and Special product ( the products always have general fields but special product have some additional fields) and they have 1-0/1 relation (I have basically used the same key in both the tables and made the relationship).I have tried the above solution and it does enable all the child record fields but when I enter the values and click save it says the "SpecialProductId" is read only and does not allow me to save the new record.I have tried to find a solution by googling and found some related issuessocial.msdn.microsoft.com/.../screen-for-one-to-one-or-zero-relationship-in-visual-studio-lightswitchstackoverflow.com/.../creating-primary-keys-in-zero-or-one-to-one-relationship-in-lightswitch both of them are focusing on desktop client. An in the Later one Yann Duran has mentioned that there is some reference which needs to be set and setting the value for id will not work as it is read only.I tried "Copy information to a new screen" from msdn.microsoft.com/.../jj733572.aspx  but again I guess like Yann Duran said in stack overflow the id is in my case read only so I could not do it.I am totally confused on how to set the reference or do something so that I can enter the values into both the tables and save the record.Please help!!!

• Anonymous
  February 13, 2014
  I am also having problem like the others, I've tried this by creating a database within LS project & it works but it doesn't work if I create the database in SQL. Here is my unanswered thread from MSDN forum: social.msdn.microsoft.com/.../screen-for-one-to-one-relationship-in-html-client

• Anonymous
  April 08, 2014
  Beth, I am unable to make either of the solutions for the HTML Client work properly. Both creating the child record with code, and allowing the user to create it with a button, cause an error "Cannot change a readonly property 'Id'." Can you assist?

• Anonymous
  April 08, 2014
  Hi BethIs there anyway you can expand on different data sources.  I have followed your leads and divyang_dv  workaround and I am not getting anywhere.In my example I am using a SharePoint Task list (to schedule maintenance visits), and then I want to attach the service record (a table I created as application data) to the scheduled visit in a one-to-one capacity.  RegardsSally

• Anonymous
  April 23, 2014
  @Chris Nixon and @skolle:If you are using virtual relationships here, then there's a slight snag with this, which is that with one to zero-or-one relationships, you need to assign the relationship from the side that has the foreign key. The HTML runtime should be able to determine where the foreign key is just like the desktop client, but currently it does not.This means that Beth's code in this article to create a new CustomerNote and assign it to Customer's CustomerNote property doesn't quite work, as the runtime ends up trying to change the primary key of the Customer to match the primary key of the new CustomerNote (which is going to be undefined initially).Instead, try code like this:myapp.Customer.created = function (entity) {   var note = new myapp.CustomerNote();   note.Customer = entity;};Or:myapp.AddEditCustomer.AddNote_execute = function (screen) {   //Add a new note   var note = new myapp.CustomerNote();   note.Customer = screen.Customer;};I recently posted an article that gets into saving to multiple data sources that uses this approach, so you might find it useful as another example:blogs.msdn.com/.../saving-to-multiple-data-sources-in-the-html-client-stephen-provine.aspxHope that helps,Stephen

• Anonymous
  July 14, 2015
  HI, I am having real issues adding or viewing any or my records if I add a datetime field. I keep getting errors on 'cultures' not being defined. I am also unable to save my add if I have a string which is free from on my add screen. Any help would be most appreciated, this is driving me mad. Cheers, Sofia