Dataverse Table Self-Joins

Recently I’ve posted quite a bit on how to navigate around Dataverse to identify related rows in different tables. With self-joins, rows can be related to other rows in the same table

Consider the example of an organisation that has programmes of work made up of multiple projects and each project can contain multiple workstreams. If the data held on a programmes (for example, start date, budget, owner, etc) is the same data you would hold on both a project and a workstream, then self-joins provide an option to hold all programme, project and workstream records in the same table, rather than separate tables

Creating a Self-Join in a Dataverse Table

To enable one record to be related to another, create a Lookup column that references its own table. It’s as straightforward as that

Below is the custom ‘Hierarchical Project’ table with a Lookup called ‘Parent Project’ with itself. The column is optional to recognise that the programmes, being at the top of the tree, won’t have a parent themselves

If we look at the relationships in the table you can see that the table has a many-to-one relationship with itself

As mentioned, the example structure I’ll be using consists of 3 tiers:

Programmes
Projects
Workstreams

A Programme is defined by the fact that it does not have a parent

If the parent of a record is a Programme, then that record is a Project

Everything else is a workpackage

Because of this logic, it’s not necessary to have a column to specify each individual record to be a programme, project or workstream

The Canvas App

The data in the table is populated using the Canvas App below

Data Entry Section

The Data Entry section is used to add new projects or worksteams. It consists of 2 cascading dropdowns and a text input box. The Programme dropdown and the ‘New Project or Workstream’ text input box are both mandatory which is denoted by the asterix and red border

Programmes are the top tier of the structure and as mentioned, are defined by the fact they do not have a parent. The Items property to display the list of programmes is below

Sort(
   Filter(
      'Hierarchical Projects',
      IsBlank('Parent Project')
   ),
   Name
)

The second mandatory field is the name of the project or workstream being created. This is a text input box

The optional dropdown box for the Project field lists all the existing projects that belong to the selected programme. If the entry is left empty, then the parent of the new record is a programme, so the new record is classed as a project. If a selection is made is the Project dropdown, then the new record’s parent is a project, so the new record will be a workstream. The Items property to display the existing projects for the selected programme is below

Sort(
   Filter(
      'Hierarchical Projects',
      'Parent Project'.Name = DropdownProgramme.Selected.Name
   ),
   Name
)

The ‘Submit’ button becomes active only when the 2 mandatory fields have entries. Clicking the ‘Submit’ button patches the data to the ‘Hierarchical Project’ table to create a new record via the button’s OnSelect property

Patch(
   'Hierarchical Projects',
   Defaults('Hierarchical Projects'),
   {
   Name: TextInputName.Text,
   'Parent Project': If(
      IsBlank(DropdownProject.Selected),
      DropdownProgramme.Selected,
      DropdownProject.Selected
      )
   }
);
Reset(DropdownProgramme);
Reset(DropdownProject);
Reset(TextInputName)

Gallery Section

When an entry is made in the Programme field. All the Projects for that Programme are displayed in the gallery. If a Project is selected, the gallery contents change to list of all the Workstreams for that particular Project instead. The Items property for the gallery is below

If(
   IsBlank(Dropdown1_1.Selected),
   Sort(
      Filter(
      'Hierarchical Projects',
      'Parent Project'.Name = DropdownProgramme.Selected.Name
      ),
      Name
   ),
   Sort(
      Filter(
      'Hierarchical Projects',
      'Parent Project'.Name = DropdowProject.Selected.Name
      ),
      Name
   )
)

Below the Canvas App is being used to add records

The title of the gallery is a HTMLText control. The content is only visible once a programme has been selected and changes depending whether it is displaying projects or workstreams

"<b>Existing " & "<font color=blue>" &
If(
   IsBlank(DropdownProject.Selected),
   "Projects",
   "Workpackages"
)
& "</font>" & " for the " & "<b><font color=blue>" & "'" &
If(
   IsBlank(DropdownProject.Selected),
   DropdownProgramme.Selected.Name & "' Programme",
   DropdownProject.Selected.Name & "' Project"
)
& "</b></font>"

Summary

Self-joins are a remarkably simple way to create relationships between records within the same table

As you have seen, the records don’t have an explicit column that states whether they are a programme, project or workstream. Instead, their identity is defined by the simple logic below

Programme: A row without a parent

Project: A row with a parent that is a Programme

Workstream: Everything else