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:
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.
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.
Next: Column Usage