Copy master & details

Copy Master record into a separate table and generate copies of related Details

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).

Master record copy

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.

Invoice ID

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)”.

Invoice ID lookup

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()

Record Formula

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:

Record ID Details Summary

As the result, you can see the system record ID values of the related Items in each Order record.

Related Items ids

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:

Lookup To Sumary

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:

Copy Details Auxiliary button

Also, the “New Record Create” action has to comprise the following assignments:

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:

Create Invoice Button

Also, you can see the navigate action settings below:

Navigate Action

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.

Record Change Trigger

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:

Navigate Action 2

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:

Button

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:

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.

Copy Master Record

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.

Leave a Reply

Your email address will not be published. Required fields are marked *