If a table contains nested records, it means that there is a multi-level tree of related records.
In this blogpost you may read how the multi-level tree of related records can be configured.
Let’s assume that within your database you need to register and calculate energy consumption of engines containing modules and submodules. The structure would look like a multi-level tree including the following levels: Engine(Parent)->Modules(Children)->SubModules(Grandchildren). The number of levels may be unlimited. The goal is to calculate the total energy consumption from the lowest level to the highest. Besides, if the highest-level record is deleted, all the related records will be deleted automatically.
You may order a free trial of the pre-built “Example: Multi-Level Tree” database to review the setup described below.
This example contains a single table “Engines”. You may see this table structure on the screenshot:
The multi-level tree has three levels: Engine->Modules->SubModules.
Building such a tree takes the following steps:
1. First, you need a single-reference column in the “Engines” table, while the table should refer to itself. One record is related to another the way a child is related to a parent. Each parent can be related to many children, while each child can be related to one parent. Let’s name this reference column “Parent”. In our example one Engine can be related to many Modules, while each Module can be related to many SubModules.
The “Parent” single-reference column settings are shown on the screenshot below:
2. You should also create the “Old Parent” single-reference column. This column participates in workflow actions making records recounting. It is needed as an auxiliary column, therefore the “Old Parent” reference column should be hidden on the form and removed from views.
3. In our example every engine or module or submodule has an energy consumption value, which is kept in the “Energy Consumption” column. You would also need to calculate the energy consumption of related lower levels (Modules and SubModultes) and store this value within the Parent record, in the “Consumption of Lower Levels” numeric column. Also, the “Need Recalculate” checkbox column should be created as it will be used for the assignments added to the workflow actions. You may see these three columns in the column list of the “Engines” table:
4. Moreover, in the relation built by the “Parent” reference column you would need to add two auxiliary columns that will be used in workflow actions. Firstly, lets create the “Count Consumption of Lower Levels” summary column. It summarizes Energy Consumption values stored in related lower level records. Secondly, you should add a RecordSet column, let’s name it “Lower Levels Recordset”. This column displays internal record IDs of related lower level records.
5. The last column that is obligatory to be created is the “Total Energy Consumption” formula-numeric column.
6. Now you can configure workflow triggers of the Record Change type.
The main advantage is that these triggers work as a cascade due to the “Execute Triggers” option activated in the workflow actions stored in these triggers. Therefore, if an Energy Consumption value is added or changed in a record of a lower level (for example in SubModule), the system recalculates the total energy consumption in higher levels, such as “Module” and “Engine” records in our example.
7. The first trigger is “Add/Edit Trigger”. It includes three actions of the Update Record type:
If a Parent value is changed in a record, the “Recalculate Parent” action initiates the request to recalculate the Energy Consumption for the Parent record.
Therefore, the “true” value is set in the “Need Recalculate” checkbox column.
The second action “Recalculate Old Parent” initiates the request to recalculate Energy Consumption for the Old Parent record.
Please note that the “Execute Triggers” option is active in both of the above-mentioned actions.
The third action is the “Update Old Parent” action. It is needed to update the Old Parent value by the Parent value after all recalculations had been made.
8. The “Recalculate Trigger” includes both the “Recalculate Children” action and the existing “Recalculate Parent” action described above.
The first “Recalculate Children” action assigns the “Count Consumption of Lower Levels” summary column value to the “Consumption of Lower Levels” numeric column. Also, it sets “Need Recalculate” checkbox value to “false”. This last assignment is needed to stop the related details recalculation if such a recalculation is finished.
The settings of the “Recalculate Children” action are displayed on the following screenshot:
The second action is the “Recalculate Parent” action which is also used in the “Add/Edit Trigger”.
This action is needed to initiate the recalculation of records on higher levels.
9. “Delete Trigger” allows to delete all Children records when a Parent record is deleted. There are three actions in this trigger:
The first action is the “Delete Children ” action. It allows to delete related details listed in the “Lower Levels Recordset” column when a Parent record is deleted.
“Execute Triggers” option is active in this action.
The second “Clear Parent” action is needed to clear a Parent value within deleted records. It allows to exclude deleted record from recalculation of the energy consumption value within the Parent record.
Deleted record may be restored from the Recycle Bin, however, a restored record will not be related to any Parent automatically since its Parent field is empty.
The third “Recalculate old Parent” action is an existing action that also participates in the “Add/Edit Trigger” described in the item # 7. Due to this action the deletion is repeated from the lowest level to the highest.
10. If you would like to review the described setup, please try the “Example: Multi-Level Tree” template from the database library.
