How to work with one to one Dataverse relationships

Introduction

The example we will be using to look at one to one (1:1) relationship is between students and their personal details. A student can have only one set of personal details. And one set of personal details can belong to only one student. To review the data model, click here. 1. Use 1:1 when you need to have higher security on certain fields. SSN is a classic example. You can have SSN as a column on the Students table itself and then use field level security, but selectively sharing some fields can sometimes be a pain. By putting them on different table, you can selectively give access to those details to different users. A 1:1 relationship can be set up simply having a lookup column on TableA to TableB and having a lookup column on TableB to TableA.

Displaying related records

Let's suppose there is a gallery - Gallery1. The data source of Gallery1 is the Students Dataverse table. Next, you want to show the SSN of each student in Gallery1. To achieve that, use the dot operator :

"SSN: " & " " & ThisItem.'Personal Detail'.SSN

where Personal Detail is the name of the lookup column on the Students table

Creating one to one relationship

Let's say we are creating new student records. To create a student record along with that student's personal details, we will need two forms, frmStudents (FormMode set to New) for the students table and frmPersonalDetails (FormMode set to New) for the personal details table. The forms can be arranged in such a way that they look like one form for the end users.

Next, add a button and set its OnSelect to:

SubmitForm(frmStudents);
UpdateContext({locNewStudent: frmStudents.LastSubmit});
SubmitForm(frmPersonalDetails);
UpdateContext({locNewPersonalDetail: frmPersonalDetails.LastSubmit});
Patch(
    Students,
    locNewStudent,
    {'Personal Detail': locNewPersonalDetail}
);
Patch(
    'Personal Details',
    locNewPersonalDetail,
    {Student: locNewStudent}
);
ResetForm(frmStudents);
ResetForm(frmPersonalDetails)

Let's break this down:

1. First, submit frmStudents to create a new student record
2. Use frmStudents.LastSubmit to capture the newly created student record
3. Next, submit frmPersonalDetails to create a new personal detail record
4. Use frmPersonalDetails.LastSubmit to capture the newly created personal detail record
5. Then, Patch the newly created student record with the newly created personal detail record
6. Last, Patch the newly created personal detail record with the newly created student record

To learn more about forms, click here.

Deleting one to one relationship

Add a gallery galStudents and set its Items property to Students. Add two labels to the gallery and set their Text property to:

ThisItem.Name

and

ThisItem.'Personal Detail'.SSN

Add a button and set its OnSelect to:

Patch(
    'Personal Details',
    galStudents.Selected.'Personal Detail',
    {Student: Blank()}
);
Patch(
    Students,
    galStudents.Selected,
    {'Personal Detail': Blank()}
);

As you might notice, clicking on the button doesn't really do anything. The SSN details for the selected student are still visible.

Blanking out lookup columns

Almost 18 months ago, I had shared a blog post that outlines this very topic - how to blank out lookup columns. The gist of that is you need to turn on the experimental setting "Formula-level error management". You can read the entire post here. With this setting turned on, if you now click on the button, you will see the SSN details get wiped off for the selected student in the gallery.

Recent articles

1. Many to one Dataverse relationships
2. OOB many to many Dataverse relationships
3. Custom many to many