How to work with Connections in Dataverse

What are Connections?

Dataverse has rich relational capabilities and provides many different relationships like 1:N, N:N, etc. Sometimes, you just want to link two records instead of creating a formal relationship between two tables. This is where Connections come into play! They provide a flexible way to connect any two records (even of the same table) and describe the relationships between them. For every connection, you create two records in the Connections table. One record to specify the source to target connection and the other one to represent a target to source connection. To learn more, click here.

What are Connection Roles?

Connection Roles describe the purpose or description of the relationship between two records. Each role is linked to a category. To better manage roles, club similar roles together by assigning them to the same category. Connection Roles can be used in 3 different ways in a connection:

1. Same role for the source and target record - e.g. "friend" can be applied to both records.
2. A role assigned to only one of the two records - e.g. "salesperson" role applied to a contact in relation to a sales opportunity
3. Opposite roles assigned to the source and target records - e.g. "parent" and "child".

To learn more, click here.

How to enable?

Connections allow users to connect any two records from any two tables. However, a table needs to be explicitly enabled for connections. To do that, follow these steps:

1. Go to make.powerapps.com
2. In the left navigation, select Tables under Dataverse
3. Click on any table and then click on Settings in the top ribbon action bar
4. Scroll down on the Settings dialog/pop-up (that opens up on the right half of the screen)
5. Under "Rows in this table", check the "Can have connections" checkbox

How to display?

We will use the Students and Instructors tables to view existing connections. To review the data model, click here. Add two galleries - galStudents and galStudentConnections. Set the Items property of galStudents to the Students table. Now to show the list of connections with a student, set the Items property of galStudentConnections to:

Filter(
    Connections,
    'Connected From' = galStudents.Selected 
)

Add two labels in galStudentConnections - lblInstructorName and lblRole. Set the text property of lblRole to:

ThisItem.'Role (To)'.Name

And set the text property of lblInstructorName to:

AsType(
    ThisItem.'Connected To',
    [@Instructors]
).Name

Now, the reason why we need to use AsType is because the records where Connected From is a Student record could have a record from any table as the Connected To. You could technically use IsType in the Text property of lblInstructorName but you cannot possibly account for all the tables. To learn more about the AsType and IsType functions, click here.

Unfortunately, you cannot limit Connection Roles to a specific table(s). Consider a role called Mentor. There is no way to enforce that usage of this role only when Student records are being connected to an Instructor record. So if there is another table linked to a particular student with the Mentor connection role, then in galStudentConnections, you will get a casting error since you will be trying to cast a record of another table to the Instructors table. To reduce the casting checks(using IsType), restrict certain connection roles to specific tables. Since you cannot explicitly link roles to tables, dd all roles pertaining to a table into one category. Then, simply filter the list of roles to that category in your app. The usage of categories will get clearer as we look at how to create new ones in the next section.

How to create?

We will use the same Students and Instructors table to create connections. Add two combo boxes cmbStudents and cmbInstructors and set their Items property to Students and Instructors. Now add another combo box cmbConnectionRoles to display the list of Connection Roles. Create a category first to make sure only those roles that pertain to Students and Instructors are available. Now, Category is an option set. What that means is that if you need to create a new category, you cannot do that from within the app. You have to first go to the maker portal and add a new option to the Category option set.

We will use the Instructor-Student category to filter Connection Roles that will be used to connect a student with an instructor. Based on this, set the Items property of cmbConnectionRoles to:

Filter(
    'Connection Roles',
    'Connection Role Category' = Category.'Instructor-Student'
)

Now that we have the three combo boxes populated, to create a connection between a student and an instructor, add a button and set its OnSelect to:

Patch(
    Connections,
    Defaults(Connections),
    {
        'Connected From': cmbStudents.Selected,
        'Connected To': cmbInstructors.Selected,
        'Role (To)': cmbStudentInstructorRoles.Selected
    }
);

Recent articles

1. Yes/No columns in Dataverse
2. Choice columns in Dataverse
3. Polymorphic lookups in Dataverse