This old chestnut!
Wouldn't it be great if this was a feature built into FileMaker Pro?
Ok, here's my thoughts (I'm sure there are better ways of doing this, but this works)
1. Store the orginal parent ID in a variable, that you know already. (Lets call it $Original_ID)
2. In the Parent table, create a new record, grab the id of the new record into a variable. (Let's call this $New_ID) The get next record ID could also work in this situation, but I'd be concerned about the timing if you're in a multi-user system where lots of records are getting created simultaneously, I'd prefer to create the record and get the ID.
3. Go back to the orignal parent record (you could easily find it using the ID stored in the variable $Original_ID)
4. Go to related child records, export these
5. Import these records into the child table, replace the linking (foreign key) parent ID with the $New_ID
6. Go to the new parent record for viewing (find the record using $New_ID)
I'd like to know any better ways of doing this, this tequnique may be slow on iOS from what I understand.
Okay, one thing I'm missing - how do I go back to the original record? (step 3 in your answer) Exactly what steps would you perform to do this? You've said to perform a find, but I've always found that this has unpredictable results. I have a lot of trouble once I've done a find knowing whether I should Show All Records, or Unsort Records, or whether I can just use the record and forget about the found set. Basically I don't know how found sets work.
If there's a good tutorial you know that covers these kinds of things, I'd be interested to see it, because like I said, I think there's some fundamental piece of Filemaker understanding that I'm missing.
1 of 1 people found this helpful
roughly, you can peform a find something like this
Enter Find Mode, set your id field to a calculated result (being the variable), then perform find.
The search should ALWAYS find the one record, but it is wise to set error capture on and check for found records after the peform find.
If you want some help understanding found sets, think of it this way:
Performing functions generally work based on a found set of records. Performing a find, then doing an export or a loop, sort etc will only work on the currently found set of records.
When you perform a find and it returns just 1 record, you can then goto the related records and the new found set of records are just those related records.
The best way to practice this is to perform some tasks manually. If you have FileMaker Pro advanced, use the debugger when performing scripts.
If you're really stuck, I could throw together a file for you to take a look at the code.
Okay, thanks, that's a good explanation.
As an aside, I always permanently populate a separate field (could be named "Original_ID") with that original ID in all the newly created records and line items. This becomes the user's path to sanity when, for example, a invoice gets multiple credit memos or a PO/Quote is altered multiple times. It then becomes easy to display a history of that transaction in any number of ways.
Thanks, I was able to duplicate Line Items using Duplicate Record, then just Go To Record/Request/Page [Next; Exit after Last] worked well enough. I didn't have to do Export/Import - although if that's a better way to do it for some reason, I'd like to understand why.
I think the main problem was I was using GTRR, but "Show only related records" wasn't checked, so my find didn't appear to be working. It seems obvious now, but when I didn't know exactly what it was supposed to do, I couldn't tell what was going wrong.