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