in other word, you'd like to duplicate related records.
I have an example of that somewhere on the site where I usually collaborate (experts-exchange.com), see if you find it there, otherwise I'll try to locate it on my disk and post it to you.
It is a big pain. I wish FMP had an option in the relationship definition that helped with this. Currently, you can select to delete when the related record is deleted so that you avoid orphan records, which is good. If you could select to duplicate when related record is duplicated, that would simplify matters.
If you just have the relationship you describe, and not ones that are multiple levels deep, you can script this.
- Get to the record you want to duplicate.
- Show all records.
- Omit record.
- Show omitted only. // Now you are only showing the record you want to duplicate.
- Duplicate & commit record.
- Store the record_id in a variable - $$new_rec_id
- Omit the new record, leaving you on the original record.
- Go to related records (line items), show only related, on appropriate layout.
- Go to first record
- Duplicate record, and set work_order_rec_id = $$new_rec_id
- Commit record.
- Omit record. - // Drops the new line item linked to the new work order
- Go to first record.
- Omit record. - // Drops the original line item linked to the original work order
- Exit loop if record count = 0
- End loop.
- Go to original layout, optionally perform find based on rec_id = $$new_rec_id to take you to the new work order.
Cool! This worked wonders!
And I even understand the script.
Can I impose upon the group-brain here and ask how this would work if you wanted to duplicate multiple records in multiple tables. Same relation as the initial question..... One master table, one-to-many record relations to two tables, one for notes and one for line-items. The script example given works for one table, how could you do it for two tables? Will the variable pass to a sub-script if I break up the loop portion into a smaller script that gets called twice with the different relations specified?
If you are only dealing with one master record and all of the records you need to duplicate are one hop away, it is trivial to add additional related groups of records. Just repeat that second block, only applying it to the other relationship(s).
Entirely up to you whether you are comfortable just flat out repeating the block of script steps or would rather put it in a seperate script and call it. If you break it out, you could either pass the variable in as a parameter to the script, or you could leave it as is. If you prefix the variable with two dollars signs, as in my previous example, the variable will remain set in the subscripts.