It’s a trivial situation when you need to copy a master record, including details and sub-details. For example, your database stores records of specific products, each product consists of many parts and every part includes many components. In this case you are dealing with 3 level cascade relation.
This blog post describes the settings allowing to copy a master record together with details’ and sub-details’ records. The configuration includes the following steps:
1. “Copy” custom button.
First, you need to create a “Copy” custom button in the Master table.
After that you should add the first “Copy Master” workflow action to this button.
2. RecordSet columns.
On the next step you create a “Details RecordSet” column within the Master-Details relation. Also, you need to add a “Subdetails RecordSet” column. To create it, you should use an additional Many-to-Many relation between the “Master” table and the “Sub-details” table with the following match condition: Id=Master Key.
As the result, two columns of RecordSet type are created in the “Master” table.
3. Auxiliary Text column.
In this part of setup, we will utilize auxiliary columns. One of such auxiliary columns is the “Original Detail” text column created in the “Details” table.
The “Original Detail” column stores the key of the original detail while details are copied. Furthermore, while ‘subdetails’ are copied, this value is used to pull a key of a parent detail copy. These settings’ details are described below.
4. Auxiliary Summary Index column.
As we’ve mentioned, we also need some auxiliary columns as part of the configuration. The second auxiliary column is the “New Detail Key” column of the summary type with the Index function. This column is added to the Many-to-Many relation between the “Sub-details” table and the “Details” table. Please note that the relation has a match condition:
The summary column settings are displayed on the following screenshot:
This summary column allows to pull an Id value of the last generated Detail and display this value in the original Sub-detail records. After that the system creates new copies of related sub-details and inserts the last generated Detail Id value into the “Detail” reference column kept in the “Sub-details” table. It can be done with the help of the “Copy Subdetails” workflow action with assignments described in the paragraph #6.
5. “Copy Details” workflow action.
Now we may proceed with the “Copy” custom button described in the paragraph #1. To copy Detail records an additional workflow action should be created and added to the “Copy” button.
The settings of the “Copy Details” action are shown on this screenshot:
In the first assignment you may see the ParentKey() function, which locates a key of the last created Master record. Also, don’t forget to add an assignment where the “Original Detail” text column is filled in.
6. “Copy Subdetails” workflow action.
The last workflow action that you need to add to the “Copy” custom button is the “Copy Subdetails” action.
Please note that this action includes an assignment, where the “New Detail Key” column value is assigned to the “Detail” reference column.
As the result, we created a “Copy” custom button containing three actions.
Please explore Copy Record with Details and Sub-Details pre-configured sample database to review the described mechanism in action.
