If your online database includes related tables, you may need to copy a Master record and copy master Details listed in the related details views . To simplify this process, the custom buttons with the Navigate actions can be adjusted.
Let’s consider the Project Management database, where each project record is related to many Stages and to many Project Personal records keeping employees involved into this project.
So, each Project record comprises two details views: Stages and Project Personnel.
What you need is to copy the Project master record and copy master details related to the project.
The adjustment requires the following steps:
1. Auxiliary reference column
First of all, create a new single-reference column in the Projects table. This column should refer to the Projects table. In simple words the table refers to itself.
2. “Copy Project Record” button
In terms of relation the Projects table is a Master table. Therefore the first custom button should create a copy of the Master record. In our example this is the “Copy Project Record” button.
Besides, it should be the Preview Page button with the Navigate action.
The “Navigate (Create Project Copy)” action navigates a New record creation in the Projects table.
The navigate action settings are displayed below:
Moreover, this action comprises assignments. These assignments copy the project column values to a new project record. In our example the “Client” column values and the “Name” column value of a project are copied. Furthermore, “Project Id” key column value is copied into the “Project Copy (auxiliary)” column. This auxiliary reference column was created in the beginning. Please, note, that this last assignment is very important.
3. Auxiliary Formulas
Before the adjustment of the other buttons you need to add some auxiliary formulas in the details tables. In terms of relation the Stages table and the Project Personnel table are Details tables. Hence they keep details records related to a Project.
On this step you should create a Formula-Text column with the same formula in each Details table. This formula will pull the record Id value assigned by the system.
So, in the Stages table you should add the “Stage Record Id (auxiliary)” formula-text column:
Also, in the Project Personnel table you should add the “Project Personnel Record Id (auxiliary)” formula-text column:
As a result, you will use each formula-column in the appropriate summary column described below.
4. Auxiliary Summary columns with Concatenate function
The next step is the Summary columns adding. These columns work like auxiliary columns only. So, we need to create two summary columns in the Projects table, because we have to copy master details kept in two details views (Stages and Project Personnel).
You can create the first Summary column in the Stages-Project relation, the second in the Project Personnel – Project relation.
The settings of the “Related Stages Record Id Concatenate (auxiliary)” summary column are displayed on this screenshot:
You can see the settings of the “Related Personnel Record Id Concatenate (auxiliary)” summary column on this screenshot:
As a result, these summary columns will help to create the auxiliary Lookup columns.
5. Auxiliary Lookup columns
Now you need to create lookup columns, that will help to copy master details. There are two details views in the database, therefore you created two auxiliary summary columns. Now the goal is to pull the summary columns values via lookup columns of the auxiliary Project-Project relation. This relation was created automatically when you added the “Project Copy (auxiliary)” single-reference column mentioned in the very beginning.
So, you need two auxiliary lookup columns. Each lookup can pull values from the “Related Stages Record Id Concatenate (auxiliary)” summary column and the “Related Personnel Record Id Concatenate (auxiliary)” summary column respectively.
You can review the settings of the “Related Stages Ids lookup (auxiliary)” column below:
The settings of the “Related Project Personnel Ids lookup (auxiliary)” column are displayed on this screenshot:
6. Auxiliary Text Columns in the Details tables
Furthermore, you have to add a new Text column, let’s name it “Master Copy Id (auxiliary)” column, to the Stages table. After that create the same text column in the Project Personnel table.
When you generate a Stage copy, the initial Stage record has to keep the Project Id value of the last Project copy. This value is assigned to the “Master Copy Id (auxiliary)” column of the Stages table.
If you generate a Project Personnel copy, the initial Project Personnel record has to keep the Project Id value of the last Project copy. This value will be assigned to the “Master Copy Id (auxiliary)” column of the Project Personnel table.
7. Auxiliary Multi-Record buttons in Details tables
To copy the Stages records you should start from the “Copy Stages (auxiliary)” multi-record button. Besides that you have to create this button with the URLParam(URL(),”p”) assignment and the New Record Create action in the Stages table.
The New Record Create action includes the following assignments:
Please, remember that the Project column is a single reference column referring to the Project table.
When you created the auxiliary multi-record button in the Stages table, you can add the similar multi-record button to the Project Personnel table.
Moreover, in the button the New Record Create action includes the following assignments:
Finally, all auxiliary elements are added. Now you should make some tweaks in the Projects table.
8. Custom buttons allowing to copy Details.
When a user clicks on the “Copy Project Record” button, the project copy is created. The initial Project value is specified in the “Project Copy (auxiliary)” reference column. Moreover, you can make this auxiliary column hidden on the form or in the default view.
When a user added the Project copy record, two buttons for Stages details and for Project Personnel details copy creation become accessible on the Project form.
The following steps demonstrate how to create these buttons.
9. “Copy Stages Details View” custom button with Navigate action
To copy the Stages details records, you need the “Copy Stages Details View” custom button on the project view form. Therefore you should create this button in the Projects table. Moreover, it can comprise the filters. Because of the filters the button will be displayed in the project copy, only if the initial project includes the related details.
You can review the button settings on the following screenshot:
The Navigate action settings:
The URL used in the action is the following:
<%URLRoot()%>/multi.aspx?b=210529&p=<%=[Project Id]%>&ids=<%=[Related Stages Ids lookup (auxiliary)]%>&ReturnUrl=<%URLRoot() & “/preview.aspx?t=” & TableId()& “&id=” & RecordId() & “&back=” & BackURL()%>
You can copy this URL, but you have to change these three elements:
| Element | Description |
|---|---|
| 210529 ID number of the Multi-Record button |
ID number of the “Copy Stages (auxiliary)” multi-record button created in the Stages details table. This ID number is assigned by the system and can be copied from the button URL . Please note, that in your online database the button ID number will be different. |
| [Project Id] | The [Project Id] is a key column of the Project table (Master table). In your online database you should insert into the URL-formula the key column name of the master table you need. |
| [Related Stages Ids lookup (auxiliary)] | The creation of this lookup is described in paragraph “5. Auxiliary Lookup columns” of this article. Please, note that the name of the lookup created in your online database can be different. |
How to find and copy the ID number of the “Copy Stages (auxiliary)” multi-record button? Just do these steps:
Firstly, go to the Setup and select the Stages table. Secondly, choose Rules>>Custom Buttons. Finally, click on the button name:
As a result, you can see the opened button form on the screen and the button URL in the address bar of a browser. You should copy the custom button ID number from the button URL:
When you added and saved the custom URL to the Navigate action, the “Copy Stages Details View” button is adjusted.
Now if a user clicks on the button, the Stages details copies will be generated under the project record form.
10. “Copy Project Personnel Details View” button with Navigate action
Also, you should make the similar adjustment to create the “Copy Project Personnel Details View” custom button.
Firstly, create the button with filters and the Navigate action in the Projects table:
Secondly, adjust the Navigate action:
Finally, build the URL that should be inserted into the action:
<%URLRoot()%>/multi.aspx?b=210532&p=<%=[Project Id]%>&ids=<%=[Related Project Personnel Ids lookup (auxiliary)]%>&ReturnUrl=<%URLRoot() & “/preview.aspx?t=” & TableId()& “&id=” & RecordId() & “&back=” & BackURL()%>
You can copy this URL, but you have to change these three elements:
| Element | Description |
|---|---|
| 210532 ID number of the Multi-Record button |
ID number of the “Copy Project Personnel (auxiliary)” multi-record button created in the Project Personnel details table. This ID number is assigned by the system and can be copied from the button URL . Please note, that in your online database the button ID number will be different. |
| [Project Id] | The [Project Id] is a key column of the Project table (Master table). In your online database you should insert into the URL-formula the key column name of the master table you need. |
| [Related Project Personnel Ids lookup (auxiliary)] | The creation of this lookup is described in paragraph “5. Auxiliary Lookup columns” of this article. Please, note that the name of the lookup created in your database can be different. |
How to find and copy the ID number of the “Copy Project Personnel (auxiliary)” multi-record button? It’s simple, just do this steps:
First of all, go to the Setup and select the Project Personnel table. Then choose Rules>>Custom Buttons. After that click on the button name:
As a result, you can see the opened button form on the screen and the button URL in the address bar of a browser. You should copy the custom button ID number from the button URL:
Now if a user clicks on the button, the Project Personnel details copies will be generated under the project record form.
Please note, that you can remove all auxiliary columns and auxiliary buttons from the data entry form and can exclude them from the Default View or any other views.
When the above described adjustment is done, in your online database you can generate Project copies with two related details views.
