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
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:
- the first Create Record action will add a new Invoice master record comprising some copied Order’s data;
- the second action should copy related Order Items into Invoice Items table;
- 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:
The first action is obvious and simple. It creates a new Invoice record in the "Invoices" table:
The second action is complicated enough. As mentioned above, it will copy data from related Order Items into generated Invoice Item records.
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.
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
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
The RecordSet column settings are displayed in the screenshot below:
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.
Let’s return to the "Copy Items" action, namely to the Assignments listed in this action.
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:
The adjustment of the "Create Invoice" button is done.