Copying master record together with details was a non-trivial task. Four months ago we outlined 9-step solution that involves creating lookups and formulas and summaries and multi-record buttons and change triggers. And quietly worked on much simpler solution.
Today TeamDesk proudly presents RecordSet columns and couple of extensions to Create and Update workflow actions to make copying detail records straightforward process.
Suppose you have Orders tables with Order Items detail table and want to use a button to create an Invoice with Items based on the order’s data.
First few steps are clear – create custom button, add create action to Invoice table and assign appropriate Order columns to Invoice columns – this will add new invoice master record.
But what about copying items? And this is where new RecordSet column come into play.
While summary column calculates aggregated value over the set of detail records, RecordSet column acts as the reference to such a set. The column is bound to master-detail relation and has optional filter. You can find New RecordSet button in Selections section.
Click on a button, select proper relation and click Add RecordSet button.
There is nothing special about RecordSet column settings. We need all of the order details so filter is empty.
Now to items copying task. Next to Create Invoice Header action we’ll add Copy Items create action. But once we have RecordSet column the action will have one extra option. Get values from 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 us to repeat an action for every detail record in a set.
Now the tricky thing is to bind Invoice Item to Invoice created in previous action. And here is new ParentKey() pseudo-function.
It’s usage is limited to workflow actions’ assignments and supported in a single scenario. You can only assign ParentKey() – no complex expressions – to some reference column and just that. Behind the scenes we track
record keys touched by preceding workflow actions, one key per table. In our case previous workflow action created a record in Invoice table, new record’s key is noted and populating Invoice reference with ParentKey() picks up a key of newly created Invoice.
Update action extension
Update action is also extended to support RecordSet columns and more.
You can update either current record or select a set of detail records to update by specifying RecordSet column as a target.