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.
Great news! Looking forward to trying it out!
For the new function, is it possible to give us a pre-built database template? i think it is easy for us to understand them
Here you go:
This is truly good !!
A pre-built template would be great. I tried for a while to recreate this blog going step by step but for some reason I could not get the Parentkey() function to input the parent key id. The records would create but the reference column would be blank.
Here you go:
Great News !
Could you confirm where you place the ‘Copy Items’ workflow actions ?
In the source-master table (aka the ORDER table) ?
So we would have in the ORDER Tabla
– 1 button
– 1 workflow action creating the invoice
– 1 workflow action creating the invoice items (copied from the ORDER ITEMS table)
Yes, please review following example:
Just used this today. Worked Perfect!! Great Job!
In our application we need to automatically insert some standard subrecords, copied from a separate table, when a parent record is created. Does this solve that or does it only work with preexisting subrecords?
I am so happy you did this – I used it and it works beautifully.
Thanks a lot to the team for this great improvement.
I have an extra request, though.
The concept of ‘Recordset’ is very convenient.
Could it possible to use it in documents, to display specific records of a subtable ?
For instance, with your ORDER & ORDER ITEMS TABLE.
If I want to generate an ORDER document showing only the ORDER ITEMS that have been shipped.
In the present situation, I would need to display all the lines, and to use an extra column with a #dr code to remove the lines I do not want.
Using a record set in the document would be so convenient…