The most typical case when you need to copy master record into a separate table and copy its related details is the Invoice record generation based on the previously created Order record.
For example, you keep customer orders in the Orders table. Moreover, each Order includes Order Items related to it. So, you have the Order master record that is related to many Order Items (detail records). In the terms of online database there is the One-to-Many relation between the Orders table and the Order Items table.
When the order is agreed, a user needs to generate the Invoice record related to this Order. Let’s assume that the relation is One Order-One Invoice.
Furthermore, all Order Items related to the Order should be copied into the separate Invoice Items table. Moreover, these copies should be related to the newly created Invoice automatically.
So, in this blog post we describe how to add a custom button to the Order record allowing to generate a master record copy (Invoice) in a separate table with copies of related details (Invoice Items).
1. The Invoice ID values can be generated in the Orders table
To simplify the adjustment we recommend to create the “Invoice ID Text (auxiliary)” column of the text type in the Orders table. The task for this column is to generate the Invoice ID values automatically. Therefore there is the Default/Calculate formula in the column settings.
As a result, the Invoice ID value can be generated automatically. Besides, it is based on the Order ID value. By the way, you can use any other formula that meets your needs.
2. Invoice ID lookup
Now you should make some adjustment in the Order Items table. As you remember, there is One-to-Many relation between Order and Order Items. The first step is to add a lookup column to the Order Items table. This lookup column should refer to the “Invoice ID Text (auxiliary)” column kept in the Orders table. Let’s name it “Invoice ID Lookup (auxiliary)”.
In the following adjustment this lookup column will help to relate newly generated details to the Invoice master record.
3. Record Id formula-column
The second step is to define the Order Item record Id value assigned by the system and keep this Id in a separate column. Therefore you need to create the “Order Item Record Id (auxiliary)” formula-text column comprising this formula: RecordId()
You can remove this formula-text column from the data entry form and don’t display in views.
4. Auxiliary Summary column in the Orders table.
According to our example One Order can be related to many Order Items. So, the next step is to list the system ID values of details records (Order Items) in the “Order” master record. Hence, you should add the summary column of the concatenate type to the relation. Let’s name this summary column “Related OrderItems RecordID (auxiliary)”. You may review the column settings on the screenshot below:
As the result, you can see the system record ID values of the related Items in each Order record.
5. Auxiliary Lookup Column in the Invoices table
In our database example each invoice should be related to one order only and vice versa. To adjust this relation, create the “Order” single-reference column in the Invoices table. Using this relation, you can add lookup columns to the Invoices table. Currently, to generate copies of related details, you need an auxiliary lookup column in the Invoices table. This lookup should refer to the previously described “Related OrderItems RecordID (auxiliary)” summary column.
On this screenshot the lookup column settings are shown:
When the lookup is adjusted, you can save the changes.
6. Multi-Record custom button.
Let’s return to the “Order Items” table again. Now you need to adjust the “Copy Details (auxiliary)” multi-record custom button with the “New Record Create” action. This button will generate the copies of related details in the “Invoice Items” table. The button settings are displayed below:
Also, the “New Record Create” action has to comprise the following assignments:
Moreover, one of the button assignments should assign “Invoice ID Lookup (auxiliary)” column values to the Invoice single-reference column created in the “Invoice Items” table. This assignment will help to related newly generated Invoice Items with Invoice master record.
7. “Create Invoice” button
Now you can add the “Create Invoice” custom button to the Orders table. This button can be placed on the Oder form. When a user clicks on the button, an Invoice master record is generated in the Invoices table. Besides, the data kept in the initial Order record can be copied to the newly created Invoice. Therefore the “Create Invoice” button has to comprise the “Create Invoice Navigate Action” of the Navigate type with the assignments.
You may review the button settings on this screenshot:
Also, you can see the navigate action settings below:
Please note, that one of the assignments should assign the “Invoice ID Text (auxiliary)” column values into the “Invoice ID” key column. As a result, these copied values will allow to relate Invoice master record to the generated Invoice Item details.
Moreover, you can add the assignment ([Order ID] to [Order]) that will assign “Order ID” key column values to the “Order” single-reference column kept in the Invoices table.
8. Record Change Trigger with Navigate Action
The last step is to generate Invoice Items related details when an Invoice master record is created. Furthermore, every Invoice Item should include values of correspondent Order Item record.
To automate the Invoice Items details generation, in the Invoices table you should create the Record Change Trigger with Navigate action. The main task of this trigger is to activate the “Copy Details (auxiliary)” multi-record custom button kept in the Order Items table.
So, firstly, add the record change trigger to the Invoices table.
9. Custom URL used in the Action
In the trigger you should adjust the navigate action of the Custom URL type, for example, this action:
So, you can see the following URL used in the action: <%URLRoot()%>/multi.aspx?b=233706&p=&ids=<%=[Related OrderItems Record ID Lookup (auxiliary)]%>&ReturnUrl=<%URLRoot() & “/preview.aspx?t=” & TableId()& “&id=” & RecordId() & “&back=” & BackURL()%>
You can copy this URL, but you have to change these two elements:
| Element | Description |
|---|---|
| 233706 ID number of the Multi-Record button |
The ID number of the “Copy Details (auxiliary)” multi-record button created in the “Order Items” 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. |
| [Related OrderItems Record ID Lookup (auxiliary)] | The creation of this lookup is described in paragraph “5. Auxiliary Lookup column in Invoices table” of this article. Please note, that the name of the lookup created in your online database can be different. |
To find and copy the ID number of the “Copy Details (auxiliary)” multi-record button, you should do these steps:
Firstly, go to the Setup and select the “Order Items” 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 the action URL is adjusted and the “New Navigate” action is saved, the trigger can activate the “Copy Details (auxiliary)” button for the related details copying.
The adjustment is finished. Now a user can click on the “Create Invoice” button displayed on the Order form and the system will create a new Invoice with generated copies of related details.
In conclusion, you can remove all auxiliary columns from the data entry forms and don’t display them in views. As a result, these columns will be invisible for users.
