RecordSet Column and ParentKey() Function

There can be cases when you need to copy a master record together with related details. You may use RecordSet columns and the ParentKey() function in Create and Update workflow actions to make the copying of detail records process straightforward. There is an example in the database library displaying such an adjustment.

Suppose you have the "Orders" table with an "Order Items" detail table and want to use a button to create an Invoice with Items based on the Order’s data.

The idea looks simple - adjust the "Create Invoice" custom button in the "Orders" table. This button should include the following actions:

  1. the first Create Record action will add a new Invoice master record comprising some copied Order’s data;
  2. the second action should copy related Order Items into Invoice Items table;
  3. the third Navigate action will redirect a user to a newly created Invoice record form.

The custom button settings with three actions are shown below:

Create Invoice button.png

The first action is obvious and simple. It creates a new Invoice record in the "Invoices" table:

Create Invoice action.png

The second action is complicated enough. As mentioned above, it will copy data from related Order Items into generated Invoice Item records.

Copy Items action.png

You may see the Get Values From field in the action settings. This extra field is displayed because there is a RecordSet column that exists in the "Orders" table. The Get Values From option allows you to select the source of the data - either the current record in the master table or any RecordSet column in that table. By selecting the RecordSet column, you instruct the system to repeat an action for every detail record in a set.

In our example, the "Order Items (RecordSet column)" is selected in the Get Values From field of the action settings.

RecordSet Column

All steps of the RecordSet column creation are described below.

While a summary column calculates an aggregated value over a set of detail records, a RecordSet column acts as a reference to such a set, keeping internal record IDs (don’t confuse these IDs with key column values). This column is bound to master-detail relation and has an optional filter. You can find the New RecordSet button in the Selections section.

To create a new RecordSet column, click on the New RecordSet button, select the proper relation, and click the Add RecordSet button. As a result, the column will be listed in the Selections section.

RecordSet.png

The RecordSet column settings are displayed in the screenshot below:

RecordSet column settings

There is nothing special about RecordSet column settings. We need all of the Order Details, so the filter is empty.

If you are using a custom formula, the Related column prefix enables you to reference columns on the other side of the relationship in filter conditions for summary columns and relation-specific record pickers. Refer to this blog article for usage examples.

The limit of records that the system can process at once with a RecordSet column depends on the number of users invited into the database. More details are provided in the Record Processing Limits item.

ParentKey() Function

Let’s return to the "Copy Items" action, namely to the Assignments listed in this action.

ParentKey

There is the ParentKey() pseudo-function in the first assignment. This function picks up a key of the newly created Invoice record. It is possible because the system tracks record keys touched by preceding workflow actions, one key per table. That’s why we can assign a key of a newly created Invoice in this action.

The other assignments listed in the action are obvious.

The last (third) Navigate action redirects a user to a newly created Invoice record form. The action settings are displayed below:

Navigate action

The adjustment of the "Create Invoice" button is done.