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 ParentKey()
function in Create
and Update
workflow actions to make the copying detail records process straightforward. There is the example in the database library displaying such adjustment.
Suppose you have the "Orders" table with "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 simply - 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 on 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 complicate enough. As it is mentioned above it will copy data from related Order Items into generated Invoice Item records.
You may see Get Values From
field in the action settings. This extra field is displayed, because there is the RecordSet
column exists in the "Orders" table. The Get Values From
option allows you to select the source of the data - either current record in master table or any RecordSet
column in that table. By selecting 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:
RecordSet Column
While a summary column calculates an aggregated value over a set of detail records, a RecordSet column acts as a reference to such 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 New RecordSet
button in Selections
section.
To create a new RecordSet
column, click on the New RecordSet
button, select proper relation and click Add RecordSet
button. As the result, the column will be listed in the Selections
section.
The RecordSet
column settings are displayed on the screenshot below:
There is nothing special about RecordSet column settings. We need all of the Order Details, so, the filter is empty.
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.
There is the ParentKey()
pseudo-function in the first assignment. This function picks up a key of 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.